Aug 02 2018 09:01 PM
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?
May 18 2020 06:38 AM
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!!
Jun 02 2020 01:26 PM - edited Jun 02 2020 01:27 PM
I found a quick work-around using powershell:
Import-Csv ".\SourceFile.csv" | Export-Csv ".\DestinationFile.csv" -NoTypeInformation
Hope this helps.
Jun 10 2020 07:10 AM
@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
Oct 29 2020 08:06 AM
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.
Jan 24 2021 08:52 AM - edited Jan 24 2021 08:56 AM
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
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
May 11 2021 11:09 AM
Jun 17 2021 08:14 PM
Aug 02 2021 06:51 AM - edited Aug 02 2021 06:51 AM
@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>
Aug 02 2021 07:03 AM
@YellowCylinder It shouldn't be this hard though, do you agree?
Aug 02 2021 10:25 AM
Aug 02 2021 10:36 AM
Sep 09 2021 02:32 PM
Oct 25 2021 07:25 PM - edited Oct 25 2021 07:30 PM
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 """ all `'` (apostrophes) with "'", and all `,` (commas) with ",". 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!
Oct 27 2021 02:13 AM
Apr 05 2022 08:11 AM
Nov 29 2022 01:25 PM
@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.
Mar 24 2023 01:36 PM
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
May 02 2023 09:34 PM
Regarding the "smart quotes" vs regular quotes, is there a way to search for or replace the smart quotes?
Thanks
Oct 04 2023 07:22 AM
Oct 06 2023 10:33 AM
@Sergei Baklan I tried this tip of putting \"@\" as the number format. The actual file ends up with """mytext""" in that column of values.