Remove SOH special character

YasserKhalil
PlatinumLounger
Posts: 4936
Joined: 31 Aug 2016, 09:02

Remove SOH special character

Post by 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
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78666
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Remove SOH special character

Post by HansV »

I don't see SOH when I open the attached file in Notepad++
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4936
Joined: 31 Aug 2016, 09:02

Re: Remove SOH special character

Post by YasserKhalil »

Please have a look at the variables in local window.

User avatar
HansV
Administrator
Posts: 78666
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Remove SOH special character

Post by 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
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 78666
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Remove SOH special character

Post by HansV »

The string contains lots of NUL characters. You might use

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

YasserKhalil
PlatinumLounger
Posts: 4936
Joined: 31 Aug 2016, 09:02

Re: Remove SOH special character

Post by 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?

User avatar
HansV
Administrator
Posts: 78666
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Remove SOH special character

Post by 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.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4936
Joined: 31 Aug 2016, 09:02

Re: Remove SOH special character

Post by YasserKhalil »

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

User avatar
HansV
Administrator
Posts: 78666
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Remove SOH special character

Post by HansV »

Do you create the CSV file?
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4936
Joined: 31 Aug 2016, 09:02

Re: Remove SOH special character

Post by YasserKhalil »

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

User avatar
HansV
Administrator
Posts: 78666
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Remove SOH special character

Post by 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.
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4936
Joined: 31 Aug 2016, 09:02

Re: Remove SOH special character

Post by YasserKhalil »

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

User avatar
HansV
Administrator
Posts: 78666
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Remove SOH special character

Post by HansV »

I don't know, sorry.
Best wishes,
Hans

User avatar
p45cal
2StarLounger
Posts: 152
Joined: 11 Jun 2012, 20:37

Re: Remove SOH special character

Post by 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
PlatinumLounger
Posts: 4936
Joined: 31 Aug 2016, 09:02

Re: Remove SOH special character

Post by YasserKhalil »

Thank you very much. That solved the problem.

User avatar
SpeakEasy
4StarLounger
Posts: 575
Joined: 27 Jun 2021, 10:46

Re: Remove SOH special character

Post by 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
PlatinumLounger
Posts: 4936
Joined: 31 Aug 2016, 09:02

Re: Remove SOH special character

Post by YasserKhalil »

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

User avatar
SpeakEasy
4StarLounger
Posts: 575
Joined: 27 Jun 2021, 10:46

Re: Remove SOH special character

Post by SpeakEasy »

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

User avatar
HansV
Administrator
Posts: 78666
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Remove SOH special character

Post by HansV »

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

S1412.png
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4936
Joined: 31 Aug 2016, 09:02

Re: Remove SOH special character

Post by YasserKhalil »

Thank you very much.