up vote 3 down vote favorite
share [g+] share [fb]

I have a csv file that constantly needs opened into Excel and then have the data copied over to a separate workbook.

I find the process of having to click through all of the dialogs, setting the text identifier, setting the columns to all be text extremely tedious.

In many actions with data like this in regards to MSSQL or Access the program will ask you if you wish to save these steps however Excel doesn't readily ask that.

Is there any way to get a comparable usage with Excel?

link|improve this question
92% accept rate
feedback

2 Answers

up vote 2 down vote accepted

Once you have imported the data, you can select the data range, right-click, and select "Refresh Data".

This will prompt you for a file – and it inserts the updated data without needing to go through all the steps you mention above.

It works in Office 2011 for OS X, but it should also work in earlier Windows versions.

link|improve this answer
So for this solution would you recommend just creating a .xslx file in the same directory as my csv file, do this once and then let the csv be overwriten as needed and push refresh? – Chris Marisic Jun 20 '11 at 16:38
It doesn't need to be in the same directory -- but yeah, after you've updated the CSV, hit "Refresh" and it'll ask you for a file, select that file again, done. Just try it out if it works for you! – slhck Jun 20 '11 at 16:42
This worked out great, any chance you know of a way for me to establish that I trust my file instead of getting the stupid data connection warning everytime? – Chris Marisic Jun 24 '11 at 12:58
I guess there's a way -- check out this help file from Microsoft about external content! – slhck Jun 24 '11 at 13:00
1  
Digging through that I found adding a trusted location is what I wanted to do. – Chris Marisic Jun 24 '11 at 13:10
show 1 more comment
feedback
up vote 2 down vote

Use the macro recorder, and record it all in a macro.

Here's what I got when I macroed a common import of mine:

With ActiveSheet.QueryTables.Add(Connection:="TEXT;E:\AEP_out.txt", _
    Destination:=Range("A1"))
    .Name = "AEP_out"
    .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 = xlFixedWidth
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = False
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(2, 1, 1, 1)
    .TextFileFixedColumnWidths = Array(27, 16, 22)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
End With
link|improve this answer
+1 recording a macro would definitely do it – Chris Marisic Jun 24 '11 at 12:58
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.