Excel macro to easily import a CSV as all text

excel_import_csv.md

Importing CSV into Excel as text

Importing a CSV into Excel is annoying, because by default it will try to interpret the columns, and it often/usually gets it wrong. You can control what format is used for each column when you import a CSV file, but it's fiddly, and for many purposes we'd just like to load a CSV and have every column to be read in as uninterpreted text, without having to faff with the import wizard.

Good news: we can do that with a macro. Here it is:

Sub importCSV()

    Dim column_types() As Variant
    
    csv_path = Application.GetOpenFilename()
    
    If csv_path = False Then
        Exit Sub
    End If
               
    For i = 0 To 16384
        ReDim Preserve column_types(i)
        column_types(i) = 2
    Next i
               
    With ActiveWorkbook.Sheets(1).QueryTables.Add(Connection:="TEXT;" & csv_path, Destination:=Range("A1"))
        .Name = "importCSVimporter"
        .FieldNames = True
        .AdjustColumnWidth = True
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = column_types
        .Refresh BackgroundQuery:=False
    End With

    ActiveWorkbook.Sheets(1).QueryTables("importCSVimporter").Delete

End Sub

How to install/use this?

These notes are based on my experience with Excel for Mac, but ought to be applicable to other versions, possibly with a little adaptation...

It's best to store this macro in the "Personal Workbook", as this makes it available to all workbooks including newly created blank ones. Following the process outlined here:

• Record a new macro (e.g. menu "Tools / Macro / Record Macro") and under "Store macro in:" select "Personal Macro Workbook". (This will create that workbook if need be, wherever it lives.) Give the macro any name you like, click "OK" to start recording, then immediately stop recording with, e.g. menu "Tools / Macro / Stop Recording".

• Then open the Visual Basic editor (e.g. menu "Tools / Macro / Visual Basic Editor"). In the "Project" sidebar on the left there should be a "Personal Macro Workbook" project, and under its "Modules" folder there should be a module containing your newly-created (but empty) macro.

• Open that in the editor and replace it with the code above, which was copied/adapted from here via here.

Once you've done that, you should be able to open a new blank workbook, and run the macro via "Tools / Macro / Macros" - select the importCSV macro from the dialog that opens and click "Run". It'll ask you where the CSV file is you want to import, and then it'll import it into your open workbook at cell A1. It'll even set the column widths for you, too. :-)

To make running the macro easier, you can give it a hotkey. Or - even better - at least in my version of Excel for Mac, you can easily add a button for this to the "Quick Access Toolbar", which is the line of icons in each window's menu bar (at the same level as the minimise/close buttons); there, choose the rightmost button ("Customize Quick Access Toolbar"), and add a button for the macro. It'll show up as a circle but it works. Yay!