Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing a Text File Into 1 Column
hi,
i have a massive text file that is nothing more than a series of words separated by commas, i.e. dog, cat, ball, clouds, rain there are probably 20k to 30k entries ... is there a way i can import this file into excel so that these words are stacked in 1 column?? i am doing it the file-import way but it pulls only so many into 1 row, and then i have to copy-paste-transpose, etc... it will take me 5 years to do it this way lol PLEASE HELP! THANKS!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing a Text File Into 1 Column
Ralph,
This is a procedure I wrote for taking Yahoo! pricing data (which I download into a text file) and placing the data into a worksheet. strFullPathName is the complete path name of the file (e.g. C:\test.txt) and rngAnchor is the upper-left cell for the output (e.g. Range("A1")). Change the delimiter in the Split function to fit your needs and remove the TextToColumns syntax if you don't need it. Best, Matthew Herbert Sub SplitTextDataToWorksheet(strFullPathName As String, rngAnchor As Range) Dim objFSO As Object Dim objFStream As Object Dim strLine As String Dim varSplit As Variant Dim intColAnchor As Integer Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFStream = objFSO.OpenTextFile(strFullPathName) strLine = objFStream.ReadAll varSplit = Split(strLine, Chr(10)) objFStream.Close Set objFStream = Nothing Set objFSO = Nothing intColAnchor = rngAnchor.Column With rngAnchor.Parent Range(.Cells(1, intColAnchor), .Cells(UBound(varSplit) + 1, intColAnchor)) = Application.WorksheetFunction.Transpose(varSplit) Range(.Cells(1, intColAnchor), .Cells(UBound(varSplit) + 1, intColAnchor)).TextToColumns Comma:=True End With End Sub "Ralph" wrote: hi, i have a massive text file that is nothing more than a series of words separated by commas, i.e. dog, cat, ball, clouds, rain there are probably 20k to 30k entries ... is there a way i can import this file into excel so that these words are stacked in 1 column?? i am doing it the file-import way but it pulls only so many into 1 row, and then i have to copy-paste-transpose, etc... it will take me 5 years to do it this way lol PLEASE HELP! THANKS!! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing a Text File Into 1 Column
i soooooooo wish i knew what you were talking about Matthew, you are way over
my head with those instructions, can you dumb it down for me please? "Matthew Herbert" wrote: Ralph, This is a procedure I wrote for taking Yahoo! pricing data (which I download into a text file) and placing the data into a worksheet. strFullPathName is the complete path name of the file (e.g. C:\test.txt) and rngAnchor is the upper-left cell for the output (e.g. Range("A1")). Change the delimiter in the Split function to fit your needs and remove the TextToColumns syntax if you don't need it. Best, Matthew Herbert Sub SplitTextDataToWorksheet(strFullPathName As String, rngAnchor As Range) Dim objFSO As Object Dim objFStream As Object Dim strLine As String Dim varSplit As Variant Dim intColAnchor As Integer Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFStream = objFSO.OpenTextFile(strFullPathName) strLine = objFStream.ReadAll varSplit = Split(strLine, Chr(10)) objFStream.Close Set objFStream = Nothing Set objFSO = Nothing intColAnchor = rngAnchor.Column With rngAnchor.Parent Range(.Cells(1, intColAnchor), .Cells(UBound(varSplit) + 1, intColAnchor)) = Application.WorksheetFunction.Transpose(varSplit) Range(.Cells(1, intColAnchor), .Cells(UBound(varSplit) + 1, intColAnchor)).TextToColumns Comma:=True End With End Sub "Ralph" wrote: hi, i have a massive text file that is nothing more than a series of words separated by commas, i.e. dog, cat, ball, clouds, rain there are probably 20k to 30k entries ... is there a way i can import this file into excel so that these words are stacked in 1 column?? i am doing it the file-import way but it pulls only so many into 1 row, and then i have to copy-paste-transpose, etc... it will take me 5 years to do it this way lol PLEASE HELP! THANKS!! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing a Text File Into 1 Column
Something like this is probably a lot faster:
Function TextFileToString(ByVal strFile As String) As String Dim hFile As Long On Error GoTo ERROROUT hFile = FreeFile Open strFile For Binary Access Read As #hFile TextFileToString = Space(LOF(hFile)) Get hFile, , TextFileToString Close #hFile Exit Function ERROROUT: If hFile 0 Then Close #hFile End If End Function Sub test() Dim i As Long Dim str As String Dim arr Dim arr2 str = TextFileToString("C:\testfile.txt") arr = Split(str, ",") ReDim arr2(1 To UBound(arr) + 1, 1 To 1) For i = 0 To UBound(arr) arr2(i + 1, 1) = arr(i) Next i Range(Cells(1), Cells(UBound(arr2), 1)) = arr2 End Sub RBS "Matthew Herbert" wrote in message ... Ralph, This is a procedure I wrote for taking Yahoo! pricing data (which I download into a text file) and placing the data into a worksheet. strFullPathName is the complete path name of the file (e.g. C:\test.txt) and rngAnchor is the upper-left cell for the output (e.g. Range("A1")). Change the delimiter in the Split function to fit your needs and remove the TextToColumns syntax if you don't need it. Best, Matthew Herbert Sub SplitTextDataToWorksheet(strFullPathName As String, rngAnchor As Range) Dim objFSO As Object Dim objFStream As Object Dim strLine As String Dim varSplit As Variant Dim intColAnchor As Integer Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFStream = objFSO.OpenTextFile(strFullPathName) strLine = objFStream.ReadAll varSplit = Split(strLine, Chr(10)) objFStream.Close Set objFStream = Nothing Set objFSO = Nothing intColAnchor = rngAnchor.Column With rngAnchor.Parent Range(.Cells(1, intColAnchor), .Cells(UBound(varSplit) + 1, intColAnchor)) = Application.WorksheetFunction.Transpose(varSplit) Range(.Cells(1, intColAnchor), .Cells(UBound(varSplit) + 1, intColAnchor)).TextToColumns Comma:=True End With End Sub "Ralph" wrote: hi, i have a massive text file that is nothing more than a series of words separated by commas, i.e. dog, cat, ball, clouds, rain there are probably 20k to 30k entries ... is there a way i can import this file into excel so that these words are stacked in 1 column?? i am doing it the file-import way but it pulls only so many into 1 row, and then i have to copy-paste-transpose, etc... it will take me 5 years to do it this way lol PLEASE HELP! THANKS!! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing a Text File Into 1 Column
i copied and pasted the code into the vb editor and ran the macro and got an
error - "compile error:expected end sub" i am kind of clueless about all this, can you please DUMB IT DOWN for me? thanks! "RB Smissaert" wrote: Something like this is probably a lot faster: Function TextFileToString(ByVal strFile As String) As String Dim hFile As Long On Error GoTo ERROROUT hFile = FreeFile Open strFile For Binary Access Read As #hFile TextFileToString = Space(LOF(hFile)) Get hFile, , TextFileToString Close #hFile Exit Function ERROROUT: If hFile 0 Then Close #hFile End If End Function Sub test() Dim i As Long Dim str As String Dim arr Dim arr2 str = TextFileToString("C:\testfile.txt") arr = Split(str, ",") ReDim arr2(1 To UBound(arr) + 1, 1 To 1) For i = 0 To UBound(arr) arr2(i + 1, 1) = arr(i) Next i Range(Cells(1), Cells(UBound(arr2), 1)) = arr2 End Sub RBS "Matthew Herbert" wrote in message ... Ralph, This is a procedure I wrote for taking Yahoo! pricing data (which I download into a text file) and placing the data into a worksheet. strFullPathName is the complete path name of the file (e.g. C:\test.txt) and rngAnchor is the upper-left cell for the output (e.g. Range("A1")). Change the delimiter in the Split function to fit your needs and remove the TextToColumns syntax if you don't need it. Best, Matthew Herbert Sub SplitTextDataToWorksheet(strFullPathName As String, rngAnchor As Range) Dim objFSO As Object Dim objFStream As Object Dim strLine As String Dim varSplit As Variant Dim intColAnchor As Integer Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFStream = objFSO.OpenTextFile(strFullPathName) strLine = objFStream.ReadAll varSplit = Split(strLine, Chr(10)) objFStream.Close Set objFStream = Nothing Set objFSO = Nothing intColAnchor = rngAnchor.Column With rngAnchor.Parent Range(.Cells(1, intColAnchor), .Cells(UBound(varSplit) + 1, intColAnchor)) = Application.WorksheetFunction.Transpose(varSplit) Range(.Cells(1, intColAnchor), .Cells(UBound(varSplit) + 1, intColAnchor)).TextToColumns Comma:=True End With End Sub "Ralph" wrote: hi, i have a massive text file that is nothing more than a series of words separated by commas, i.e. dog, cat, ball, clouds, rain there are probably 20k to 30k entries ... is there a way i can import this file into excel so that these words are stacked in 1 column?? i am doing it the file-import way but it pulls only so many into 1 row, and then i have to copy-paste-transpose, etc... it will take me 5 years to do it this way lol PLEASE HELP! THANKS!! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing a Text File Into 1 Column
Or to make it simpler you can put it all in one Sub:
Sub TextFileToColum(strFile As String, rngFirstCell As Range) Dim i As Long Dim str As String Dim arr Dim arr2 str = TextFileToString(strFile) arr = Split(str, ",") ReDim arr2(1 To UBound(arr) + 1, 1 To 1) For i = 0 To UBound(arr) arr2(i + 1, 1) = arr(i) Next i Range(rngFirstCell, _ Cells(rngFirstCell.Row + UBound(arr2) - 1, _ rngFirstCell.Column)) = arr2 End Sub Function TextFileToString(ByVal strFile As String) As String Dim hFile As Long On Error GoTo ERROROUT hFile = FreeFile Open strFile For Binary Access Read As #hFile TextFileToString = Space(LOF(hFile)) Get hFile, , TextFileToString Close #hFile Exit Function ERROROUT: If hFile 0 Then Close #hFile End If End Function You can then run it with this simple code: Sub test() TextFileToColum "C:\testfile.txt", Cells(1) End Sub This is in VBA, so in Excel press Alt + F11, then press Ctrl + R to see the VB project explorer, in the left hand pane right-click your VBAProject and do insert module, then paste the posted code to that module. You can run the code from the worksheet with Tools, Macro, Macros. RBS "Ralph" wrote in message ... hi, i have a massive text file that is nothing more than a series of words separated by commas, i.e. dog, cat, ball, clouds, rain there are probably 20k to 30k entries ... is there a way i can import this file into excel so that these words are stacked in 1 column?? i am doing it the file-import way but it pulls only so many into 1 row, and then i have to copy-paste-transpose, etc... it will take me 5 years to do it this way lol PLEASE HELP! THANKS!! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing a Text File Into 1 Column
Try the somewhat simpler code I posted just a while ago.
RBS "Ralph" wrote in message ... i copied and pasted the code into the vb editor and ran the macro and got an error - "compile error:expected end sub" i am kind of clueless about all this, can you please DUMB IT DOWN for me? thanks! "RB Smissaert" wrote: Something like this is probably a lot faster: Function TextFileToString(ByVal strFile As String) As String Dim hFile As Long On Error GoTo ERROROUT hFile = FreeFile Open strFile For Binary Access Read As #hFile TextFileToString = Space(LOF(hFile)) Get hFile, , TextFileToString Close #hFile Exit Function ERROROUT: If hFile 0 Then Close #hFile End If End Function Sub test() Dim i As Long Dim str As String Dim arr Dim arr2 str = TextFileToString("C:\testfile.txt") arr = Split(str, ",") ReDim arr2(1 To UBound(arr) + 1, 1 To 1) For i = 0 To UBound(arr) arr2(i + 1, 1) = arr(i) Next i Range(Cells(1), Cells(UBound(arr2), 1)) = arr2 End Sub RBS "Matthew Herbert" wrote in message ... Ralph, This is a procedure I wrote for taking Yahoo! pricing data (which I download into a text file) and placing the data into a worksheet. strFullPathName is the complete path name of the file (e.g. C:\test.txt) and rngAnchor is the upper-left cell for the output (e.g. Range("A1")). Change the delimiter in the Split function to fit your needs and remove the TextToColumns syntax if you don't need it. Best, Matthew Herbert Sub SplitTextDataToWorksheet(strFullPathName As String, rngAnchor As Range) Dim objFSO As Object Dim objFStream As Object Dim strLine As String Dim varSplit As Variant Dim intColAnchor As Integer Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFStream = objFSO.OpenTextFile(strFullPathName) strLine = objFStream.ReadAll varSplit = Split(strLine, Chr(10)) objFStream.Close Set objFStream = Nothing Set objFSO = Nothing intColAnchor = rngAnchor.Column With rngAnchor.Parent Range(.Cells(1, intColAnchor), .Cells(UBound(varSplit) + 1, intColAnchor)) = Application.WorksheetFunction.Transpose(varSplit) Range(.Cells(1, intColAnchor), .Cells(UBound(varSplit) + 1, intColAnchor)).TextToColumns Comma:=True End With End Sub "Ralph" wrote: hi, i have a massive text file that is nothing more than a series of words separated by commas, i.e. dog, cat, ball, clouds, rain there are probably 20k to 30k entries ... is there a way i can import this file into excel so that these words are stacked in 1 column?? i am doing it the file-import way but it pulls only so many into 1 row, and then i have to copy-paste-transpose, etc... it will take me 5 years to do it this way lol PLEASE HELP! THANKS!! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing a Text File Into 1 Column
THAT WORKS, THANK YOU!!!!!!
"RB Smissaert" wrote: Or to make it simpler you can put it all in one Sub: Sub TextFileToColum(strFile As String, rngFirstCell As Range) Dim i As Long Dim str As String Dim arr Dim arr2 str = TextFileToString(strFile) arr = Split(str, ",") ReDim arr2(1 To UBound(arr) + 1, 1 To 1) For i = 0 To UBound(arr) arr2(i + 1, 1) = arr(i) Next i Range(rngFirstCell, _ Cells(rngFirstCell.Row + UBound(arr2) - 1, _ rngFirstCell.Column)) = arr2 End Sub Function TextFileToString(ByVal strFile As String) As String Dim hFile As Long On Error GoTo ERROROUT hFile = FreeFile Open strFile For Binary Access Read As #hFile TextFileToString = Space(LOF(hFile)) Get hFile, , TextFileToString Close #hFile Exit Function ERROROUT: If hFile 0 Then Close #hFile End If End Function You can then run it with this simple code: Sub test() TextFileToColum "C:\testfile.txt", Cells(1) End Sub This is in VBA, so in Excel press Alt + F11, then press Ctrl + R to see the VB project explorer, in the left hand pane right-click your VBAProject and do insert module, then paste the posted code to that module. You can run the code from the worksheet with Tools, Macro, Macros. RBS "Ralph" wrote in message ... hi, i have a massive text file that is nothing more than a series of words separated by commas, i.e. dog, cat, ball, clouds, rain there are probably 20k to 30k entries ... is there a way i can import this file into excel so that these words are stacked in 1 column?? i am doing it the file-import way but it pulls only so many into 1 row, and then i have to copy-paste-transpose, etc... it will take me 5 years to do it this way lol PLEASE HELP! THANKS!! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing a Text File Into 1 Column
You don't need a loop in order to assign the split out values to the column
of cells... you can use the Transpose worksheet function to do it in one line of code. Here is a subroutine that reads in the file, removes any line feeds or carriage returns that might be used to separate data neatly in the file (I added this on the off chance it is needed) and then splits out the data and assigns it to a column starting at a specified cell address.... Sub TextFileToColumn(PathFilename As String, StartAt As String) Dim FileNum As Long, TotalFile As String, Arr() As String ' Read entire file into TotalFile variable FileNum = FreeFile On Error GoTo Whoops Open PathFilename For Binary As #FileNum TotalFile = Space(LOF(FileNum)) Get #FileNum, , TotalFile ' Make sure no line feeds or carriage returns interfere with anything TotalFile = Replace(Replace(Replace(TotalFile, _ vbCr, ","), vbLf, ","), ",,", ",") ' Write the list to the column starting at StartAt Arr = Split(TotalFile, ",") Range(StartAt).Resize(UBound(Arr) + 1) = WorksheetFunction.Transpose(Arr) Whoops: Close End Sub You would call this subroutine from your own function specifying the filename along with its full path and the address of the cell to start at. Something like this... Sub TestMe() TextFileToColumn "c:\temp\test.txt", "J8" End Sub Note: As written, the macro must be run from the sheet where the specified cell address is located. This restriction can be removed by providing an argument in the subroutine to receive the sheet name and then providing a the necessary Worksheets property call to implement it. I didn't do that here because my main point was to show the looping assignments were not needed. -- Rick (MVP - Excel) "RB Smissaert" wrote in message ... Or to make it simpler you can put it all in one Sub: Sub TextFileToColum(strFile As String, rngFirstCell As Range) Dim i As Long Dim str As String Dim arr Dim arr2 str = TextFileToString(strFile) arr = Split(str, ",") ReDim arr2(1 To UBound(arr) + 1, 1 To 1) For i = 0 To UBound(arr) arr2(i + 1, 1) = arr(i) Next i Range(rngFirstCell, _ Cells(rngFirstCell.Row + UBound(arr2) - 1, _ rngFirstCell.Column)) = arr2 End Sub Function TextFileToString(ByVal strFile As String) As String Dim hFile As Long On Error GoTo ERROROUT hFile = FreeFile Open strFile For Binary Access Read As #hFile TextFileToString = Space(LOF(hFile)) Get hFile, , TextFileToString Close #hFile Exit Function ERROROUT: If hFile 0 Then Close #hFile End If End Function You can then run it with this simple code: Sub test() TextFileToColum "C:\testfile.txt", Cells(1) End Sub This is in VBA, so in Excel press Alt + F11, then press Ctrl + R to see the VB project explorer, in the left hand pane right-click your VBAProject and do insert module, then paste the posted code to that module. You can run the code from the worksheet with Tools, Macro, Macros. RBS "Ralph" wrote in message ... hi, i have a massive text file that is nothing more than a series of words separated by commas, i.e. dog, cat, ball, clouds, rain there are probably 20k to 30k entries ... is there a way i can import this file into excel so that these words are stacked in 1 column?? i am doing it the file-import way but it pulls only so many into 1 row, and then i have to copy-paste-transpose, etc... it will take me 5 years to do it this way lol PLEASE HELP! THANKS!! |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing a Text File Into 1 Column
OK, a few less lines of code maybe, but is it faster?
RBS "Rick Rothstein" wrote in message ... You don't need a loop in order to assign the split out values to the column of cells... you can use the Transpose worksheet function to do it in one line of code. Here is a subroutine that reads in the file, removes any line feeds or carriage returns that might be used to separate data neatly in the file (I added this on the off chance it is needed) and then splits out the data and assigns it to a column starting at a specified cell address.... Sub TextFileToColumn(PathFilename As String, StartAt As String) Dim FileNum As Long, TotalFile As String, Arr() As String ' Read entire file into TotalFile variable FileNum = FreeFile On Error GoTo Whoops Open PathFilename For Binary As #FileNum TotalFile = Space(LOF(FileNum)) Get #FileNum, , TotalFile ' Make sure no line feeds or carriage returns interfere with anything TotalFile = Replace(Replace(Replace(TotalFile, _ vbCr, ","), vbLf, ","), ",,", ",") ' Write the list to the column starting at StartAt Arr = Split(TotalFile, ",") Range(StartAt).Resize(UBound(Arr) + 1) = WorksheetFunction.Transpose(Arr) Whoops: Close End Sub You would call this subroutine from your own function specifying the filename along with its full path and the address of the cell to start at. Something like this... Sub TestMe() TextFileToColumn "c:\temp\test.txt", "J8" End Sub Note: As written, the macro must be run from the sheet where the specified cell address is located. This restriction can be removed by providing an argument in the subroutine to receive the sheet name and then providing a the necessary Worksheets property call to implement it. I didn't do that here because my main point was to show the looping assignments were not needed. -- Rick (MVP - Excel) "RB Smissaert" wrote in message ... Or to make it simpler you can put it all in one Sub: Sub TextFileToColum(strFile As String, rngFirstCell As Range) Dim i As Long Dim str As String Dim arr Dim arr2 str = TextFileToString(strFile) arr = Split(str, ",") ReDim arr2(1 To UBound(arr) + 1, 1 To 1) For i = 0 To UBound(arr) arr2(i + 1, 1) = arr(i) Next i Range(rngFirstCell, _ Cells(rngFirstCell.Row + UBound(arr2) - 1, _ rngFirstCell.Column)) = arr2 End Sub Function TextFileToString(ByVal strFile As String) As String Dim hFile As Long On Error GoTo ERROROUT hFile = FreeFile Open strFile For Binary Access Read As #hFile TextFileToString = Space(LOF(hFile)) Get hFile, , TextFileToString Close #hFile Exit Function ERROROUT: If hFile 0 Then Close #hFile End If End Function You can then run it with this simple code: Sub test() TextFileToColum "C:\testfile.txt", Cells(1) End Sub This is in VBA, so in Excel press Alt + F11, then press Ctrl + R to see the VB project explorer, in the left hand pane right-click your VBAProject and do insert module, then paste the posted code to that module. You can run the code from the worksheet with Tools, Macro, Macros. RBS "Ralph" wrote in message ... hi, i have a massive text file that is nothing more than a series of words separated by commas, i.e. dog, cat, ball, clouds, rain there are probably 20k to 30k entries ... is there a way i can import this file into excel so that these words are stacked in 1 column?? i am doing it the file-import way but it pulls only so many into 1 row, and then i have to copy-paste-transpose, etc... it will take me 5 years to do it this way lol PLEASE HELP! THANKS!! |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing a Text File Into 1 Column
I don't know for sure (and, being retired, I no longer have any files large
enough to test it), but my "gut" tells me that having Excel "blast" the entire array into the cells in "one fell swoop" should be faster than visiting the cells one at a time in order to write each values into each cell. Perhaps some one with a sufficiently large enough file can run a time test for us. -- Rick (MVP - Excel) "RB Smissaert" wrote in message ... OK, a few less lines of code maybe, but is it faster? RBS "Rick Rothstein" wrote in message ... You don't need a loop in order to assign the split out values to the column of cells... you can use the Transpose worksheet function to do it in one line of code. Here is a subroutine that reads in the file, removes any line feeds or carriage returns that might be used to separate data neatly in the file (I added this on the off chance it is needed) and then splits out the data and assigns it to a column starting at a specified cell address.... Sub TextFileToColumn(PathFilename As String, StartAt As String) Dim FileNum As Long, TotalFile As String, Arr() As String ' Read entire file into TotalFile variable FileNum = FreeFile On Error GoTo Whoops Open PathFilename For Binary As #FileNum TotalFile = Space(LOF(FileNum)) Get #FileNum, , TotalFile ' Make sure no line feeds or carriage returns interfere with anything TotalFile = Replace(Replace(Replace(TotalFile, _ vbCr, ","), vbLf, ","), ",,", ",") ' Write the list to the column starting at StartAt Arr = Split(TotalFile, ",") Range(StartAt).Resize(UBound(Arr) + 1) = WorksheetFunction.Transpose(Arr) Whoops: Close End Sub You would call this subroutine from your own function specifying the filename along with its full path and the address of the cell to start at. Something like this... Sub TestMe() TextFileToColumn "c:\temp\test.txt", "J8" End Sub Note: As written, the macro must be run from the sheet where the specified cell address is located. This restriction can be removed by providing an argument in the subroutine to receive the sheet name and then providing a the necessary Worksheets property call to implement it. I didn't do that here because my main point was to show the looping assignments were not needed. -- Rick (MVP - Excel) "RB Smissaert" wrote in message ... Or to make it simpler you can put it all in one Sub: Sub TextFileToColum(strFile As String, rngFirstCell As Range) Dim i As Long Dim str As String Dim arr Dim arr2 str = TextFileToString(strFile) arr = Split(str, ",") ReDim arr2(1 To UBound(arr) + 1, 1 To 1) For i = 0 To UBound(arr) arr2(i + 1, 1) = arr(i) Next i Range(rngFirstCell, _ Cells(rngFirstCell.Row + UBound(arr2) - 1, _ rngFirstCell.Column)) = arr2 End Sub Function TextFileToString(ByVal strFile As String) As String Dim hFile As Long On Error GoTo ERROROUT hFile = FreeFile Open strFile For Binary Access Read As #hFile TextFileToString = Space(LOF(hFile)) Get hFile, , TextFileToString Close #hFile Exit Function ERROROUT: If hFile 0 Then Close #hFile End If End Function You can then run it with this simple code: Sub test() TextFileToColum "C:\testfile.txt", Cells(1) End Sub This is in VBA, so in Excel press Alt + F11, then press Ctrl + R to see the VB project explorer, in the left hand pane right-click your VBAProject and do insert module, then paste the posted code to that module. You can run the code from the worksheet with Tools, Macro, Macros. RBS "Ralph" wrote in message ... hi, i have a massive text file that is nothing more than a series of words separated by commas, i.e. dog, cat, ball, clouds, rain there are probably 20k to 30k entries ... is there a way i can import this file into excel so that these words are stacked in 1 column?? i am doing it the file-import way but it pulls only so many into 1 row, and then i have to copy-paste-transpose, etc... it will take me 5 years to do it this way lol PLEASE HELP! THANKS!! |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing a Text File Into 1 Column
and, being retired, I no longer have any files large
enough to test it ?? I am sure you know how to write to a file in a loop! faster than visiting the cells one at a time I am not doing that. RBS "Rick Rothstein" wrote in message ... I don't know for sure (and, being retired, I no longer have any files large enough to test it), but my "gut" tells me that having Excel "blast" the entire array into the cells in "one fell swoop" should be faster than visiting the cells one at a time in order to write each values into each cell. Perhaps some one with a sufficiently large enough file can run a time test for us. -- Rick (MVP - Excel) "RB Smissaert" wrote in message ... OK, a few less lines of code maybe, but is it faster? RBS "Rick Rothstein" wrote in message ... You don't need a loop in order to assign the split out values to the column of cells... you can use the Transpose worksheet function to do it in one line of code. Here is a subroutine that reads in the file, removes any line feeds or carriage returns that might be used to separate data neatly in the file (I added this on the off chance it is needed) and then splits out the data and assigns it to a column starting at a specified cell address.... Sub TextFileToColumn(PathFilename As String, StartAt As String) Dim FileNum As Long, TotalFile As String, Arr() As String ' Read entire file into TotalFile variable FileNum = FreeFile On Error GoTo Whoops Open PathFilename For Binary As #FileNum TotalFile = Space(LOF(FileNum)) Get #FileNum, , TotalFile ' Make sure no line feeds or carriage returns interfere with anything TotalFile = Replace(Replace(Replace(TotalFile, _ vbCr, ","), vbLf, ","), ",,", ",") ' Write the list to the column starting at StartAt Arr = Split(TotalFile, ",") Range(StartAt).Resize(UBound(Arr) + 1) = WorksheetFunction.Transpose(Arr) Whoops: Close End Sub You would call this subroutine from your own function specifying the filename along with its full path and the address of the cell to start at. Something like this... Sub TestMe() TextFileToColumn "c:\temp\test.txt", "J8" End Sub Note: As written, the macro must be run from the sheet where the specified cell address is located. This restriction can be removed by providing an argument in the subroutine to receive the sheet name and then providing a the necessary Worksheets property call to implement it. I didn't do that here because my main point was to show the looping assignments were not needed. -- Rick (MVP - Excel) "RB Smissaert" wrote in message ... Or to make it simpler you can put it all in one Sub: Sub TextFileToColum(strFile As String, rngFirstCell As Range) Dim i As Long Dim str As String Dim arr Dim arr2 str = TextFileToString(strFile) arr = Split(str, ",") ReDim arr2(1 To UBound(arr) + 1, 1 To 1) For i = 0 To UBound(arr) arr2(i + 1, 1) = arr(i) Next i Range(rngFirstCell, _ Cells(rngFirstCell.Row + UBound(arr2) - 1, _ rngFirstCell.Column)) = arr2 End Sub Function TextFileToString(ByVal strFile As String) As String Dim hFile As Long On Error GoTo ERROROUT hFile = FreeFile Open strFile For Binary Access Read As #hFile TextFileToString = Space(LOF(hFile)) Get hFile, , TextFileToString Close #hFile Exit Function ERROROUT: If hFile 0 Then Close #hFile End If End Function You can then run it with this simple code: Sub test() TextFileToColum "C:\testfile.txt", Cells(1) End Sub This is in VBA, so in Excel press Alt + F11, then press Ctrl + R to see the VB project explorer, in the left hand pane right-click your VBAProject and do insert module, then paste the posted code to that module. You can run the code from the worksheet with Tools, Macro, Macros. RBS "Ralph" wrote in message ... hi, i have a massive text file that is nothing more than a series of words separated by commas, i.e. dog, cat, ball, clouds, rain there are probably 20k to 30k entries ... is there a way i can import this file into excel so that these words are stacked in 1 column?? i am doing it the file-import way but it pulls only so many into 1 row, and then i have to copy-paste-transpose, etc... it will take me 5 years to do it this way lol PLEASE HELP! THANKS!! |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing a Text File Into 1 Column
and, being retired, I no longer have any files large
enough to test it ?? I am sure you know how to write to a file in a loop! True, I could do that... and maybe will later on (sort of in the middle of several things right now) faster than visiting the cells one at a time I am not doing that. You are right, I misread your loop. But you are still writing each value to the array one at a time before "blasting" the array into the cells. Definitely faster than writing values into the cell... no question about that. Given this, my "gut" says we are probably not too far apart time wise then. -- Rick (MVP - Excel) "RB Smissaert" wrote in message ... and, being retired, I no longer have any files large enough to test it ?? I am sure you know how to write to a file in a loop! faster than visiting the cells one at a time I am not doing that. RBS "Rick Rothstein" wrote in message ... I don't know for sure (and, being retired, I no longer have any files large enough to test it), but my "gut" tells me that having Excel "blast" the entire array into the cells in "one fell swoop" should be faster than visiting the cells one at a time in order to write each values into each cell. Perhaps some one with a sufficiently large enough file can run a time test for us. -- Rick (MVP - Excel) "RB Smissaert" wrote in message ... OK, a few less lines of code maybe, but is it faster? RBS "Rick Rothstein" wrote in message ... You don't need a loop in order to assign the split out values to the column of cells... you can use the Transpose worksheet function to do it in one line of code. Here is a subroutine that reads in the file, removes any line feeds or carriage returns that might be used to separate data neatly in the file (I added this on the off chance it is needed) and then splits out the data and assigns it to a column starting at a specified cell address.... Sub TextFileToColumn(PathFilename As String, StartAt As String) Dim FileNum As Long, TotalFile As String, Arr() As String ' Read entire file into TotalFile variable FileNum = FreeFile On Error GoTo Whoops Open PathFilename For Binary As #FileNum TotalFile = Space(LOF(FileNum)) Get #FileNum, , TotalFile ' Make sure no line feeds or carriage returns interfere with anything TotalFile = Replace(Replace(Replace(TotalFile, _ vbCr, ","), vbLf, ","), ",,", ",") ' Write the list to the column starting at StartAt Arr = Split(TotalFile, ",") Range(StartAt).Resize(UBound(Arr) + 1) = WorksheetFunction.Transpose(Arr) Whoops: Close End Sub You would call this subroutine from your own function specifying the filename along with its full path and the address of the cell to start at. Something like this... Sub TestMe() TextFileToColumn "c:\temp\test.txt", "J8" End Sub Note: As written, the macro must be run from the sheet where the specified cell address is located. This restriction can be removed by providing an argument in the subroutine to receive the sheet name and then providing a the necessary Worksheets property call to implement it. I didn't do that here because my main point was to show the looping assignments were not needed. -- Rick (MVP - Excel) "RB Smissaert" wrote in message ... Or to make it simpler you can put it all in one Sub: Sub TextFileToColum(strFile As String, rngFirstCell As Range) Dim i As Long Dim str As String Dim arr Dim arr2 str = TextFileToString(strFile) arr = Split(str, ",") ReDim arr2(1 To UBound(arr) + 1, 1 To 1) For i = 0 To UBound(arr) arr2(i + 1, 1) = arr(i) Next i Range(rngFirstCell, _ Cells(rngFirstCell.Row + UBound(arr2) - 1, _ rngFirstCell.Column)) = arr2 End Sub Function TextFileToString(ByVal strFile As String) As String Dim hFile As Long On Error GoTo ERROROUT hFile = FreeFile Open strFile For Binary Access Read As #hFile TextFileToString = Space(LOF(hFile)) Get hFile, , TextFileToString Close #hFile Exit Function ERROROUT: If hFile 0 Then Close #hFile End If End Function You can then run it with this simple code: Sub test() TextFileToColum "C:\testfile.txt", Cells(1) End Sub This is in VBA, so in Excel press Alt + F11, then press Ctrl + R to see the VB project explorer, in the left hand pane right-click your VBAProject and do insert module, then paste the posted code to that module. You can run the code from the worksheet with Tools, Macro, Macros. RBS "Ralph" wrote in message ... hi, i have a massive text file that is nothing more than a series of words separated by commas, i.e. dog, cat, ball, clouds, rain there are probably 20k to 30k entries ... is there a way i can import this file into excel so that these words are stacked in 1 column?? i am doing it the file-import way but it pulls only so many into 1 row, and then i have to copy-paste-transpose, etc... it will take me 5 years to do it this way lol PLEASE HELP! THANKS!! |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing a Text File Into 1 Column
Actually, if you take one large file your code is quite a bit faster and
that must be because worksheetfunction.transpose is highly optimized and faster than the simple VB loop. If you take a small file and run both in a large loop then my code is a bit faster, but dealing with a large file is more relevant, so I think you win there. Still have a feeling though there must be a faster way. RBS "Rick Rothstein" wrote in message .. . and, being retired, I no longer have any files large enough to test it ?? I am sure you know how to write to a file in a loop! True, I could do that... and maybe will later on (sort of in the middle of several things right now) faster than visiting the cells one at a time I am not doing that. You are right, I misread your loop. But you are still writing each value to the array one at a time before "blasting" the array into the cells. Definitely faster than writing values into the cell... no question about that. Given this, my "gut" says we are probably not too far apart time wise then. -- Rick (MVP - Excel) "RB Smissaert" wrote in message ... and, being retired, I no longer have any files large enough to test it ?? I am sure you know how to write to a file in a loop! faster than visiting the cells one at a time I am not doing that. RBS "Rick Rothstein" wrote in message ... I don't know for sure (and, being retired, I no longer have any files large enough to test it), but my "gut" tells me that having Excel "blast" the entire array into the cells in "one fell swoop" should be faster than visiting the cells one at a time in order to write each values into each cell. Perhaps some one with a sufficiently large enough file can run a time test for us. -- Rick (MVP - Excel) "RB Smissaert" wrote in message ... OK, a few less lines of code maybe, but is it faster? RBS "Rick Rothstein" wrote in message ... You don't need a loop in order to assign the split out values to the column of cells... you can use the Transpose worksheet function to do it in one line of code. Here is a subroutine that reads in the file, removes any line feeds or carriage returns that might be used to separate data neatly in the file (I added this on the off chance it is needed) and then splits out the data and assigns it to a column starting at a specified cell address.... Sub TextFileToColumn(PathFilename As String, StartAt As String) Dim FileNum As Long, TotalFile As String, Arr() As String ' Read entire file into TotalFile variable FileNum = FreeFile On Error GoTo Whoops Open PathFilename For Binary As #FileNum TotalFile = Space(LOF(FileNum)) Get #FileNum, , TotalFile ' Make sure no line feeds or carriage returns interfere with anything TotalFile = Replace(Replace(Replace(TotalFile, _ vbCr, ","), vbLf, ","), ",,", ",") ' Write the list to the column starting at StartAt Arr = Split(TotalFile, ",") Range(StartAt).Resize(UBound(Arr) + 1) = WorksheetFunction.Transpose(Arr) Whoops: Close End Sub You would call this subroutine from your own function specifying the filename along with its full path and the address of the cell to start at. Something like this... Sub TestMe() TextFileToColumn "c:\temp\test.txt", "J8" End Sub Note: As written, the macro must be run from the sheet where the specified cell address is located. This restriction can be removed by providing an argument in the subroutine to receive the sheet name and then providing a the necessary Worksheets property call to implement it. I didn't do that here because my main point was to show the looping assignments were not needed. -- Rick (MVP - Excel) "RB Smissaert" wrote in message ... Or to make it simpler you can put it all in one Sub: Sub TextFileToColum(strFile As String, rngFirstCell As Range) Dim i As Long Dim str As String Dim arr Dim arr2 str = TextFileToString(strFile) arr = Split(str, ",") ReDim arr2(1 To UBound(arr) + 1, 1 To 1) For i = 0 To UBound(arr) arr2(i + 1, 1) = arr(i) Next i Range(rngFirstCell, _ Cells(rngFirstCell.Row + UBound(arr2) - 1, _ rngFirstCell.Column)) = arr2 End Sub Function TextFileToString(ByVal strFile As String) As String Dim hFile As Long On Error GoTo ERROROUT hFile = FreeFile Open strFile For Binary Access Read As #hFile TextFileToString = Space(LOF(hFile)) Get hFile, , TextFileToString Close #hFile Exit Function ERROROUT: If hFile 0 Then Close #hFile End If End Function You can then run it with this simple code: Sub test() TextFileToColum "C:\testfile.txt", Cells(1) End Sub This is in VBA, so in Excel press Alt + F11, then press Ctrl + R to see the VB project explorer, in the left hand pane right-click your VBAProject and do insert module, then paste the posted code to that module. You can run the code from the worksheet with Tools, Macro, Macros. RBS "Ralph" wrote in message ... hi, i have a massive text file that is nothing more than a series of words separated by commas, i.e. dog, cat, ball, clouds, rain there are probably 20k to 30k entries ... is there a way i can import this file into excel so that these words are stacked in 1 column?? i am doing it the file-import way but it pulls only so many into 1 row, and then i have to copy-paste-transpose, etc... it will take me 5 years to do it this way lol PLEASE HELP! THANKS!! |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing a Text File Into 1 Column
Ralph,
Here is your much awaited reply to "can you dumb it down for me please": strFullPathName and rngAnchor are two parameters for the SplitTextDataToWorksheet procedure. So, in order to run SplitTextDataToWorksheet, you need to provide both parameters with the appropriate arguments. strFullPathName is the full file path name of the file that you want to read the data from, and rngAnchor is the cell for which you want the data output, i.e. the data will be placed in the rngAnchor column but in the first row (given that the data you are reading from is rather large, placing the data in the first row allows you to take advantage of the entire worksheet size). For example, you can run the code with a separate procedure, calling the SplitTextDataToWorksheet procedu Sub TestSplitText() SplitTextDataToWorksheet strFullPathName:="C:\Documents and Settings\Matt\Desktop\Temp\Yahoo\Data\AA.txt", rngAnchor:=Range("A1") End Sub So, the argument for SplitTextDataToWorksheets strFullPathName parameter is "C:\Documents and Settings\Matt\Desktop\Temp\Yahoo\Data\AA.txt" and the argument for SplitTextDataToWorksheets rngAnchor parameter is Range(A1). Youll notice that strFullPathName requires a string argument (hence the double quotes around the file path name), and rngAnchor requires a range object. You'll need to change the strFullPathName argument to fit your file path name (because I highly doubt you have a file called AA.txt in C:\Documents and Settings\Matt\Desktop\Temp\Yahoo\Data). You can change Range("A1") to be what you want (i.e. Range("B1"), Range("C1"), etc.). Once you make the changes, run TextSplitText. Of course, you will need to copy and paste both TextSplitText and SplitTextDataToWorksheet into a code module. (You can do this by hitting Alt+F11 to open the Editor, then click Insert from the menu bar, and then click Module1. Lastly, click View from the menu bar and then click Code. The cursor should now be blinking in the code window. Copy and paste the text). How SplitTextDataToWorksheet works: See the commented code in the SplitTextDataToWorksheet Sub SplitTextDataToWorksheet(strFullPathName As String, _ rngAnchor As Range) '"Dim" is a keyword that dimensions a variable, or in other ' words, it tells the computer "I have a varialbe I want ' to use in the program, make some room for it." The ' "As" means to make it a particular data type, or in other ' words, allocate a certain size of memory for the variable. ' (For example, a Boolean data type takes up less room than ' a Variant data type because Boolean holds two values ' - True and False, but a Variant might be an array that ' holds 10,000 values). Dim objFSO As Object Dim objFStream As Object Dim strLine As String Dim varSplit As Variant Dim intColAnchor As Integer 'Create an object that references the FileSystemObject ("FSO"), an ' object that provides access to a computer's file system. This ' allows you to perform the same sort of operations you are ' already used to, e.g. open a file, close a file, copy a file, ' etc. Set objFSO = CreateObject("Scripting.FileSystemObject") 'One of the the methods of the FSO object is to open a text file. ' You can think of this as if you opened the text file in NotePad. 'Creat an object that references the "open" text file. Set objFStream = objFSO.OpenTextFile(strFullPathName) 'Store all of the string content in the text file into a string ' variable. Think of this as if you selected the text in ' NotePad and copied it. strLine = objFStream.ReadAll 'Take the copied text and replace vbCr and vbLf characters with a ' comma. vbCr is a constant code for a carriage return (i.e. ' as if you hit "Enter" on your keyboard). vbLf is a constant ' code for a line feed character (i.e. as if you hit "Alt+Enter" ' on your keyboard. vbCrLf is a carriage return followed by a ' line feed character. 'Typically, text files will contain vbCr and/or vbLf characters. ' These characters may not be visible to your eye when you open ' the file, but they are there. You may need to change the ' constant (i.e. the vbCrLf) to fit your needs if you don't get ' the anticipated results. (For example, you could have ' strLine = Replace(strLine, vbLf, ",") instead). strLine = Replace(strLine, vbCrLf, ",") 'Use the Split Funciton. If you are familiar with Text to Columns ' in native Excel, then think of the Split function in a similar ' manner. You are taking text and breaking it by a designated ' delimiter, or character (e.g. a space, a tab, a comma, etc). 'Split retuns a zero-based, one-dimensional array containing a ' specified number of substrings. Thus, varSplit will be an ' array of strings "split" by the delimiter. The first argument ' is all of the text from the text file and the delimiter to ' split by is a comma. varSplit = Split(strLine, ",") 'Since the data from the text file is stored in a variable, close ' the text file because the text file is no longer needed. objFStream.Close 'Clear the object/memory. Set objFStream = Nothing 'Clear the object/memory. Set objFSO = Nothing 'Create a reference to the column number of rngAnchor, i.e. the ' argument which contains the output location. intColAnchor = rngAnchor.Column 'rngAnchor.Parent refers to the parent of rngAnchor. In a normal ' Excel hierarchy you have Application.Workbook.Worksheet.Range. ' The parent to the range is the worksheet (i.e. ' rngAnchor.Parent), the parent to the worksheet is the workbook ' (i.e. rngAnchor.Parent.Parent), etc. 'A With statement is a way to execute a series of statements on a ' single object. So take the parent of rngAnchor (i.e. the ' worksheet from which rngAnchor is located) and perform the ' statments inside the With End With statment. 'So, the .Cells is interpretted as follows: ' rngAnchor.Parent.Cells. Rather than having to repeat ' rngAnchor.Parent.Cells multiple times in your syntax, the code ' With statement will append it for you. With rngAnchor.Parent 'Take a look at the TRANSPOSE function in Excel (i.e. search the ' Excel help for Transpose). This funciton is an array ' function, hence why it is being used on varSplit (which was ' created as a one-dimensional array previously). 'UBound is a function that returns the largest available ' placeholder in an array. So, if your text file had 10,000 ' entries (separated by a comma) then UBound would be 9,999. ' (Since the array is zero based, the 1st element is in the 0 ' index and the 10,000th element is in the 9,999 index). If ' you ran the same procedure on another text file which had 10 ' entries (separated by a comma), then UBound would be 9. 'Cells is a one based referencing system that operates off of ' numbers. So Cells(1,1) is the same as Range("A1"), ' Cells(2,2) is the same as Range("B2"), Cells(2,3) is the ' same as Range("C2"), etc. 'Range(.Cells(), .Cells()) creates a Range specified by two cell ' locations, i.e. the first cell in the specified column on ' the first row and the last cell in the specified column on ' the row defined by the UBound in the array + 1 (again, +1 is ' because the array is zero based and as mentioned previously ' 10,000 entries will have a 9,999 UBound and it will take ' 9,999 + 1 to get to 10,000). 'In order to output all of the values in the varSplit array by ' using the TRANSPOSE function, you need to make the size of ' the worksheet range the same as the array. 'The "_" is a line continuation character. It's as if the line ' after "_" were coded on the same line of code for which the ' "_" was coded. Range(.Cells(1, intColAnchor), _ .Cells(UBound(varSplit) + 1, intColAnchor)) = _ Application.WorksheetFunction.Transpose(varSplit) End With End Sub "Ralph" wrote: i soooooooo wish i knew what you were talking about Matthew, you are way over my head with those instructions, can you dumb it down for me please? "Matthew Herbert" wrote: Ralph, This is a procedure I wrote for taking Yahoo! pricing data (which I download into a text file) and placing the data into a worksheet. strFullPathName is the complete path name of the file (e.g. C:\test.txt) and rngAnchor is the upper-left cell for the output (e.g. Range("A1")). Change the delimiter in the Split function to fit your needs and remove the TextToColumns syntax if you don't need it. Best, Matthew Herbert Sub SplitTextDataToWorksheet(strFullPathName As String, rngAnchor As Range) Dim objFSO As Object Dim objFStream As Object Dim strLine As String Dim varSplit As Variant Dim intColAnchor As Integer Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFStream = objFSO.OpenTextFile(strFullPathName) strLine = objFStream.ReadAll varSplit = Split(strLine, Chr(10)) objFStream.Close Set objFStream = Nothing Set objFSO = Nothing intColAnchor = rngAnchor.Column With rngAnchor.Parent Range(.Cells(1, intColAnchor), .Cells(UBound(varSplit) + 1, intColAnchor)) = Application.WorksheetFunction.Transpose(varSplit) Range(.Cells(1, intColAnchor), .Cells(UBound(varSplit) + 1, intColAnchor)).TextToColumns Comma:=True End With End Sub "Ralph" wrote: hi, i have a massive text file that is nothing more than a series of words separated by commas, i.e. dog, cat, ball, clouds, rain there are probably 20k to 30k entries ... is there a way i can import this file into excel so that these words are stacked in 1 column?? i am doing it the file-import way but it pulls only so many into 1 row, and then i have to copy-paste-transpose, etc... it will take me 5 years to do it this way lol PLEASE HELP! THANKS!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing a Text File Into 1 Column | Excel Discussion (Misc queries) | |||
Importing CSV file (saved as Text) into XL as Text -- over 60 colu | Excel Discussion (Misc queries) | |||
Importing text file, only option to edit existing file | Excel Discussion (Misc queries) | |||
Importing csv file all data is in first column | Excel Worksheet Functions | |||
importing text file, removing data and outputting new text file | Excel Programming |