Excel – Any way to have Excel 2010 treat a quoted value “08540” as text not number when opening a CSV

csvmicrosoft excelmicrosoft-excel-2010

I have a CSV with data like this:

  "08540",1,"PRINCETON","NJ"

I know that it is possible to specify "Text" when using the Import Wizard and defining columns. But is there a way to tell Excel to treat quoted values as Text when Excel opens a CSV file?

Best Answer

There is no way to do it automatically. You need to convert the file to the following format first:

="08540",1,"PRINCETON","NJ"

If your files do have the format as you said (i.e. all you need is to add = in the beginning of every line) then the conversion is easy. Create a CONVERT.BAT file on your Desktop with the following code:

@echo off
mkdir "%USERPROFILE%\Desktop\Converted CSV files"
:next
if '%1'=='' goto done
set CSV="%USERPROFILE%\Desktop\Converted CSV files\%~nx1"
for /F "tokens=*" %%A in ('TYPE %1') do echo =%%A >>%CSV%
shift
goto next
:done

Now you can drag-n-drop your .CSV files on the CONVERT icon. The processed files will appear in the Converted CSV files folder.

Related Solutions

Mac – How to force Excel to treat dates in CSV as text in a macro

Usually one would select the data-type for each imported column as step 3 of 3 of the text-import dialog. To avoid misformatting, just use text as format, and you're good.

However, you seem to use a VBA macro to import your CSV files. So I just recorded such an uncorrupted text import:

With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;C:\test.csv", Destination:=Range("$A$1"))
    .Name = "test_1"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 850
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = True
    .TextFileCommaDelimiter = False
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(2, 1) 'THIS IS THE MAGIC LINE!
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
End With

Also, look at this example from excel-help for TextFileColumnDataTypes:

Set shFirstQtr = Workbooks(1).Worksheets(1) 
Set qtQtrResults = shFirstQtr.QueryTables _
        .Add(Connection := "TEXT;C:\My Documents\19980331.txt", _
        Destination := shFirstQtr.Cells(1, 1))
With qtQtrResults
    .TextFileParseType = xlFixedWidth
    .TextFileFixedColumnWidths = Array(5, 4)
    .TextFileColumnDataTypes = _
        Array(xlTextFormat, xlSkipColumn, xlGeneralFormat)
    .Refresh
End With

These are the formats you can use:

xlGeneralFormat
xlTextFormat
xlSkipColumn
xlDMYFormat
xlDYMFormat
xlEMDFormat
xlMDYFormat
xlMYDFormat
xlYDMFormat
xlYMDFormat

Excel Does Not Parse Quoted String containing comma in CSV File Correctly

Remove the extraneous spaces and it'll work correctly:

T7,7,"331-8463 C - Ind. HT collar, laser HT insert, crown TR",85,0.37,N,0.00,N
T7,8,"331-8463 C - Ind. HT collar, laser HT insert, crown TR",90,0.33,N,0.00,N

Best Answer

Related Solutions

Mac – How to force Excel to treat dates in CSV as text in a macro

Excel Does Not Parse Quoted String containing comma in CSV File Correctly

Related Question