Remove SOH special character

[YasserKhalil]

Hello everyone
I am trying to import some data from csv file, and I noticed special characters which I copied to notepad++ and it showed me SOH special character. Then I searched how to remove this special character, and tried several topics but with no use
Any ideas?

This is my code (the weird point is that I can see the contents in locals window but couldn't print to activecell

Code: Select all

    Dim x, v, myPath As String, fn As String, txt As String, i As Long
    myPath = ThisWorkbook.Path & "\MyFiles\"
    fn = Dir(myPath & "*.csv")
    Do While fn <> ""
    With CreateObject("ADODB.Stream")
        .Charset = "UTF-8":  .Open:  .LoadFromFile myPath & fn:  v = Split(.ReadText, vbLf):  .Close
    End With
    
    ActiveCell.Value = v(9)
          Exit Do
        fn = Dir
    Loop

[HansV]

I don't see SOH when I open the attached file in Notepad++

[YasserKhalil]

Please have a look at the variables in local window.

[HansV]

I do see that the active cell is not being filled, but what does that have to do with SOH?
This is what I see in the Locals window:

S1403.png

[HansV]

The string contains lots of NUL characters. You might use

ActiveCell.Value = Replace(v(9), vbNullChar, "")

[YasserKhalil]

Thanks a lot, my tutor.
I am confused about the csv file, on my pc I got the values separated by semi-colon ; and when opened by excle I got only one column. While on another pc for a user, the comma is a comma not a semi-colon, and the csv file opened by excel in multiple columns. How can I solve such a problem on two different pcs in this case?

[HansV]

The file contains semi-colons, not commas:

S1405.png

On a PC that uses comma as decimal separator, semi-colon is most probably the list separator, so Excel should automatically split the data into multiple columns.
On a PC that uses point as decimal separator, comma is usually the list separator. This would cause Excel to leave the data in one column.

[YasserKhalil]

Thank you very much. Is there a common approach to reading the CSV file in the same way on both PCs?

[HansV]

Do you create the CSV file?

[YasserKhalil]

No, it is generated by a system on a pc's friend

[HansV]

Ask your friend if they can insert a line

Sep=;

at the beginning of the file. This will tell applications such as Excel to use semicolon as delimiter regardless of the system setting.

[YasserKhalil]

Is it possible to add this line in a temp file a way from the original file?

[HansV]

I don't know, sorry.

[p45cal]

Try missing out the .Charset line:

Code: Select all

Dim x, v, myPath As String, fn As String, txt As String, i As Long
myPath = ThisWorkbook.Path & "\MyFiles\"
fn = Dir(myPath & "*.csv")
Do While fn <> ""
  With CreateObject("ADODB.Stream")
    '.Charset = "UTF-8"
    .Open
    .LoadFromFile myPath & fn
    v = Split(.ReadText, vbLf)
    .Close
  End With
  ActiveCell.Value = v(9)
  Exit Do
  fn = Dir
Loop

[YasserKhalil]

Thank you very much. That solved the problem.

[SpeakEasy]

>That solved the problem

Just in case you were wondering, the reason this works is because
a) the default format for an ADODB stream handling text is UTF-16LE; and
b) Your CSV file is UTF-16LE

[YasserKhalil]

Thanks a lot for sharing. How can I know that the CSV is UTF-16LE?

[SpeakEasy]

Just check first two bytes of the file. If they are FF FE (in that order) then you have a UTF-16LE file

[HansV]

Open the CSV file in Notepad++ and look at the right-hand side of the status bar:

S1412.png

[YasserKhalil]

Thank you very much.