Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing text file without end-of-line delimiter?
In Excel, I recorded a macro while importing a text file (because that's the easiest way I've found to capture the fixed-width values for my fields). It imported just fine. Now I'm using VBA to import that same text file a line at a time and parse each line for data values. I was having trouble (after the first line, the code would quit). I used a msgbox to display that first line prior to parsing- and it showed me the whole file! (or as much as could fit in a msgbox, anyway). So somehow, on importing the text file via the menu (file/open), Excel knew that there were multiple lines and put the data in multiple rows. However, when I use: Do While Not EOF(1) Line Input #1, LineofText MsgBox LineofText Loop It opens the entire file as one line. Clearly I'm missing something here, but I'm not sure what. Any suggestions? Thank you, Keith |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing text file without end-of-line delimiter?
In the VB world, the end of line marker is a Carriage Return followed by a
Line Feed... if your lines are not delimited with this, Line Input does not see your individual lines of text as individual lines. I'm guessing your lines are delimited by simple Line Feeds. If this is the case, and if this code will only be used for files generated in the same way as the one you are currently working with, you could do away with the Do-Loop, read the entire file into a variable and then Split the text into individual lines using the Split function with vbLf, or Chr(10) if you prefer, as the delimiter... this will produce a zero-based array that you can loop through element by element (which will then be line-by-line) and do whatever you need to. For example... Dim X As Long Dim TotalFile As String Dim Paragraphs() As String Line Input #1, TotalFile Paragraphs = Split(TotalFile, vbLf) For X = 0 To UBound(Paragraphs) ' Do whatever you need to on each line in your file Debug.Print Paragraphs(X) Next Remember, this code will only work for Line Feed delimited text... if your code could have to handle both Line Feed delimited text and text delimited with Windows' normal line delimiter (a Carriage Return followed by a Line Feed), then write back and I'll generalize the code for you. Rick "ker_01" wrote in message ... In Excel, I recorded a macro while importing a text file (because that's the easiest way I've found to capture the fixed-width values for my fields). It imported just fine. Now I'm using VBA to import that same text file a line at a time and parse each line for data values. I was having trouble (after the first line, the code would quit). I used a msgbox to display that first line prior to parsing- and it showed me the whole file! (or as much as could fit in a msgbox, anyway). So somehow, on importing the text file via the menu (file/open), Excel knew that there were multiple lines and put the data in multiple rows. However, when I use: Do While Not EOF(1) Line Input #1, LineofText MsgBox LineofText Loop It opens the entire file as one line. Clearly I'm missing something here, but I'm not sure what. Any suggestions? Thank you, Keith |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing text file without end-of-line delimiter?
Rick-
Thank you for your response, explanation, and sample code. For this project I will import several different files, but their sources and formats are so different I'm making a separate sub for each. I'll adapt the snippet you've provided, and post back if I have any problems or questions. Thank you!! Keith "Rick Rothstein (MVP - VB)" wrote in message ... In the VB world, the end of line marker is a Carriage Return followed by a Line Feed... if your lines are not delimited with this, Line Input does not see your individual lines of text as individual lines. I'm guessing your lines are delimited by simple Line Feeds. If this is the case, and if this code will only be used for files generated in the same way as the one you are currently working with, you could do away with the Do-Loop, read the entire file into a variable and then Split the text into individual lines using the Split function with vbLf, or Chr(10) if you prefer, as the delimiter... this will produce a zero-based array that you can loop through element by element (which will then be line-by-line) and do whatever you need to. For example... Dim X As Long Dim TotalFile As String Dim Paragraphs() As String Line Input #1, TotalFile Paragraphs = Split(TotalFile, vbLf) For X = 0 To UBound(Paragraphs) ' Do whatever you need to on each line in your file Debug.Print Paragraphs(X) Next Remember, this code will only work for Line Feed delimited text... if your code could have to handle both Line Feed delimited text and text delimited with Windows' normal line delimiter (a Carriage Return followed by a Line Feed), then write back and I'll generalize the code for you. Rick "ker_01" wrote in message ... In Excel, I recorded a macro while importing a text file (because that's the easiest way I've found to capture the fixed-width values for my fields). It imported just fine. Now I'm using VBA to import that same text file a line at a time and parse each line for data values. I was having trouble (after the first line, the code would quit). I used a msgbox to display that first line prior to parsing- and it showed me the whole file! (or as much as could fit in a msgbox, anyway). So somehow, on importing the text file via the menu (file/open), Excel knew that there were multiple lines and put the data in multiple rows. However, when I use: Do While Not EOF(1) Line Input #1, LineofText MsgBox LineofText Loop It opens the entire file as one line. Clearly I'm missing something here, but I'm not sure what. Any suggestions? Thank you, Keith |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing text file without end-of-line delimiter?
In case you come back to this thread, here is a general function (with
comments) that will handle any normally delimited text file (Line Feed as from a UNIX or Linux system, Carriage Return as from a Mac, or Carriage Feed followed by a Line Feed which is an normal Windows type file). You pass it the full path and filename for your file and it returns an array containing each individual line. Function SplitFileIntoLines(PathFileName As String) As String() Dim X As Long Dim FileNum As Long Dim TotalFile As String Dim Lines() As String ' Better to let VB select the file channel number than to hard code it. FileNum = FreeFile ' Load entire file into the TotalFile variable in "one fell swoop". Open PathFileName For Binary As #FileNum TotalFile = Space(LOF(FileNum)) Get #FileNum, , TotalFile Close #FileNum ' Replace CarriageReturns with Line Feeds... this will help ' handle normal Windows files AND files generated by a Mac. TotalFile = Replace(TotalFile, vbCr, vbLf) ' Now, if it was a normar Windows file, there will be two ' Line Feeds following each other, so we reduce them to just one. TotalFile = Replace(TotalFile, vbLf & vbLf, vbLf) ' Now that the entire file is now contained in the TotalFile ' variable as a Line Feed delimited file, let's return those ' individual lines in an array for use by the calling code. SplitFileIntoLines = Split(TotalFile, vbLf) End Function To see how to use it, consider this sample macros... Sub Test() Dim X As Long Dim FileLines() As String FileLines = SplitFileIntoLines("c:\temp\Book1.txt") For X = 0 To UBound(FileLines) Debug.Print FileLines(X) Next End Sub Rick "ker_01" wrote in message ... Rick- Thank you for your response, explanation, and sample code. For this project I will import several different files, but their sources and formats are so different I'm making a separate sub for each. I'll adapt the snippet you've provided, and post back if I have any problems or questions. Thank you!! Keith "Rick Rothstein (MVP - VB)" wrote in message ... In the VB world, the end of line marker is a Carriage Return followed by a Line Feed... if your lines are not delimited with this, Line Input does not see your individual lines of text as individual lines. I'm guessing your lines are delimited by simple Line Feeds. If this is the case, and if this code will only be used for files generated in the same way as the one you are currently working with, you could do away with the Do-Loop, read the entire file into a variable and then Split the text into individual lines using the Split function with vbLf, or Chr(10) if you prefer, as the delimiter... this will produce a zero-based array that you can loop through element by element (which will then be line-by-line) and do whatever you need to. For example... Dim X As Long Dim TotalFile As String Dim Paragraphs() As String Line Input #1, TotalFile Paragraphs = Split(TotalFile, vbLf) For X = 0 To UBound(Paragraphs) ' Do whatever you need to on each line in your file Debug.Print Paragraphs(X) Next Remember, this code will only work for Line Feed delimited text... if your code could have to handle both Line Feed delimited text and text delimited with Windows' normal line delimiter (a Carriage Return followed by a Line Feed), then write back and I'll generalize the code for you. Rick "ker_01" wrote in message ... In Excel, I recorded a macro while importing a text file (because that's the easiest way I've found to capture the fixed-width values for my fields). It imported just fine. Now I'm using VBA to import that same text file a line at a time and parse each line for data values. I was having trouble (after the first line, the code would quit). I used a msgbox to display that first line prior to parsing- and it showed me the whole file! (or as much as could fit in a msgbox, anyway). So somehow, on importing the text file via the menu (file/open), Excel knew that there were multiple lines and put the data in multiple rows. However, when I use: Do While Not EOF(1) Line Input #1, LineofText MsgBox LineofText Loop It opens the entire file as one line. Clearly I'm missing something here, but I'm not sure what. Any suggestions? Thank you, Keith |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing text file without end-of-line delimiter?
Rick-
This looks fabulous- Just to help me learn, I understand everything except the use of the 'space' keyword: TotalFile = Space(LOF(FileNum)) It appears that this is getting the total file length (length of string to load in the next line), but I'm not clear what the 'space' keyword is doing for the statement. Can you educate me with a brief explanation? Thank you! Keith "Rick Rothstein (MVP - VB)" wrote in message ... In case you come back to this thread, here is a general function (with comments) that will handle any normally delimited text file (Line Feed as from a UNIX or Linux system, Carriage Return as from a Mac, or Carriage Feed followed by a Line Feed which is an normal Windows type file). You pass it the full path and filename for your file and it returns an array containing each individual line. Function SplitFileIntoLines(PathFileName As String) As String() Dim X As Long Dim FileNum As Long Dim TotalFile As String Dim Lines() As String ' Better to let VB select the file channel number than to hard code it. FileNum = FreeFile ' Load entire file into the TotalFile variable in "one fell swoop". Open PathFileName For Binary As #FileNum TotalFile = Space(LOF(FileNum)) Get #FileNum, , TotalFile Close #FileNum ' Replace CarriageReturns with Line Feeds... this will help ' handle normal Windows files AND files generated by a Mac. TotalFile = Replace(TotalFile, vbCr, vbLf) ' Now, if it was a normar Windows file, there will be two ' Line Feeds following each other, so we reduce them to just one. TotalFile = Replace(TotalFile, vbLf & vbLf, vbLf) ' Now that the entire file is now contained in the TotalFile ' variable as a Line Feed delimited file, let's return those ' individual lines in an array for use by the calling code. SplitFileIntoLines = Split(TotalFile, vbLf) End Function To see how to use it, consider this sample macros... Sub Test() Dim X As Long Dim FileLines() As String FileLines = SplitFileIntoLines("c:\temp\Book1.txt") For X = 0 To UBound(FileLines) Debug.Print FileLines(X) Next End Sub Rick "ker_01" wrote in message ... Rick- Thank you for your response, explanation, and sample code. For this project I will import several different files, but their sources and formats are so different I'm making a separate sub for each. I'll adapt the snippet you've provided, and post back if I have any problems or questions. Thank you!! Keith "Rick Rothstein (MVP - VB)" wrote in message ... In the VB world, the end of line marker is a Carriage Return followed by a Line Feed... if your lines are not delimited with this, Line Input does not see your individual lines of text as individual lines. I'm guessing your lines are delimited by simple Line Feeds. If this is the case, and if this code will only be used for files generated in the same way as the one you are currently working with, you could do away with the Do-Loop, read the entire file into a variable and then Split the text into individual lines using the Split function with vbLf, or Chr(10) if you prefer, as the delimiter... this will produce a zero-based array that you can loop through element by element (which will then be line-by-line) and do whatever you need to. For example... Dim X As Long Dim TotalFile As String Dim Paragraphs() As String Line Input #1, TotalFile Paragraphs = Split(TotalFile, vbLf) For X = 0 To UBound(Paragraphs) ' Do whatever you need to on each line in your file Debug.Print Paragraphs(X) Next Remember, this code will only work for Line Feed delimited text... if your code could have to handle both Line Feed delimited text and text delimited with Windows' normal line delimiter (a Carriage Return followed by a Line Feed), then write back and I'll generalize the code for you. Rick "ker_01" wrote in message ... In Excel, I recorded a macro while importing a text file (because that's the easiest way I've found to capture the fixed-width values for my fields). It imported just fine. Now I'm using VBA to import that same text file a line at a time and parse each line for data values. I was having trouble (after the first line, the code would quit). I used a msgbox to display that first line prior to parsing- and it showed me the whole file! (or as much as could fit in a msgbox, anyway). So somehow, on importing the text file via the menu (file/open), Excel knew that there were multiple lines and put the data in multiple rows. However, when I use: Do While Not EOF(1) Line Input #1, LineofText MsgBox LineofText Loop It opens the entire file as one line. Clearly I'm missing something here, but I'm not sure what. Any suggestions? Thank you, Keith |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing text file without end-of-line delimiter?
Whenever you have a question about a keyword in VB, the first thing you
should do is click anywhere on the word and press F1 to get VB's help file for that keyword. The Space function returns the number of spaces (character with the ASCII value of 32) called for by the value you pass into it (in this case, you are right, the length of the file). Rick "ker_01" wrote in message ... Rick- This looks fabulous- Just to help me learn, I understand everything except the use of the 'space' keyword: TotalFile = Space(LOF(FileNum)) It appears that this is getting the total file length (length of string to load in the next line), but I'm not clear what the 'space' keyword is doing for the statement. Can you educate me with a brief explanation? Thank you! Keith "Rick Rothstein (MVP - VB)" wrote in message ... In case you come back to this thread, here is a general function (with comments) that will handle any normally delimited text file (Line Feed as from a UNIX or Linux system, Carriage Return as from a Mac, or Carriage Feed followed by a Line Feed which is an normal Windows type file). You pass it the full path and filename for your file and it returns an array containing each individual line. Function SplitFileIntoLines(PathFileName As String) As String() Dim X As Long Dim FileNum As Long Dim TotalFile As String Dim Lines() As String ' Better to let VB select the file channel number than to hard code it. FileNum = FreeFile ' Load entire file into the TotalFile variable in "one fell swoop". Open PathFileName For Binary As #FileNum TotalFile = Space(LOF(FileNum)) Get #FileNum, , TotalFile Close #FileNum ' Replace CarriageReturns with Line Feeds... this will help ' handle normal Windows files AND files generated by a Mac. TotalFile = Replace(TotalFile, vbCr, vbLf) ' Now, if it was a normar Windows file, there will be two ' Line Feeds following each other, so we reduce them to just one. TotalFile = Replace(TotalFile, vbLf & vbLf, vbLf) ' Now that the entire file is now contained in the TotalFile ' variable as a Line Feed delimited file, let's return those ' individual lines in an array for use by the calling code. SplitFileIntoLines = Split(TotalFile, vbLf) End Function To see how to use it, consider this sample macros... Sub Test() Dim X As Long Dim FileLines() As String FileLines = SplitFileIntoLines("c:\temp\Book1.txt") For X = 0 To UBound(FileLines) Debug.Print FileLines(X) Next End Sub Rick "ker_01" wrote in message ... Rick- Thank you for your response, explanation, and sample code. For this project I will import several different files, but their sources and formats are so different I'm making a separate sub for each. I'll adapt the snippet you've provided, and post back if I have any problems or questions. Thank you!! Keith "Rick Rothstein (MVP - VB)" wrote in message ... In the VB world, the end of line marker is a Carriage Return followed by a Line Feed... if your lines are not delimited with this, Line Input does not see your individual lines of text as individual lines. I'm guessing your lines are delimited by simple Line Feeds. If this is the case, and if this code will only be used for files generated in the same way as the one you are currently working with, you could do away with the Do-Loop, read the entire file into a variable and then Split the text into individual lines using the Split function with vbLf, or Chr(10) if you prefer, as the delimiter... this will produce a zero-based array that you can loop through element by element (which will then be line-by-line) and do whatever you need to. For example... Dim X As Long Dim TotalFile As String Dim Paragraphs() As String Line Input #1, TotalFile Paragraphs = Split(TotalFile, vbLf) For X = 0 To UBound(Paragraphs) ' Do whatever you need to on each line in your file Debug.Print Paragraphs(X) Next Remember, this code will only work for Line Feed delimited text... if your code could have to handle both Line Feed delimited text and text delimited with Windows' normal line delimiter (a Carriage Return followed by a Line Feed), then write back and I'll generalize the code for you. Rick "ker_01" wrote in message ... In Excel, I recorded a macro while importing a text file (because that's the easiest way I've found to capture the fixed-width values for my fields). It imported just fine. Now I'm using VBA to import that same text file a line at a time and parse each line for data values. I was having trouble (after the first line, the code would quit). I used a msgbox to display that first line prior to parsing- and it showed me the whole file! (or as much as could fit in a msgbox, anyway). So somehow, on importing the text file via the menu (file/open), Excel knew that there were multiple lines and put the data in multiple rows. However, when I use: Do While Not EOF(1) Line Input #1, LineofText MsgBox LineofText Loop It opens the entire file as one line. Clearly I'm missing something here, but I'm not sure what. Any suggestions? Thank you, Keith |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing text file without end-of-line delimiter?
Is it possible to open the file (using the equivalent of File|Open), then parse
the data from the worksheet? If not, maybe you can open the file, convert it, save it as a new name, then have your code open the converted file: Option Explicit Sub UpDateTxtFile() Dim FSO As Object Dim RegEx As Object Dim myFile As Object Dim myContents As String Dim myInFileName As String Dim myOutFileName As String myInFileName = "C:\my documents\excel\test.txt" myOutFileName = Environ("temp") & "\testout.txt" Set FSO = CreateObject("Scripting.FileSystemObject") Set myFile = FSO.OpenTextFile(myInFileName, 1, False) myContents = myFile.ReadAll myFile.Close Set RegEx = CreateObject("VBScript.RegExp") With RegEx .Global = True .IgnoreCase = False .Pattern = vbLf myContents = .Replace(myContents, vbCrLf) End With Set myFile = FSO.CreateTextFile(myOutFileName) myFile.Write myContents myFile.Close End Sub Then point at the temp folder's testout.txt. You'll want to be nice, so clean up that file in the %temp% folder when you're done with it. ker_01 wrote: In Excel, I recorded a macro while importing a text file (because that's the easiest way I've found to capture the fixed-width values for my fields). It imported just fine. Now I'm using VBA to import that same text file a line at a time and parse each line for data values. I was having trouble (after the first line, the code would quit). I used a msgbox to display that first line prior to parsing- and it showed me the whole file! (or as much as could fit in a msgbox, anyway). So somehow, on importing the text file via the menu (file/open), Excel knew that there were multiple lines and put the data in multiple rows. However, when I use: Do While Not EOF(1) Line Input #1, LineofText MsgBox LineofText Loop It opens the entire file as one line. Clearly I'm missing something here, but I'm not sure what. Any suggestions? Thank you, Keith -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delimiter for csv and text file using ADO | Excel Programming | |||
Importing text file with no line delimiters | Excel Discussion (Misc queries) | |||
Need Help Importing Text File Using Two or More Spaces as the Delimiter | Excel Discussion (Misc queries) | |||
Need Help Importing Text File Using Two or More Spaces as the Delimiter | Excel Programming | |||
Adding a delimiter when importing a txt file | Excel Programming |