Inconsistent Output for Empty Columns in a CSV File

by Allen Wyatt
(last updated June 9, 2014)

1

Mark Warner noticed an odd thing when it comes to creating CSV files with Excel: the files are not always consistent in how they end each row in the output data. When he creates a CSV file that has, perhaps, 70 field (columns) of data and then views the CSV file in a text editor, he noticed that the records all contain carriage returns, but in different places. Some records have a string of commas representing empty fields, then terminate in the 'right' place; others end right after the last populated column; some have a few commas, but not enough for all the empty fields. Mark wondered why this occurs, and how he can get the CSV files to contain a consistent number of output fields.

This is actually a problem that has been known to Microsoft for quite some time. The Microsoft Knowledge Base includes an article on this topic as early as Excel 97:

http://support.microsoft.com/kb/77295

One relatively easy way around the issue is to include a fully populated "dummy" field in your data, before you save as a CSV. For instance, if your table has 70 columns in it, at cell A71 enter a period. Copy the contents of this cell downward, for as many rows as you have in the table. When you then export the worksheet to CSV, Excel will include the dummy field, but more importantly will include the proper number of field delimiters (commas) before that final field in each record.

If you don't want the dummy field, you can try this:

  1. Select one of the cells in your header row.
  2. Press Shift+Ctrl+8. The entire data table is selected.
  3. Press Ctrl+H to display the Replace tab of the Find and Replace dialog box. (See Figure 1.)
  4. Figure 1. The Replace tab of the Find and Replace dialog box.

  5. Make sure the Find What box is completely empty.
  6. In the Replace With box enter a single space.
  7. Click Replace All. Excel should inform you how many replacements were done.

These steps replace all the empty cells with cells that contain a single space. You can then do the export to CSV and the proper number of fields will be exported for every single row.

Finally, if you routinely export large tables to CSV format, you may wish to create a macro that does the file creation for you. The following is just one example of the type of macro you can use:

Sub CreateCSV()
    Dim wkb As Workbook
    Dim wks As Worksheet
    Dim wksOri As Worksheet
    Dim iCols As Integer
    Dim lRow As Long
    Dim iCol As Integer
    Dim lRows As Long
    Dim sFilename As String

    Application.ScreenUpdating = False
    sFilename = "C:\test.csv"
    Set wksOri = ActiveSheet
    iCols = wksOri.Cells. _
        SpecialCells(xlCellTypeLastCell).Column
    lRows = wksOri.Cells. _
        SpecialCells(xlCellTypeLastCell).Row

    Set wkb = Workbooks.Add
    Set wks = wkb.Worksheets(1)

    For lRow = 1 To lRows
        For iCol = 1 To iCols
            With wks.Cells(lRow, 1)
                If iCol = 1 Then
                    .Value = wksOri.Cells(lRow, iCol).Text
                Else
                    .Value = .Value & "," & _
                        wksOri.Cells(lRow, iCol).Text
                End If
            End With
        Next
    Next

    Application.DisplayAlerts = False
    wkb.SaveAs FileName:=sFilename, _
        FileFormat:=xlCSV
    wkb.Close
    Application.DisplayAlerts = True
    wksOri.Parent.Activate
    Application.ScreenUpdating = True
    MsgBox sFilename & " saved"

    Set wks = Nothing
    Set wkb = Nothing
    Set wksOri = Nothing
End Sub

The macro creates a brand new workbook and then "compiles" into column A of the workbook's first worksheet the information from the original worksheet. This data, which will contain a delimiter for every single field in the original, is then saved as a CSV file. Finally, the temproary workbook is deleted.

The path and filename of the CSV is hard-coded into the code (the sFileName variable), though it could be modified to have the code ask for a filename if desired.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3068) applies to Microsoft Excel 97, 2000, 2002, and 2003.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Changing Page Margins

Part of determining page layout is to specify the size of the margins that surround the text on a page. Word allows you to ...

Discover More

Standard Text before a Sequence Number

When you use fields to number items within a document, you may want to add some standard text before each field. There are a ...

Discover More

Selecting a Graphic Behind a Text Box

How to select a graphic that is obscured by a text box can be perplexing. Here's an overview of the different ways you can ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (menu)

Correctly Saving Delimited Files

Delimited files are often created through Excel so that your data can be exported to other programs. If the delimited data ...

Discover More

Closing Multiple Files

When working with multiple workbooks open, you may want a way to close them all with a single command. Here's the secret.

Discover More

Loading Unwanted Files at Startup

Imagine how painful it would be if every time you started Excel it tried to load all the files in your root directory? That ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 8Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 0 + 7?

2012-06-19 09:25:22

Ozmac46

Great! Can't wait to try it. This problem has been bugging me for a week. I have 110,000 data points in a 35 column spreadsheet which I want to toss into a (steam driven) GWBasic program, but the inconsistency of the record lengths in the .csv has been screwing up my logic. One other way around the problem of inconsistent packing of blank cells is to highlight the spreadsheet, copy/paste to Word (2003), then tAble convert to teXt and specify commas as the delimeter. Then save this as a .txt file with a .csv suffix. Word doesn't seem to have the same problems as Excel.

It's actually the same as changing the blank numeric cells to blank text, as you suggest as a solution in Excel.


This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.