CODEWITHSUNDEEP
excelvba
Geoffrey
Geoffrey

Reputation: 467

Convert a txt file that uses space delimiter to tab delimiter

I have an excel file that is importing pricing from various txt files that are automatically generated by a third party. I hit a road block with one of the txt files because it uses space delimited whereas the others use tab. Because of the space delimited, when i split the data i get different values in each column.

Here is what I have so far to open and read the text file

        usFileName = PathName & "\" & "Prices.txt"

        If fs.FileExists(usFileName) Then
            Set US = fs.OpenTextFile(usFileName, 1)

            theData = US.ReadLine
            getDate = Split(theData, Chr(0))
            curDate = Trim(Left(getDate(0), 10))

            If curDate = ActiveSheet.Range("Sheet_Date") Then
                    Do While Not US.AtEndOfStream
                        On Error Resume Next
                        Ln = US.ReadLine
                        Cols = Split(Ln, "  ")
                        Price = Trim(Cols(7))
                        NameTrim = Trim(Replace(Cols(1), "USO-", ""))
                        CellName = Replace(NameTrim, "-", "_") & "_" & Trim(Cols(2))

                        If ActiveSheet.Range(CellName) Is Nothing Then
                            ''Do Nothing here
                            On Error Resume Next
                        Else

                            Set TxtRng = ActiveSheet.Range(CellName)

                            If TxtRng = ActiveSheet.Range(CellName) Then
                                TxtRng.Value = Price
                            End If
                        End If
                    Loop

                Else
                    MsgBox ("The current sheet date does not match the US file import date.")
                End If

            US.Close
        Else
            MsgBox ("The file Prices.txt does not exist.")
        End If

This is what the txt file looks like:

01/11/2019 06:00 PM  USO-FOX-USO  E10           8.9929     0.0000
01/11/2019 06:00 PM  USO-FOX-USO  CON8HE10      1.3212    -0.0244
01/11/2019 06:00 PM  USO-FOX-USO  CON8HE10TT    1.3232    -0.0244

And this is what the Cols variable looks like.

Cols Output Cols Output 2

Upvotes: 0

Views: 1725

Answers (2)

IAmNerd2000
IAmNerd2000

Reputation: 771

You can try removing spaces and replacing them this way:

    Ln = US.ReadLine
    Do While (InStr(Ln, "  ") > 0)
        Ln = Replace(Ln, "  ", " ")
    Loop
    'The only problem I see is the case where you have Time (6:00 PM) this 
    '    would replace the space with Tab. in that case, I would do the following:
    Ln = Replace(Ln, " PM", "PM")
    Ln = Replace(Ln, " AM", "AM")
    Ln = Replace(Ln, " ", vbTab)

    'And then put them back
    Ln = Replace(Ln, "PM", " PM")
    Ln = Replace(Ln, "AM", " AM")

    'Finally, split the columns
    Cols = Split(Ln, vbTab)

If you want to try with fixed width then do the following (after Ln = US.Readline):

    '01/11/2019 06:00 PM  USO-FOX-USO  E10           8.9929     0.0000    
    'Do the following only if (InStr(Ln, "  ") > 0)
    strCol1 = Mid(Ln, 1, 21)  
    strCol2 = Mid(Ln, 22, 13)  
    strCol3 = Mid(Ln, 35, 13)  
    strCol4 = Mid(Ln, 48, 11)  
    strCol5 = Mid(Ln, 59, Len(Ln))

Upvotes: 0

Dy.Lee
Dy.Lee

Reputation: 7567

Try

Sub Test()

    Dim PathName As String
    Dim usFileName  As String

    PathName = "Your path"
    usFileName = PathName & "\" & "Prices.txt"

    Workbooks.OpenText Filename:=usFileName, _
        StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), _
        Array(19, 1), Array(32, 1), Array(48, 1), Array(55, 1))

End Sub

Upvotes: 1

Related Questions