CODEWITHSUNDEEP
vbaexcel
Duraholiday
Duraholiday

Reputation: 111

Reading a TXT with commas into individual cells

I have a txt file that has the following data example

hz,test1,test2,test3,test4,test5,

1,#,#,#,#,#,
2,#,#,#,#,#,
3,#,#,#,#,#,
4,#,#,#,#,#,
5,#,#,#,#,#,
6,#,#,#,#,#,
7,#,#,#,#,#,

. . etc

So each test as a reading at the 1,2,3,4,5 frequencies

but when I use my code it reads all the text into the first column instead of each being in separate columns. I thought the "," were supposed to divide it up so each section that is between commas goes in a separate columns and rows?

right now looks like this enter image description here

Id like it to look like enter image description here

Code:

Dim DataFileName As String
Dim DFNamePath As String
Dim Handle As String
Dim i As Integer

i = 2

DataFileName = Cells(1, 2).Value

DFNamePath = "C:\Users\aholiday\Desktop\Data Dump\" & DataFileName & ".txt"

If MsgBox("Import RF Data?", vbYesNo) = vbYes Then

    Open DFNamePath For Input As #1
    Do Until EOF(1)
        Input #1, Handle
        Cells(i, 1) = Handle
        i = i + 1
    Loop
    Close #1

    Else
    Exit Sub
End If

I also get a runtime error 62 input past end of file during the loop, I think on the loop last pass

Upvotes: 1

Views: 89

Answers (1)

user4039065
user4039065

Reputation:

You can readily split the imported TXT once it arrives in the worksheet with the Range.TextToColumns method. 

Sub split_Import()

    Application.DisplayAlerts = False  '<~~ take the traing wheels off
    With Worksheets("Sheet4")
        With .Columns(1)
            .TextToColumns Destination:=.Cells(1), DataType:=xlDelimited, _
                           TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
                           Comma:=True, Tab:=False, Semicolon:=False, _
                           Space:=False, Other:=False, _
                           FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), _
                                            Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1))
        End With
    End With
    Application.DisplayAlerts = True

End Sub

Your data is pretty straightforward so it should not be necessary to set any of the xlColumnDataType FieldInfo values beyond a General output.

Direct Workbooks.OpenText method

With an unknown number of columns, the Workbooks.OpenText method may be a better option. Note its close resemblance to the Range.TextToColumns method. The array of FieldInfo xlColumnDataTypes is optional.

Sub split_Import()
    Dim fn As String, fp As String, ws As Worksheet

    fp = Environ("TMP") & Chr(92)      '<~~ set the TXT source folder
    Set ws = Worksheets("Sheet3")      '<~~ set destination worksheet

    With ws
        fn = .Cells(1, 1).Value2 & ".txt"
        Workbooks.OpenText Filename:=fp & fn, startrow:=2, DataType:=xlDelimited, _
                           TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
                           Comma:=True, Tab:=False, Semicolon:=False, _
                           Space:=False, Other:=False, local:=False
        With ActiveWorkbook
            With .Worksheets(1).Cells(1, 1).CurrentRegion
                ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Resize(.Rows.Count, .Columns.Count) = .Value
            End With
            .Close False
        End With
    End With
End Sub

Note that I have discarded the header (e.g. startrow:=2) to match the first of your data images. Set to startrow:=1 if you want to retain the header.

Be aware that I had some trouble with your merged cells interfering in locating the first empty row in column A. If you plan to keep them, you may have to make adjustments.

Upvotes: 2

Related Questions