Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 199
  • Last Modified:

flat file import

folks

i have a txt file


inside the file in have values

car1, ford , 1003 , km, 19-7-14
car2, ford , 73 , km, 18-7-14

how do I import this into excel like so from columms A to E

Vehicle # Make Reading Meter  Date
car1        ford     1003        km   19-7-14
car2        ford       73          km   18-7-14

preferably use a macro to import the values and overwrite the previous ones on the sheet
0
rutgermons
Asked:
rutgermons
  • 3
1 Solution
 
5teveoCommented:
quick macro to copy data in starting at A2

so... pre-create template empty worksheet with headers only and use this command for Excel 2007.

Sub Macro1()
'
' Macro1 Macro
'

'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Users\Dad\Documents\Projects\A-ExpertExchange\EE-2013-03-22-Excel\ImportTextv1.txt" _
        , Destination:=Range("$A$2"))
        .Name = "ImportTextv1"
        .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 = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(2, 2, 1, 2, 7)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub
ImportText.xlsm
ImportTextv1.txt
0
 
5teveoCommented:
Oh -

change
'C:\Users\Dad\Documents\Projects\A-ExpertExchange\EE-2013-03-22-Excel\ImportTextv1.txt'

to point to your file!
0
 
rutgermonsAuthor Commented:
thanks Steve

seems to work but when running the macro for the second time it writes into columns E-I, ideally it should clear the page out

any ideas?
0
 
5teveoCommented:
Ah!

I modified to clear then import

'
' Macro1 Macro
'
   Range("A2").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.ClearContents

'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Users\Dad\Documents\Projects\A-ExpertExchange\EE-2013-03-22-Excel\ImportTextv1.txt" _
        , Destination:=Range("$A$2"))
        .Name = "ImportTextv1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(2, 2, 1, 2, 7)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now