I am trying to open a file and read the data and parse out each line. I open the file and get the rows no problem.
My question is, in the file, the columns are tab delimited. When I Open and read the file with:
Open sFileName For Input As #nFileNumber
Line Input #nFileNumber, sLine
The tabs go away and I have spaces instead. How do I keep the tabs? Or is there another way of doing it? Basically, I need to open the file, get each line, and parse each column in line into a variable.
Please let me know your thoughts.
Split and Join work nicely for this. Personally, I like to read the entire file into a single string variable (assuming the file is less than 5 MB or so,) SPLIT the whole file string, based on vbNewLine as a delimiter, then, SPLIT each element of the resulting array on the field delimiter:
dim sFile as String ' File Buffer
dim sLines() as string ' dynamic array of string, each element will be one line from the file.
dim sLine() as String ' ditto, except each element is one field from a line
Dim L as Long ' Line Counter
Dim F as Long ' Field counter
Open "YourFileName" for binary as #1
sFile = Space(LOF(#1)) ' size the file buffer
get #1,,sFile ' Check this line in MSDN- I'm doing this from memory)
'Now, lets's process the file
sLines = Split (sFile, vbNewLine)
sFile = "" ' Now that the file has been parsed into lines, no need to keep this copy around.
For N = 0 to ubound(sLines)
sLine = Split(sLines(N), vbTab) ' parse line N into fields
For F = 0 to ubound(sLine)
debug.Print "Text at Line " & N & ", Field " & F & " is: " & sLine
As an alternative approach, you could use ADO and the JET text IIsam to load the file into an ADO recordset... Although slower than the raw file I/O approach above, it does handle quoted strings and embedded delimiters automatically... ("An embedded delimiter, she said", "String without an embedded delimiter")
Last edited by loquin; 12-11-08 at 13:33.
"Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
"I have my standards. They may be low, but I have them!" - Bette Middler
"It's a book about a Spanish guy named Manual. You should read it." - Dilbert