Forum Discussion
Save as CSV file (UTF-8) with double quotes - how?
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?
Hi Peter,
The idea is here http://lenashore.com/2012/04/how-to-add-quotes-to-your-cells-in-excel-automatically/.
In brief, convert your file to CSV, select your range and apply custom \"@\" format, save again.
When I do what you propose (format with double quotes) it will actually quote and escape them in the csv. So now it will apparently quote the fields after all, just when I didn't want it to...
Example:
(I'm using semicolon delimited btw)
text text2 -> text;text2
"text" "text" -> """text""";"""text2"""
Of course I could do some search and replace to make it work but honestly, it's embarrassing that this isn't a trivial operation in Excel.
Do you have any idea?
I agree entirely with this. I have the same problem. The official Microsoft response to this is to build a macro that exports a CSV in the format you want it. I also find the justification that "this is the up to date guidance" to be a sloppy one, as when I use CSV I often require backwards compatibility to systems not using that guidance that require double quotes around each field. It takes no effort to add functionality to export as such within Excel.
My preferred solution as offered elsewhere is: download and install Open Office/Libre Office and use their inbuilt functionality for it. It will save you having to track down a Macro for it every time you need to do this.
The idea is here http://lenashore.com/2012/04/how-to-add-quotes-to-your-cells-in-excel-automatically/.
In brief, convert your file to CSV, select your range and apply custom \"@\" format, save again.
It seems not to work with fields containing numbers
- If the cells contain number, you can apply custom \"#\" format.
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>
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.
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 cellsSub LEFT-RIGHT()Dim c As RangeFor Each c In SelectionIf c.Value <> "" Then c.Value = "¿" & c.Value & "¿"NextEnd SubThen 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
- LibreOffice Calc does it by default 🙂
-Chris 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!!
I found a quick work-around using powershell:
Import-Csv ".\SourceFile.csv" | Export-Csv ".\DestinationFile.csv" -NoTypeInformation
Hope this helps.
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
Hi Peter
Sorry I am not quite following what you are asking for.
I have saved cells with double quotes in them and they save fine in CSV file (UTF-8) in Office 365.
Can you please give an example of what type of data you have in " " and what version of Office you are using?
We can hopefully sort it out from there.
Thanks and have a great weekend.
Cheers
Damien
The format in the file should end up like this:
"product no","description","quantity"
"1254A","Green bucket","10"I got round this by replacing the double quotes in Excel with a character that I did not use in the spreadsheet (£ worked for me), exporting as a .csv file, and then, having opened the file in Notepad, replacing the £ with the double quotes.
Hello, you can change your file format to "CSV". You need to do the following:
- Click on File
- Select "Save As"
- Selec the folder where you need to save your document
- Select "Save as type", Click on the dropdown arrow to choose the new format, e.g., CSV file (UTF-8) Comma delimited.
I hope the above instructions help you.
Thank you
Elvira
Where is this mythical file type csv (utf8)? I only have csv, csv (dos), and csv (mac). I really need Excel to stop screwing up the foreign language characters when saving as csv. What is it that Excel actually saves them as that they don't work in my other apps?
thanx,
clarence
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.
- Hi, Need help. i m using lenovo ideapad s145 laptop with inbuilt windows 10 an and Excel, if i am pressing Ctrl+Shift+L keys as a keyboard shortcut for data filter, it results nothing whereas ctrl+c and ctrl+v shortcuts are working fine
Jitender_Sehgal This is not related. Please start a new thread.
- 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.
- 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 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
