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?
44 Replies
Use this online https://tableconvert.com/csv-to-csv
- 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....
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- 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 - 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.
Yes, this is the current behavior in Windows as well. Just set the column as TEXT and Excel will generate the enclosing double quotes in CSV if your column value has a comma in it. It will not generate the quotes in the rows that does not contains any comma.
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
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.
- This is the way
- 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.
- LibreOffice Calc does it by default 🙂
-Chris 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.
- This was the only solution that worked with my file. One problem though: if you have a number entered in the cell, even if the cell is formatted as [text] and special format is applied, the Double Quotation Character ["] will not be added.
To go around this problem I had to add Single Quotation Character ['] in front of the number that made excel to treat it as text and after exporting into csv format deleted all single quotation signs using Notepad.
If anyone has a better idea, please let me know. SergeiBaklan I tried this tip of putting \"@\" as the number format. The actual file ends up with """mytext""" in that column of values.
Use the PowerShell option , it is the only way I found that works winston_dowling
- 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>
