Importing a Text File Into 1 Column

[Ralph]

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!!

 

[Matthew Herbert]

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]

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?

 

[RB Smissaert]

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

 

[Ralph]

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]

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

 

[RB Smissaert]

Try the somewhat simpler code I posted just a while ago.

RBS

 

[Ralph]

THAT WORKS, THANK YOU!!!!!!

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

 

[Rick Rothstein]

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.

 

[RB Smissaert]

OK, a few less lines of code maybe, but is it faster?

RBS

 

[Rick Rothstein]

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.

 

[RB Smissaert]

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]

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)

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.

 

[RB Smissaert]

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

 

[Matthew Herbert]

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 procedure:

Sub TestSplitText()
SplitTextDataToWorksheet strFullPathName:="C:\Documents and
Settings\Matt\Desktop\Temp\Yahoo\Data\AA.txt", rngAnchor:=Range("A1")
End Sub

So, the argument for SplitTextDataToWorksheet’s strFullPathName parameter is
"C:\Documents and Settings\Matt\Desktop\Temp\Yahoo\Data\AA.txt" and the
argument for SplitTextDataToWorksheet’s rngAnchor parameter is Range(“A1â€).
You’ll 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