Reading a text file by column in to an array?

[bdl004]

Hi Guys,

I am having some trouble with some VBA code that is supposed to read a text file in to three arrays by column. The problem is set up as follows:

There is a text file called test.txt that has series of numbers over "n" columns (specified by user). The vba sub must read the first column in to one array, the second column in to a second array, and then nth column in to a third array. The text file is tab and space delimited and contains around 10 000 rows of data.

My problem is that I don't know how to open the file delimited with tab and space and then read the data in to several arrays using VBA memory without writing the data to a sheet (I want to avoid this as it takes a very long time to process this much data).

Any help is greatly appreciated!

[brettdj]

You could convert the file to csv, and then open it directly

Pls change the paths
"C:\test.txt"
"C:\test.csv"
as required

Cheers

Dave


[VBA]Sub GetIt()
Dim objFSO
Dim objTF
Dim strTmp As String
Dim strFNameIn As String
Dim strFNameout As String
Dim lngSize As Long
strFNameIn = "C:\test.txt"
strFNameout = "C:\test.csv"
Set objFSO = CreateObject("Scripting.FileSystemObject")
If objFSO.FileExists(strFNameIn) Then
Set objTF = objFSO.OpenTextFile(strFNameIn, 1, , False)
strTmp = objTF.ReadAll
strTmp = Replace(strTmp, Chr(9) & Chr(32), ",")
objTF.Close
Set objTF = objFSO.createtextfile(strFNameout, 2)
objTF.writeline strTmp
objTF.Close
End If
End Sub
[/VBA]

[bdl004]

Hey Dave,

Thanks a lot! Great idea.

Best Regards,

Brett

[brettdj]

Brett,

No probs

Note that you can also quickly read a text file into an array and use Split to separate the array by line, ie

Cheers

Dave[VBA]Sub GetIt()
Dim objFSO
Dim objTF
Dim strFNameIn As String
Dim strTmp As String
Dim X
strFNameIn = "C:\test.txt"
Set objFSO = CreateObject("Scripting.FileSystemObject")
If objFSO.FileExists(strFNameIn) Then
Set objTF = objFSO.OpenTextFile(strFNameIn, 1, , False)
strTmp = objTF.ReadAll
X = Split(strTmp, vbNewLine)
objTF.Close
End If
End Sub
[/VBA]

[bdl004]

Is it possible to separate the text file in to columns once it has been parsed in to lines? That would be ideal...

Thanks