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?
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.
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>
