Save as CSV file (UTF-8) with double quotes - how?

[Peter Snabe]

Hi

I often need to save data as a CSV file (UTF-8) where all cells are surrounded by double quotes. I cannot find any way to do that in Excel - can it be true? Any idea how to do that?

[#2]

@Peter Snabe 

I also can't believe this is true...

When I set to type=text, no quotes

When I set customer format as \"@\" it comes out as """"DATA""""

So there's no way to make this work that I found.

Incredible that Excel cannot save a standard CSV file!!

[#3]

I found a quick work-around using powershell:

Import-Csv ".\SourceFile.csv" | Export-Csv ".\DestinationFile.csv" -NoTypeInformation

Hope this helps. 

[#4]

@JohnClement Thanks much for the reply! The following modification worked for me (semicolon csv with no quotes -> into comma delimited with double quotes, make sure to use PowerShell not CMD):

Import-Csv ".\source.csv" -Delimiter ";" | Export-Csv ".\target.csv" -NoTypeInformation

[#5]

@Peter Snabe 

I read through all the replies and best I can see there is STILL NO EASY WAY to do this simple task.

Excel can read a csv with double quotes, but it won't save it the same way.  

This is has been a big disappointment of mine since 2012, I can not believe there is no easier way.

So I continue to use Access. 

[#6]

Well there is two "not simple way" to do it.

1.You could use Kutools, Text->add text, you could use a character that you never use such as ¿ (ALT+168), at the beggining and at the end of each cell.
Then you save as CSV-UT8, after you use Notepad to replace ¿ for ".

2. Use a macro instead of Kutools to do the same.
Select the cells

Sub LEFT-RIGHT()

Dim c As Range

For Each c In Selection

If c.Value <> "" Then c.Value = "¿" & c.Value & "¿"

Next

End Sub

Then use Notepad to Search And Replace 

Not that hard, but could easily be inserted in options by Microsoft

I sometimes wonder myself: "How could people with no computer skills could do it?, but then i realize people with no computer skills don't use csv files" .. so i guess it's fine

[#7]

On the Mac, I just set the column to TEXT, and then it will export properly (with quotes when necessary) using the UTF-8 CSV option.

[#8]

If the cells contain number, you can apply custom \"#\" format.

[#9]

@cantikaliem_ Thanks, but that doesn't work if there is a mix of text and numbers.

I figure out this customer formula which will work for all scenarios.

\"#\";\"#\";\"#\";\"@\"

This works because you can have multiple custom formula's separated by ; for different data types.

<Format for POSITIVE Numer>;<Format for NEGATIVE Numer>;<Format for ZERO>;<Format for TEXT>

[#10]

@YellowCylinder It shouldn't be this hard though, do you agree? 

[#11]

On mac I use CSV editor (small fee) solves all the CSV manipulation. On Windows is a freeware tool called: Ron’s Editor.
To me it’s unbelievable that Excel can’t handle this in a user friendly way. I live in The Netherlands and CSV’s are exported with a semi colon (;) so we need to replace that too.

[#12]

Actually, I just did some testing and ended up not using my new custom format. As far as I can see Excel adds quotes if needed when a cell contains a comma whether you save it as UTF-8 or normal CSV, this is the latest Excel 2016. For my use case that is fine, although I can understand why someone people would want it fully on or fully off.

[#13]

All these replies and torture that people have just shows how retarded microsoft developers are. You cant paste data properly between two microsoft products. Their developers should be used only for transcribing from pdf to word and nothing else. Trashbags

[#14]

Okay, so as a software dev I've been cursing about this particular idiocy for years. There are a few fixes I've found

First off MS has gone with a "quotes only if something in the cell conflicts with the CSV format" approach. So

John, Doe, "said """hello""" to me, but only once"
Jane, Doe, did not.

This is even more obnoxious, insofar as it's both inconsistent, and still ignores some of the worst offenders: legitimate commas IN THE DATA. As a dev I'm often having to import/ingest large volumes of data, often times containing legitimate text passages. Excel makes this a bloody nightmare (especially on a mac).

Some things that have worked for me: if the text is to be simply output later on on-screen, a substitution pattern works great, If the text is going to wind up on a website, while in Excel, but BEFORE you export, replace all instances of `"` (double quote) with "&quot;" all `'` (apostrophes) with "&apos;", and all  `,` (commas) with "&comma;". The advantage here being that when the characters render again on screen, they will LOOK exactly as they originally did to the user. They won't know they've been swapped out for the HTML char codes, and browsers auto-correct this when you copy+paste out.

Not working on a webpage? Did you know that there's a difference between “ and " (colors are added by me to make it easier to see)?

Let me explain: A decade or two back, MS decided what the world really NEEDED was "smart punctuation". Basically, "we need quotation marks that are slightly angled and INVERTED so the start and end ones are DIFFERENT!" Don't ask me. The relevant bit is, though, 

"is not the same as“”, nor is ' and ‘, or even , and ‚. The green ones are the native, normal punctuation we've all grown up with. The red ones are "smart".

...BUT! They ARE different characters to the COMPUTER. Nothing that parses CSV will split on "‚". Or treat a string delimited “like this” as though it's a standalone unit "like this". This is relevant because again: if you do your replaces in excel before exporting, to a HUMAN on pretty much ANY MEDIA we cannot tell a difference. But you'll know that EVERY comma in your CSV is a true delimiter. As a developer I've lost hours of my life to trying to figure out why this code someone copied out of Slack won't run, until I eventually remembered to check that "their string" actually WAS one.

The others are programmatic fixes; I'm trying to post layperson-compatible, EASY solutions. If anyone wants the macro code, just drop a reply and I'll post again.

Hope this helps someone!

[#15]

This is awesome! Thanks.

[#16]

This can be accomplished by using MS Access.
Import the Excel data into a MS Access table. Right click on the table and select Export / Text file.
After you select "Delimited" there is a screen where you select the delimiter and the Text Qualifier.
Change the name from .TXT to .CSV

[#17]

@clarence potter 
If you "Save As" → "CSV (Comma Delimited)" and then click "Tools" → "Web Options" → "Encoding" , you can choose UTF=8 ... see screen shot below. 

[#18]

@Peter Snabe 

I use Powershell for this. Excel 2016 still has this issue so I work around. I do have code to  read from an xls a worksheet and convert it from excel, then do this again.  Pretty much a pain but it works pretty fast. 150,000 records import-csv/export-csv only take a couple minutes.

$dir = 'C:\SomeFile\And\Folder\Path\'
$filein = '20230304T0051PST-RealEstate-Values.csv'
$fileout = '20230304T0051PST-RealEstate-Values-PS.csv'
cd $dir ; import-csv -LiteralPath $filein | export-csv -LiteralPath $fileout -NoTypeInformation -Encoding UTF8

[#19]

@ZenAtWork 

Regarding the "smart quotes" vs regular quotes, is there a way to search for or replace the smart quotes?

Thanks

[#20]

Here we are, 5 years later, and Excel still can't handle the most basic of CSV tasks, such as a comma separated quoted file....

[#21]

@Sergei Baklan I tried this tip of putting \"@\" as the number format.  The actual file ends up with """mytext""" in that column of values.