using excel vba read and edit text file into excel sheet

Question

i would like to extract data from text file into excel worksheet.
my text file format is not the same for each line.
so for each line read, the first data would go input into the 1st excel column, and the next data to go into the 2nd excel column(same row) which is 2 or more blank spaces away from the 1st data. This goes on until all the text file data in that line are input into different columns of the same row.

text file:
data1 (space) data2 (space,space,space) data3 (space,space) data4

excel:  
column 1      | column 2 | column 3  
data1 data2   | data3    | data4

i do not know how to identify the spaces in each line to be written to excel sheet, pls advise, below is my code:

Sub test()
  Dim ReadData, myFile As String

  myFile = Application.GetOpenFilename()
  Open myFile For Input As #1

  Do Until EOF(1)
    Line Input #1, ReadData
  Loop
End Sub

CSAW · Accepted Answer · 2014-08-14 04:42:20Z

While David's Solution works fine, here is another way to go about it.

Like David's my solution assumes that each data piece is not broken. This solution also assumes that each new row (that has data) will be placed in the Sheet1 row after the prior row

You need to use the Split() function to separate the pieces of data into their respective Strings. Then, only using the strings with actual characters (i.e. no spaces or blank lines), you Trim the strings to remove spaces before or after your data(s) Once all this has occurred, you are left with desired elements in an array which you populate the columns with.

Sub test()
    'variables
    Dim ReadData, myFile As String
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    Dim s As Variant
    Dim stringTemp1() As String
    Dim stringTemp2() As Variant
    i = 1
    'get fileName
    myFile = Application.GetOpenFilename()
    Open myFile For Input As #1

    Do Until EOF(1)
        Line Input #1, ReadData
        'check to make sure line is not empty
        If Not ReadData = "" Then
            'split row into array of strings
            stringTemp1 = Split(ReadData, "  ")
            'remove any string elements that are blank
            j = 0
            ReDim stringTemp2(j)
            For Each s In stringTemp1
                If Not IsSpace(s) Then
                    ReDim Preserve stringTemp2(j)
                    stringTemp2(j) = s
                    j = j + 1
                End If
            Next s
            'remove excess spaces from each element when adding to cell
            For k = 0 To UBound(stringTemp2)
                Worksheets("Sheet1").Cells(i, k + 1).Value = Trim(stringTemp2(k))
            Next k
            i = i + 1
            Erase stringTemp2
            Erase stringTemp1
        End If
    Loop
    Close #1
End Sub

This external function was to check if an element in stringTemp1 contained data or not

Function IsSpace(ByVal tempString As String) As Boolean
    IsSpace = False
    If tempString = "" Then
        IsSpace = True
    End If
End Function

many thanks. unfortunately my data was not what David had predicted. i'll need to make adjustment from here, it is a good start, thanks again David & CSAW.

David Zemens · Accepted Answer · 2014-08-14 03:00:02Z

Assuming that each element of "data" does not internally contain spaces (e.g., your data is non-breaking, such as "John" or 1234 but not like "John Smith", or "1234 Main Street") then this is what I would do.

Use the Split function to convert each line to an array. Then you can iterate the array in each column.

Sub test()
  Dim ReadData As String
  Dim myFile As String
  Dim nextCol as Integer


  myFile = Application.GetOpenFilename()
  Open myFile For Input As #1

  Do Until EOF(1)
    nextcol = nextCol + 1
    Line Input #1, ReadData
    Call WriteLineToColumn(ReadData, nextCol)
  Loop
End Sub

Now that will call a procedure like this which splits each line (ReadData) and puts it in to the column numbered nextCol:

Sub WriteLineToColumn(s As String, col as Integer)
'Converts the string of data to an array
'iterates the array and puts non-empty elements in to successive rows within Column(col)

Dim r as Long 'row counter
Dim dataElement as Variant
Dim i as Long
For i = lBound(Split(s, " ")) to UBound(Split(s, " "))
    dataElement = Trim(Split(s)(i))
    If Not dataelement = vbNullString Then
        r = r + 1
        Range(r, col).Value = dataElement
    End If
Next

End Sub

NOTE ALSO that a declaration of Dim ReadData, myFile as String is declaring ReadData as type Variant. VBA does not support implied declarations like this. To properly, strongly type this variable, it needs to be: Dim ReadData as String, myFile as String.

don't know why data was inserted in different rows & not in columns of the same row

Collectives™ on Stack Overflow

using excel vba read and edit text file into excel sheet

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related