CODEWITHSUNDEEP
excelcsvexport-to-csv
Shmewnix
Shmewnix

Reputation: 1573

Excel Ruins CSV

I have a CSV file that gets exported from a vb.net program. I have a few fields where I manually type the data and then re-save the CSV (from Excel). Whenever I save the CSV from Excel it removes the ""'s from each column. How can I manually edit a CSV file, without destroying the formatting of the CSV?

Upvotes: 1

Views: 2215

Answers (3)

Wouter
Wouter

Reputation: 1987

Excel does not support (afaik) specifying in detail how the data should be exported to CSV (e.g. what delimiter, encoding, ect... to use). Access does. So, what you can do is, first import the CSV file into access, making sure you specify the delimiter/encapsulation used in your source file, and then use Access's more advanced 'export to text' method to export your data to a file with the desired encoding and encapsulation.

You should be aware, as @GolezTrol mentioned, that Excel does not "open" CSV files. It loads them and converts them into Excel files using default settings, and exports them to the file when you save, using default settings. These default settings can cause data corruption. Hence, you should not use Excel to edit and save data, and should only use it to read data when you use the import method, -not- the default settings.

I've written an in depth article on common problems and misconceptions around plain text data files, and ways to avoid data corruption when working with them: http://theonemanitdepartment.wordpress.com/2014/12/15/the-absolute-minimum-everyone-working-with-data-absolutely-positively-must-know-about-file-types-encoding-delimiters-and-data-types-no-excuses/

Upvotes: 0

Tom R
Tom R

Reputation: 68

CSV are plain text files (Comma separated values), hence they don't require any particular editor.

You can do the job with Windows Notepad, but other advanced editors will be easier to work with (give a try NotePad++, which will allow you to save your previous find and replace commands).

Depending on your version of Excel, I think you can configure it so it doesn't remove the quote marks.

Alternatively, you can add quote marks directly in Excel, the CSV output will then have 3 quote marks to delimit all your fields but that may be a way to deal with your problem.

Upvotes: 1

GolezTrol
GolezTrol

Reputation: 116180

The quotes around the fields are not mandatory, and are only needed when the field contains certain characters. So strictly, the file is still valid CSV and is not ruined at all.

Excel will load the entire CSV into memory, forgetting about its formatting completely and write an entirely new file. I don't thing there's a way to keep the existing formatting of a CSV file.

This thread on SuperUser links to some macro's that might help you.

Upvotes: 2

Related Questions