Excel – How to Force Excel to Open CSV Files with Data Arranged in Columns

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

A blank line in a CSV file means it doesn't conform to the syntax that is used in most—if not all—CSV implementations. You can read about that in RFC 4180:

Each line should contain the same number of fields throughout the file.

What Excel ouputs is actually the correct representation of a blank row, since a CSV file always has the same number of columns. You won't be able to use another CSV editor, since they'll all either output standardized CSV format or might not even be able read your file properly. They might skip the blank line, or output it like Excel.

Here are some possible solutions:

• You can't change the way Excel saves CSV files. If you want to break a butterfly on a wheel you need to write VBA code to manually export your file.

• Change the original CSV files so they don't contain blank rows.

  If you need blank rows to identify non-contiguous data or separate tables, consider creating separate CSV files for each part, or making the table identifier another column in the CSV to get a proper tabular representation.

• Change your code to work with Excel's representation and thus follow the standards that have been established for CSV. Simply treat a row with empty fields as empty.

• Modify the CSV files in any plain text editor (Notepad, etc.) to remove the offending lines.