loading Data in VBA from a text file

Question

I am not very familiar with VBA but need to use it for a new software program I am using (not Microsoft related)

I have a text file that has columns of data I would like to read into VBA.

Specifically the text file has 4 entries per row. Thus I would like to load in the column vectors (N by 1).

The text file is separated by a space between each entry.

So for example I want to load in column one and save it as array A, then column two and save as array B, then column three and save as array C, and then column four and save as array D.

This code snippet found below from http://www.tek-tips.com/faqs.cfm?fid=482 is something I found that can load in text to an array, but I need to adapt it to be able to save the columns as different arrays as specified above...

Open "MyFile.txt" For Input As #1
ReDim Txt$(0)
Do While Not EOF(1)
ReDim Preserve Txt$(UBound(Txt$) + 1)
Input #1, Txt$(UBound(Txt$))
Loop
Close #1

Answer 1

There is few detais in your question, but i would suggest using "Text to column"

If you're not very familiar with VBA programming try recording macro with this steps:

1. Import file to Excel
2. select column A
3. select "Text to columns" form tools menu
4. choose delimited by space

This way you'll get array of data you asked for, now assigning to any variables you want shouldn't be a problem.

EDIT (Without using Excel):

Take a look on that FSO method.

by replacing

MsgBox strLing

with some kind of split function, like

strTemp = Split(strLine, " ")

You'll be able to loop through all the values in your source file, would that work?

Answer 2

For this example, you will need a file called schema.ini in the same directory as the text file. It should contain:

[Import.txt]
Format=Delimited( )

Where Import.txt is the name of the file (http://msdn.microsoft.com/en-us/library/ms709353(VS.85).aspx).

You can then use this, which should work in VBScript or VBA with very little tampering:

Set cn = CreateObject("ADODB.Connection")

'Note HDR=Yes, that is, first row contains field names '
'and FMT delimted, ie CSV '

strCon="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Docs\;" _
& "Extended Properties=""text;HDR=Yes;FMT=Delimited"";"

cn.Open strcon

strSQL="SELECT * FROM Import.txt" _

set rs = createobject("adodb.recordset")

rs.open strSQL,cn

MsgBox rs(2)
MsgBox rs.GetString

The first message box should return the third column of the first row, it is a test that it works.

The second message box should return the whole file, so don't use it with a large set. The recordset can be manipulated, or you can use .GetRows to create an array of values (http://www.w3schools.com/ado/met_rs_getrows.asp)

Answer 3

Seems the remaining problem is to convert from an array of lines to four arrays of columns. Maybe this snippet helps

Option Explicit
Option Base 0

Sub import()
    Dim sTxt() As String
    Dim sLine As Variant
    Dim iCountLines As Long
    Dim iRowIterator As Long
    Dim i As Long
    Dim sRow() As String
    Dim sColumnA() As String
    Dim sColumnB() As String
    Dim sColumnC() As String
    Dim sColumnD() As String

    ' read in file '
    Open "MyFile.txt" For Input As #1
    ReDim sTxt(0)

    Do While Not EOF(1)
        Input #1, sTxt(UBound(sTxt))
        ReDim Preserve sTxt(UBound(sTxt) + 1)
    Loop
    Close #1

    ' dim array for each columns '
    iCountLines = UBound(sTxt)
    Debug.Print "working with ", iCountLines, "lines"
    ReDim sColumnA(iCountLines)
    ReDim sColumnB(iCountLines)
    ReDim sColumnC(iCountLines)
    ReDim sColumnD(iCountLines)

    ' "transpose" sTxt '
    iRowIterator = 0
    For Each sLine In sTxt
        sRow = Split(sLine, " ")
        If UBound(sRow) = 3 Then
            sColumnA(iRowIterator) = sRow(0)
            sColumnB(iRowIterator) = sRow(1)
            sColumnC(iRowIterator) = sRow(2)
            sColumnD(iRowIterator) = sRow(3)
            iRowIterator = iRowIterator + 1
        End If
    Next sLine

    ' now work with sColumnX '
    Debug.Print "Column A"
    For i = 0 To iCountLines
        Debug.Print sColumnA(i)
    Next i

End Sub