Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Line Input not finding Chr10 or 13
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 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Line Input not finding Chr10 or 13
are you allowed to post a couple lines of the raw data?
-- Gary wrote in message ups.com... 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 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Line Input not finding Chr10 or 13
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Line Input not finding Chr10 or 13
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 wrote in message oups.com... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Line Input not finding Chr10 or 13
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Line Input not finding Chr10 or 13
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" |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Line Input not finding Chr10 or 13
Why not import it in Access?
MH wrote in message ups.com... 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 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Line Input not finding Chr10 or 13
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. -- Jay " wrote: 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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
finding 10 smallest numbers from last 20 input | Excel Worksheet Functions | |||
Countif for finding a Time input | Excel Worksheet Functions | |||
Reads entire *.txt file into string opposed to a desired line by line input. | Excel Programming | |||
Finding Column based on input, then first empty row | Excel Programming | |||
Finding Column based on input, then first empty row | Excel Programming |