?
Solved

flat file import

Posted on 2014-07-19
4
Medium Priority
?
197 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

764 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