6

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?

1
  • 1
    The question is more potent than it seems (actually I think the question is badly posed). If someone does know of a non Excel CSV editor, please post a reply! It's really annoying when Excel tries to change a date to a number format, or tries to change a phone number to an Exponential. For real heavy-duty data-manipulation purposes, Excel is is a very poor tool to manage CSV. Sep 20, 2016 at 19:03

5 Answers 5

1

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.

1
  • 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, 2013 at 17:55
1

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

1
  • 8
    Yes, but simple task like deleting one column would be very difficult.
    – LWZ
    Jan 23, 2013 at 17:54
1

The way I remove the extra rows is by opening the CSV file using Word and then using Find/Replace All to remove the blank rows. You just have to remember that a blank row shows the commas followed by a hidden paragraph marker. Turn on paragraph markers by pressing Control + * to see the markers. So for example using the original posters sample above, use the find/replace feature in Word (or even Wordpad which is included in all versions of Windows), type: ,,,^p for Find What and leave Replace as blank. You might have to turn on "show markup"

Once you are done, just save it (not save as). And then open the file in Excel.

0

You need to Open an empty Excel and use the import from text option from the DATA tab.

There you will have an import wizard, where you can select codification, delimitators and format of each column before importing.

After edit, you can save as CSV.

1
  • Yes, but that doesn't help the question about saving without commas in blank lines.
    – Chenmunka
    Jan 29, 2016 at 14:04
0

I came across this question and the and answers and they were helpful however it didn't give me everything I needed. I was needing to be able to manage and work with CSV Data without destroying the format as Excel is so famously known for. I find the the NEW Import Wizards are not conducive to a basic CSV Import - at least I didn't find it useful due to my not having the ability of importing the RAW Data.

Basically I'm using Excel Office 365 version 16 at the time of writing. I had to turn on the LEGACY CSV/Text Import Wizard... I hope Microsoft NEVER gets rid of this wizard without first providing an easier method to import raw data.

Using the below method I have no issues removing rows or columns after first making sure I have imported ALL columns as TEXT... when you save your changes be sure to save it as a CSV file.

Turn On Legacy Wizard:

In Excel navigate to File --> Options --> Data --> Check the box in the 'Show legacy data import wizards' section 'From Text (Legacy)'

Now to import your CSV or RAW Text File

Navigate to the Data Tab and within the 'Get & Transform Data' section choose Get Data --> Legacy Wizards --> From Text (Legacy).

Gif Showing how to turn on Legacy Import: Turn on CSV Legacy Import

Gif Showing how to import CSV Data: Import CSV Data

Hopefully this will help somebody else.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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