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

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

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)