Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
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.

share|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
Digging through that I found adding a trusted location is what I wanted to do. – Chris Marisic Jun 24 '11 at 13:10

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", _
    .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
share|improve this answer
+1 recording a macro would definitely do it – Chris Marisic Jun 24 '11 at 12:58

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .