I want to export a huge excel file as a .csv, but the data contains commas within the cells.
How do I export the excel data to a .csv with the deliminator as this |
I've tried doing the usual "save as", but it is not working for my data.
2 Answers
Hi Check out this method in the below link, its what I have used in the past
Summary:
Control Panel –> Region and Language, and then click the Additional settings
When in the additional settings find “List separator”
Change this to anything you want, in your case you would want the | pipe
By definition CSV stands for Comma Delimited.
Easy alternatives that were under your nose in the Save As window are:
"Formatted Text(Space Delimited)" though this won't be appropriate if you have spaces in your cells
"Text(Tab Delimited)" it is less likely you'll have tabs in your cells and people won't type those in to excel.
If you really want the 'pipe' | then I would suggest saving as tab delimited and running some kind of find & replace on the resulting text file. This is a bit of a workaround
A even bigger workaround is to concatenate the cells using a formula on the sheet with a | involved and the save as text only that column.
Related