Solved

flat file import

Posted on 2014-07-19
4
192 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

726 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