How can I set Excel to always import all columns of CSV files as Text?

Question

Though I try to avoid it, I occasionally have to open a CSV file in Excel. When I do, it formats columns containing numbers, which makes them useless for my purposes. As far as I can tell, the only way to prevent this from happening on import is to rename the file so the extension isn't .csv and use the import wizard to specify the format of each column individually. For files with 50-60 columns, this is impractical.

Since every answer for this oft-asked question on the internet suggests either some means of converting the formatted numbers back once the file is open (which won't work for me - I want to solve the general problem, not a few specific cases) or manually selecting the format type of each column (which I don't want to do), I'm looking for a way to set a global preference or style such that all columns of all CSV files opened are always formatted as text. I know about "armoring" the numbers with quotes, too, but the files I get don't come like that and I was hoping to avoid having to pre-process the files so Excel doesn't screw them up.

Is there a way to do specifically this: Always format all columns in opened CSV files as text, without manually selecting each column every time during import?

I'm using Excel 2003, but I'll take answers for 2007 if that's what you know.

Answer 1

Interesting that this seems to be such a mystery for The Internet. I thought this would be some sort of challenge, but really it's quite straightforward in VBA. This works:

Sub OpenCsvAsText(ByVal strFilepath As String)

    Dim intFileNo As Integer
    Dim iCol As Long
    Dim nCol As Long
    Dim strLine As String
    Dim varColumnFormat As Variant
    Dim varTemp As Variant

    '// Read first line of file to figure out how many columns there are
    intFileNo = FreeFile()
    Open strFilepath For Input As #intFileNo
    Line Input #intFileNo, strLine
    Close #intFileNo
    varTemp = Split(strLine, ",")
    nCol = UBound(varTemp) + 1

    '// Prepare description of column format
    ReDim varColumnFormat(0 To nCol - 1)
    For iCol = 1 To nCol
        varColumnFormat(iCol - 1) = Array(iCol, xlTextFormat)
        ' What's this? See VBA help for OpenText method (FieldInfo argument).
    Next iCol

    '// Open the file using the specified column formats
    Workbooks.OpenText _
            Filename:=strFilepath, _
            DataType:=xlDelimited, _
            ConsecutiveDelimiter:=False, Comma:=True, _
            FieldInfo:=varColumnFormat

End Sub

Usage:

OpenCsvAsText "C:\MyDir\MyFile.txt"

Comma-separated file is now open as Excel sheet with all columns formatted as text.

Note that @Wetmelon's wizard solution (if you can parse it out of the surrounding confusion) works just fine, but if you're opening many files then you may, like me, grow weary of, each time, scrolling to column 60 in order to Shift-Click it.

EDIT @GSerg claims in the comment below that this "doesn't work" and "eats spaces and leading zeroes". I'll just quote the comment to the question, which is more descriptive:

For reasons unknown, even if you explicitly provide formats for all columns in VBA, Excel will ignore it if the file extension is CSV. As soon as you change the extension, that same code will yield the correct results.

So the code above "works", but gets killed by this ridiculous Excel behaviour. Whichever way you cut it, you're stuck having to change the extension to something other than ".csv", sorry! After that, you're home free.

Answer 2

You can try by opening a .xlsx first, then creating a data connection and importing the .csv. Select "comma" delimited, then select the option to treat all columns as text rather than "General".

Edit: Oh, I didn't fully read the question. In import wizard, select the first column header that you want to import as text, scroll to the final column header, and Shift+Click the header. Then select the "Text" radial option.

Answer 3

If you are always importing the same data (constant record format, layout, etc...) you could write an Access macro using an import spec and then dump the data back out to Excel. Done this may times. The other way that I have done it is to use VBA and read the data into the worksheet one record at a time and parse it out as it reads. As far as I know there is no way to set a default format during import in Excel and even if you could it would cause problems with the next file type you try to parse.

Answer 4

As request, submitting my comment as an answer (with a little more info added):

Hey Scripting Guy did a blog article about Importing CSV into Excel that might have some useful tidbits for you. Playing with the data object inside powershell may allow you to do what you want.

Although the article specifically just mentions importing the data into the cells which may leave the number format, it may be possible to play around with some of the Excel ComObject properties and methods to force the data to enter the cells as raw text instead (or force the formatting of the cells into text before or after the import).

Answer 5

For reasons unknown, even if you explicitly provide formats for all columns, Excel will ignore it if the file extension is CSV.

Some options:

• Create a query to import the data, as Wetmelon suggests.
  Disadvantage: you may be missing CSV database drivers on a 64-bit machine.

• Use Jean's code, but incorporate copying the file to a temporary folder and changing the copy's extension.
  Disadvantage: No link to the original file (saving will overwrite the copy); you will have to manually delete the copy afterwards. Still, you can manually Save As over the original CSV.

• Open the CSV in Notepad, Ctrl+A, Ctrl+C, paste to Excel, then Data - Text to Columns, then it's the usual wizard where you can set all columns to Text in one go. It's a different flavour of the previous option, because it also hides the precious extension from Excel.
  Disadvantage: manual.

• Have a very simple VBA loop that reads the whole file into memory and puts it onto the sheet, cell by cell.
  Disadvantage: slower, ugly.