Line Input not finding Chr10 or 13

[bulldawgfan12000]

Thanks for reading. Excel 2003. I'm trying to use the code below
(not mine) to read a huge text file, which I believe comes from UNIX,
into multiple sheets. The text file is 25mb and is ~500,000 lines
when viewed in EditPad (notepad doesn't format it right) so I am
pretty sure it is using *LF* only. There are no other delimiters in
the file what so ever. It's not fixed width either.

VBA Help says that "The Line Input # statement reads from a file one
character at a time until it encounters a carriage return (Chr(13)) or
carriage return-linefeed (Chr(13) + Chr(10)) sequence."

So, why is this not working? It's basically reading the whole file as
one line and therefore gets an error once the "InStr" gets to ~25
million.

Right now, I am planning on reading the file into multiple sheets and
then saving those sheets back out as individual text files. This is
because I've already written a LONG program to pull each in as fixed
width and manipulate/format the data back the way I need it to be sent
to an Access Database. That is, after I had manually "split" the
files using Edit Pad.....

Thanks all, this board has invaluable so far in this project; I just
couldn't find any posts re: this exact subject.

Do While Not EOF(5)
iSh = (I / MaxSize) + 1
lL = I Mod MaxSize
Line Input #5, strLine
If Right(strLine, 1) <> sDelim Then
strLine = Trim(strLine) & sDelim
End If
J = 0
Do While Len(strLine) > 1
iLen = InStr(strLine, sDelim)
Worksheets("Sheet" & iSh).Offset(lL, J).Value = _
Trim(Left(strLine, iLen - 1))
strLine = Trim(Right(strLine, Len(strLine) - iLen))
J = J + 1
Loop
I = I + 1
Loop

 

[Gary Keramidas]

are you allowed to post a couple lines of the raw data?

 

[Helmut Weber]

Hi,

at first glance

(Chr(13)) or carriage return-linefeed (Chr(13) + Chr(10))

sDelim can't be this or that.

If Right(strLine, 1) <> Chr(13) and

Right(strLine, 2) <> (Chr(13) & Chr(10) then


--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Dave Peterson]

I've used old DOS commands that will convert DOS files to UNIX and vice versa.

If you search google for
DOS2Unix
and
Unix2DOS
(maybe spell them out)

You'll find tons of hits.

(But I'm not sure what you're gonna do with 500,000 rows in xl2003.)

 

[SQL Guy]

Why not import it in Access?

MH

 

[Jay]

Hi BD Fan -

Whenever I see a discussion about CRLF, I recall having been frequently
stung by a LFCR pair instead of the expected CRLF pair. You can never really
be sure how some of these strings were forged together in the 'olden days.'

It sounds like you may have covered this scenario in your code, but I call
your attention to it just in case. Now that I think of it, also watch out
for CRCR... As Gary suggests, a bit of your text file might provide some
leads.

 

[bulldawgfan12000]

Gary - see snippet below. The EOL delimiter is the "square" symbol
when you open the text file in Notepad, if that helps...Notepad doesnt
know how to format the thing at all but EditPad make it look like it's
supposed to (I think 'cause it supports the UNIX "format" or whatever)

Dave - i'll try this. i'll also try to find a way to kick off one of
these programs from within excel if possible (so users dont need me to
do the conversion for them). i could package the file with my excel
program and the access db.

SQL Guy - the text file is actually a report that was never meant to
be anything else but hard copy/viewed on the screen. The only way *I*
knew how to get it in database format was to put it in excel and then
manipulate heavily.

Helmut - if I understand my own situation right, none of the code
after the "Line Input..." line really matters because all I have is
one loooooong string of data referenced by strLine. Maybe I'm wrong


SNIPPET...this is starting at the first character of the file - i
selected all the way from left to right in notepad....it's weird it
went to the next line after each of the "returns", except the very
first one where you can see the "square". FYI, there is a "return"
after "1" of Page 1, two of them after the "7" in 14:17, one after
"ACCOUNT2", etc. (hoping that helps somehow)


Date 02/28/07 Form ID Security Report
Page 1
Time 14:17

SECURITY CLASS: ACCOUNT2
FORM TYPE: All
ACCESS MODE: All

** Indicates Point of Inaccessibility

APPLICATION TOKENS:

SYSTEM PROGRAM
UNSECURED SECURED
PRODUCT LINE CODE CODE FORM ID SECURED
FCs FCs

-------------------------------------------------------------------------------------------------
L80 **ACB01 ACB0.2 YES
L80 AC AC00 AC00.1 NO
INP ACD
L80 AC AC00 AC00.2 NO INP
+- ACD
L80 AC AC00 AC00.3 NO
INP C
L80 AC AC01 AC01.1 NO INPMVFZRU-
+ ACD
L80 AC AC01 AC01.2 NO
L80 AC AC01 AC01.3 NO
L80 AC AC01 AC01.4

 

[Tom Ogilvy]

Use this to test your file. Make a blank sheet active, then run the code,

Sub ReadFile()
Dim sChr As String, sStr As String
Dim rw As Long, cnt As Long
Dim sName As String, ff As Long
sName = Application.GetOpenFilename(FileFilter:="Text Files (*.txt),*.txt")
If LCase(sName) = "false" Then Exit Sub
ff = FreeFile
Open sName For Input As #ff ' Open file.
cnt = 0
rw = 0
Do While Not EOF(ff) ' Loop until end of file.
cnt = cnt + 1
sChr = Input(1, #1) ' Get one character.
If sChr = Chr(10) Then 'vbLF
rw = rw + 1
Cells(rw, 1).Value = sStr
sStr = ""
Else
sStr = sStr & sChr
End If
If cnt > 2000 Then Exit Do
Loop
Close #ff
End Sub

If it works, then you can modify it to handle the whole file.

--
Regards,
Tom Ogilvy


Gary - see snippet below. The EOL delimiter is the "square" symbol
when you open the text file in Notepad, if that helps...Notepad doesnt
know how to format the thing at all but EditPad make it look like it's
supposed to (I think 'cause it supports the UNIX "format" or whatever)

Dave - i'll try this. i'll also try to find a way to kick off one of
these programs from within excel if possible (so users dont need me to
do the conversion for them). i could package the file with my excel
program and the access db.

SQL Guy - the text file is actually a report that was never meant to
be anything else but hard copy/viewed on the screen. The only way *I*
knew how to get it in database format was to put it in excel and then
manipulate heavily.

Helmut - if I understand my own situation right, none of the code
after the "Line Input..." line really matters because all I have is
one loooooong string of data referenced by strLine. Maybe I'm wrong


SNIPPET...this is starting at the first character of the file - i
selected all the way from left to right in notepad....it's weird it
went to the next line after each of the "returns", except the very
first one where you can see the "square". FYI, there is a "return"
after "1" of Page 1, two of them after the "7" in 14:17, one after
"ACCOUNT2", etc. (hoping that helps somehow)


Date 02/28/07 Form ID Security Report
Page 1
Time 14:17

SECURITY CLASS: ACCOUNT2
FORM TYPE: All
ACCESS MODE: All

** Indicates Point of Inaccessibility

APPLICATION TOKENS:

SYSTEM PROGRAM
UNSECURED SECURED
PRODUCT LINE CODE CODE FORM ID SECURED
FCs FCs

-------------------------------------------------------------------------------------------------
L80 **ACB01 ACB0.2 YES
L80 AC AC00 AC00.1 NO
INP ACD
L80 AC AC00 AC00.2 NO INP
+- ACD
L80 AC AC00 AC00.3 NO
INP C
L80 AC AC01 AC01.1 NO INPMVFZRU-
+ ACD
L80 AC AC01 AC01.2 NO
L80 AC AC01 AC01.3 NO
L80 AC AC01 AC01.4

 

[bulldawgfan12000]

Tom - That worked well. I guess I didn't consider doing it this way
because I thought it would take forever but it actually processes
60,000 rows in <30 seconds on my PC. Pretty impressive. I think I
can handle the modifications to iterate on different sheets for the
rest of the file. You guys are awesome. Thanks.