How to save double quotes and semicolon in a csv file while opening the file in excel 2010?

Question

I receive a CSV file from sever. The data inside it is delimited by semicolon and it is in UTF-8 format. For example, the data what we receive is:

 "NL 我喜"" mutilple""我喜"

When we open it for the first time in Excel 2010, it will show as

NL 我喜" mutilple"我喜

But when I modify (I just removed the letter L from the above text) this file and save it as CSV using excel and open it for the second time, it will look like

N inbetween我喜 (without double quotes)

I want the data after modification to be saved with double quotes.

Thanks

Answer 1

I wrote a VBA Macro and logic is:

1. If there is any double quote in the data cell, then duplicate the double quote.
2. If there is any comman or double quote present in the data cell, then enclose the whole data with double quotes.

Example:

• Step 1: NL 我喜" mutilple"我喜 will become NL 我喜"" mutilple""我喜
• Step 2: After step 2, the data will become "NL 我喜"" mutilple""我喜"

This code matches the description above

tempString = Sheets(1).Cells(lRow, lCol).Text                   ' Get the data from cell.
tempString = Replace(tempString, Chr(34), Chr(34) & Chr(34))    ' If there is double quote, then duplicate it.
CurrTextStr = tempString
pos1 = InStr(tempString, Chr(34))                               ' Get the position of double quote. If not present, it will be 0.
pos2 = InStr(tempString, ";")                                   ' Get the position of semicolon. If not present, it will be 0.
If (pos1 <> 0 Or pos2 <> 0) Then                                ' If there is any double quote or semicolon, then the whole data
    CurrTextStr = Chr(34) & tempString & Chr(34)                '       should be enclosed with double quotes.
End If
oAdoS.WriteText (";" & CurrTextStr)

Answer 2

I tried this using excel 2016

I opened Excel - Select File,open selected the "CSV" file and selected delimited, Semicolon as the separator and " as the text delimiter.

This was then imported as expected and I could edit the cell and delete the L as per you original question

Clicking SAVE does corrupt the file as you found and explained in other answers.

However select SAVE AS and selected UNICODE as per one of the comments - the file was saved UTF-8 as a TAB separated file with " as the text delimeter. Have you tried this? Is this not acceptable?

If I selected SAVEAS and selected UTF-8 Comma Separated then it was saved UTF-8 comma separated - I could not see a method of retaining the Semicolon as separator. You should however go through the SAVE AS options and see if one of the formats available in your version of Excel is acceptable.

If it is necessary to retain the semicolon then you could post process the saved file a text replacement script that understood quoting (so that , inside the quotes don't get replaced) A VBScript or Powershell script could be written to do this, alternately a VBA macro to concatenate all the cells into one cell with commas separating them could be used.

A single line Powershell dependent command to to that final conversion would be (Run from CMD prompt)

powershell -command "& import-csv 'CSVUTF.CSV'| export-csv 'PSCSVUTF.CSV' -Encoding 'UTF8' -Delimiter ';' -notype"

If your file does not have a header line with column names then a -header parameter will need to be provided and the output file will end up with an header line

Answer 3

Seems like this was answered years ago in StackOverflow: https://stackoverflow.com/questions/4221176/excel-to-csv-with-utf8-encoding

Basically, excel saves CSV using ASCII encoding only. The workaround is to use OpenOffice, Google Docs, or save as UTF8 encoded text. Not sure how that will affect other processes you are using with the manipulated file after that.