4

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

  • I don't know a good solution for that. Save it as an Excel file (.xls*), not as .csv – Máté Juhász May 17 '17 at 12:52
  • I tried already, but that didn't help. – NJMR May 18 '17 at 4:57
  • Can you post a small example file? – harrymc May 22 '17 at 8:19
  • Sorry, security issues. You can save the above text using a notepad and extension as .csv and try opening it in excel. – NJMR May 22 '17 at 9:12
  • As you already know the quote " inside a string are escaped doubling them (Common Format and MIME Type for Comma-Separated Values (CSV) Files). Try to see if you can "import/export" the .csv file somehow forcing that the cells have not to be quoted (or have to be quoted). (It should exists some checkbox similar to "Quote all text cells"). You should be even able to force the file to be imported as a text file... and exported with the same rules ;... – Hastur May 24 '17 at 12:06
2
+25

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

1

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.

  • I am not allowed to use any other tools, since this file will be going to customers, I can't ask them to install OpenOffice or use other stuffs. – NJMR May 23 '17 at 5:13
  • Not sure I explained it correctly in excel one of the save as file type options is Unicode Text. However, it uses the .txt extension, meaning you'll have to rename it to .csv manually, or maybe with VBA. But I think it does what you want. – gns100 May 24 '17 at 21:48
0

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)

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.