Microsoft Office Forums - View Single Post - importing and xonditioning data from a text file

NoSparks · 03-14-2018, 01:53 PM

Try giving this a test run.

I used the macro recorder for importing the text file using semicolon and equal sign as delimiters.
Then wrote a little macro to delete some columns, split the date/time to adjacent cells and delete the original date/time column.

Paste this into a standard module of a new workbook.
You'll need to adjust for your own text file location.

Code:

Sub ImportTextFile()
'
' as recorded by macro recorder
'
Application.ScreenUpdating = False

    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;D:\Forum Stuff\2018\2018_03_01\norcross\uroven121.txt", Destination:= _
        Range("$A$1"))
        .Name = "uroven121_5"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileOtherDelimiter = "="
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    
Call Manipulate
    
Application.ScreenUpdating = True

End Sub



Private Sub Manipulate()

    Dim lr As Long
    Dim cl As Range
    
With ActiveSheet
    .Range("A:C,E:G,I:I,K:K,M:S").Delete Shift:=xlToLeft

    lr = .Cells(Rows.Count, "E").End(xlUp).Row
    For Each cl In .Range("E1:E" & lr)
        cl.Offset(, 1).Resize(, 3).Value = Split(Trim(cl.Value), " ")
    Next cl
    .Columns("E").Delete Shift:=xlToLeft
End With
End Sub