Csv to XLS and again csv

[thedogg]

I have example of csv file, I want to open that file in xlsx file split in the columns and then save exactly the same again, the file contains chinese characters.

The best would be to have vba code but I even cannot save that file manualy to keep the same.

Could you please support?

Information
"ZDRTRMSD,""CA_3_16666"","""",""COMPUTER"",""Olek Belek"",""-"",""-"",""belek.olek@gmail.com"""
tData
edients
"1,""1"","""",""TEST_1"","""",""RTRMSD"",""TEST_1"","""",""54.5"","""",""54.5"",""0"",""0"",""0"",""0"","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""",""2"","""","""","""","""","""""
"1.1,""1"",""CA_5_2131321"",""Color"",""Color"",""ZDRTRMSD"",""3425545656"","""",""54.5"",""1"",""54.5"",""0"",""0"",""0"",""0"",""1"",""2018-03-21 15:34:25"",""2014-03-21 15:43:52"",""2014-03-21 15:43:40"","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""",""1"","""","""","""","""","""""
"1.1.1,""3"","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""","""",""CA_8_18555464654"",""E/P"","""",""呵呵"",""E/P"","""","""",""5.1.b"",""E/P"",""ISO 1043"","""",""0"",""0"",""2016-05-28 15:21:46"",""2016-05-28 15:27:08"",""2013-08-28 15:26:57"","""","""","""","""","""","""","""","""","""","""",""2"",""52.32"","""","""","""","""""

 

[thedogg]

I have everything what I need now except for chinese characters, seems to be the final file is not Unicode but ANSI therefore the characters are unvisible.

 

[thedogg]

I am trying to preapare XLSX template which will be able to modify, at the end I want to generate the CSV file, exactly the same as I have attached in first post. First I am trying just to have XLSX with exactly the same values. The csv file has been downloaded from the program, I want to upload it back now. I can do it, with modifications, only when I modify the CSV file in notepad, in excel directly (csv/xlsx) it is impossible.


I am trying also with different code like below to keep chinese characters, but I still cannot generate the same csv file. The input is the CSV file from post 1, saved as xlsm and text changed on columns. The input file doesn't contain "". In the code I am trying to have the txt file generated as with encoding: unicode. But something seems to be wrong because I have it as ANSI.

Sub SaveAsUnicode()
  Dim arr1() As Variant
  Dim Delimiter As String
  Dim Filename As String
  Dim Filepath As String
  Dim R As Long
  Dim Rng As Range
  Dim Text As String
  Dim TextFile As String
  Dim Wks As Worksheet
  
    Filepath = "[URL="file://\\acc.kol.comf\ADMIN\CSV"]\\acc.kol.comf\ADMIN\CSV[/URL] - files for export"
    Filename = "Unicode Test"
    Delimiter = StrConv(",", vbUnicode)
    
    Set Wks = Worksheets("Sheet1")
    Set Rng = Wks.UsedRange
    
      ReDim arr1(1 To Rng.Rows.Count)
    
      TextFile = Filepath & Filename & ".csv" 'txt
      
      Open TextFile For Output As [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1"][URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] [/URL] 
        For R = 1 To Rng.Rows.Count
          arr1 = WorksheetFunction.Index(Rng.Rows(R).Cells.Value, 1, 0)
          Text = Join(arr1, Delimiter)
          Print [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1"][URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] [/URL] , StrConv(Text, vbUnicode)
        Next R
      Close [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1"][URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] [/URL] 
      
End Sub

Seems to be that the problem is in Delimiter = StrConv(",", vbUnicode). Unfortunatelly I cannot confirm it with debug.print becayse I cannot display chinese characters I have only "????????". After that in step Print #1 , StrConv(Text, vbUnicode) instead of "????????" I have "YNdp/ NdpqQZ€ir" no more CN characters

 

[thedogg]

Today I am able to create csv file with all characters I need but the encoding is UTF-8, can I change it somehow now to have encoding - UNICODE? Manually I can open csv as txt then save as and select unicode. Could you please support me to do it via vba code?

I am using:

ActiveSheet.SaveAs Filename:=strFilename, FileFormat:=xlCSVUTF8, Local:=False

Seems to be that only UTF-8 can keep chinese characters as I wish.

 

[Joe4]

Sorry, I cannot really comment on the Chinese characters. I have never worked with them. I would be in the same boat as you, and would be dependent on Google searches to try to search for answers on that.

 

[thedogg]

is there any way then to open txt file with utf-8 encoding and save as unicode. If I can do it via vba macro I have what I need.

 

[Joe4]

is there any way then to open txt file with utf-8 encoding and save as unicode. If I can do it via vba macro I have what I need.

Look like you already posted that question here: https://www.mrexcel.com/forum/excel...n-txt-file-save-again-different-encoding.html

Note that the duplicate posting of questions is against forum rules (rule 12 here: [/COLOR]https://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html). Asking the same question in multiple threads often leads to confusion and can lead to hard feelings (when someone takes the time to craft a solution only to learn later that the question was already answered in a different thread). That is the reason we do not allow it.

So I am going to direct anyone responding to this latest question to answer in that other thread.

 

[thedogg]

Yes, I have done it because I had thought that this is rather completly different topic. Now I asked again because you have answered me.

 

[Joe4]

Now I asked again because you have answered me.

Bottom line is, please do not post the same question in multiple threads.