2

I have a CSV file that I want to import to excel, edit, and save back as CSV.

The problem that I encounter is with Text Import Wizard: I open a new Excel file, then Data > From Text, and in The text Import Wizard I pick delimited>comma>Finish. This shows the data in easy-to-read columns. I make the needed changes, then I want to export as CSV, but if I go through File > Save As > CSV, it's no longer in the original format, it does not have commas/quotation marks because the Import Wizard removed them.

Is it possible to go in reverse with the import wizard and add back the commas? How can one import CSV, make changes, and export to CSV keeping the formatting exactly as it was?

I am using Excel 2007, Windows 7 PC.

Thanks for your help.

Improve this question
10
  • Are you sure that the incoming format is "escaping" the commas with double quotes as the default import method assumes? If not you have to change the Text Qualifier to {none} or whatever it is using (perhaps single quotes?) – Madball73 Feb 25 '14 at 18:11
  • Thanks for the reply. Changing the 'Text Qualifier' to 'None' did the trick for keeping the double quotes; but when I Save as CSV, the new file has the data separated to several columns, it does not join columns with commas, as it was in the original. Any thoughts? – user303184 Feb 25 '14 at 18:51
  • Which CSV are you using? My Excel 2010 has 3 of them. 1- CSV (Comma Delimited) 2- CSV (Macintosh) 3-CSV (MS-DOS). Also check your Region and Language settings in the windows control panel (see (howtogeek.com/howto/21456/…)) – Madball73 Feb 25 '14 at 18:59
  • In Save As, I use "CSV (Comma Delimited).csv." I see the other two as well: CSV (Macintosh), CSV (MS-DOS). – user303184 Feb 25 '14 at 19:01
  • looks like you responded before my edit. Check your regional settings too. – Madball73 Feb 25 '14 at 19:03
2

Final Solution involves understanding the both incoming format (i.e. what is being used as an escape character and separator) and setting the export behavior of excel.

  1. Change the Text Qualifier to {none} in the import data wizard
  2. Despite its name, Excel can export to CSV using other delimiters, and uses settings from the windows regional settings to decide (as detailed here.). Make sure that you have the list delimiter set to comma in this case.
Improve this answer

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.