Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have some csv data file that looks like this

1,2,3

5,6,7

I open it with Excel 2010, do some change (change some value, delete a column, or anything), and then re-save the file still as csv. But now the data becomes

1,2,3
,,
5,6,7

where the blank line in the middle are now filled with a row of empty fields separated with comma. I know this is not that much of a difference in this example but it gets really annoying when I have code to work with these two different formats. Is there a way to stop Excel from changing the format? Or is there another program I can edit csv file easily?

share|improve this question

2 Answers 2

up vote 1 down vote accepted

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.

share|improve this answer
    
This is great. It's good to know that there's a standard. I'll just convert all my data to the same format that excel uses. –  LWZ Jan 23 '13 at 17:55

csv files can be edited with notepad or any simple text editor

share|improve this answer
    
Yes, but simple task like deleting one column would be very difficult. –  LWZ Jan 23 '13 at 17:54

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.