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"","""","""","""","""""

 

[Joe4]

You seem to have a lot of unnecessary double-quotes (i.e. 4 in a row for blank entries). Seems like overkill to me. Typically, you only need double-quotes around text entries with literal commas in them when using CSV files.

If the issue is that Excel is converting some data when opening the CSV file in Excel, the trick is to import the data and set EVERY incoming field as Text. That will keep all the values "as-is" without any conversions. To do this, you must get the Import Wizard to invoke. You can do this when you import the file into Excel in the following manner:
- Go into Excel and open a blank file
- Go to the Data menu, and from the "Get External Data" ribbon, select "From Text"
- Browse to your file, and open it, going through the steps of the Import Wizard

If you use the Macro Recorder while you perform these steps, you will get the necessary VBA code.

 

[Joe4]

If you do need to keep all the double-quotes, here is a workaround that may help:
Open the file like I described in the post above, but instead of choosing a comma delimited file, choose some other delimiter that will never be found in the data, like a Tab or pipe symbol.
This will import everything into column A. Then, if you are simply trying to remove those Chinese characters, you can do Find/Replace on them.
Then re-save the file as Space Delimited Text file.

Once again, using the Macro Recorder on these steps should get you most of the VBA code that you need.

 

[thedogg]

I have the double-quotes in csv file. I do not need them in xlsx file but again they must be inside csv. The code I need is to just generate the same csv again.

The idea is to have excel template to generate csv exactly like this what I have attached.

 

[Joe4]

I have the double-quotes in csv file. I do not need them in xlsx file but again they must be inside csv. The code I need is to just generate the same csv again.

The issue is that opening the file in Excel and splitting into multiple columns will drop them.
Re-saving as a CSV file will not put them back in. Excel only uses them where necessary, and that will be a single set around an entry with a literal comma only. Excel certainly won't put in doubles automatically.

The question is are they REALLY required (especially doubled-up like that)?
Most programs that I have worked with that import CSV files do not need them around entries without commas, and certainly don't need them to be doubled-up.
Many times I see CSV data files like this, not because the program you are importing to requires it, but rather because of limitations of the program exporting the the CSV file initially (or the person who set it up didn't quite know what they were doing).

If you need them doubled-up, you have two option, as far as I can see:
1. You can use the technique I described in my previous post, to pull all the data into the first Excel column "as-is"
2. You can create VBA code that manually exports each row to a CSV file, and puts double-quotes around every field.

 

[thedogg]

If I remove "" I cannot upload it anymore.


Sorry, I can but I removed them in notepad. So how to generate the file like that with chinese characters from XLSX? This is my question now

 

[Joe4]

You would need VBA code, something like this: http://www.excel-easy.com/vba/examples/write-data-to-text-file.html

I am messing around with it, but am having trouble with the doubling-up on double-quotes. It seems to want to do 1 set, or 3, but I cannot get it to do 2.
Do you always have a set number of columns, or does that change?

 

[thedogg]

I will have always the same amount of columns I will save later to csv but I want to have more columns in xlsx file, as I mentioned, it works without "".

 

[Joe4]

I am sorry, but from your last two statements, I am a little unclear as to what the current requirements are.

Sorry, I can but I removed them in notepad. So how to generate the file like that with chinese characters from XLSX? This is my question now

I will have always the same amount of columns I will save later to csv but I want to have more columns in xlsx file, as I mentioned, it works without "".

Can you try re-stating your new requirements in detail?

 

[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"]#1[/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"]#1[/URL] , StrConv(Text, vbUnicode)
        Next R
      Close [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1"]#1[/URL] 
      
End Sub