Solved

flat file import

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

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…
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

717 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