Solved

flat file import

Posted on 2014-07-19
4
191 Views
Last Modified: 2014-08-03
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
Comment
Question by:rutgermons
  • 3
4 Comments
 
LVL 8

Expert Comment

by:5teveo
ID: 40206886
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
 
LVL 8

Expert Comment

by:5teveo
ID: 40206888
Oh -

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

to point to your file!
0
 

Author Comment

by:rutgermons
ID: 40206964
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
 
LVL 8

Accepted Solution

by:
5teveo earned 500 total points
ID: 40210626
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

ScreenConnect 6.0 Free Trial

Want empowering updates? You're in the right place! Discover new features in ScreenConnect 6.0, based on partner feedback, to keep you business operating smoothly and optimally (the way it should be). Explore all of the extras and enhancements for yourself!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question