Rilith Alar
Rilith Alar

Reputation: 13

Why am I getting characters changing in my replace to �?

I have hundreds of text files with thousands of records in each. See sample record below.

3”100”56”2016-10-26 03:00:52.107”2016-10-26 07:00:52.000”1509”1”1509”17”1”-21749”-23437”0”2”0”0”0”0”0”0”1”1”80.844208”2”1”0.51136178”3”1”0.4092806”4”1”638”5”1”0”6”1”0”7”1”7”8”1”3”9”1”914”10”1”984”11”1”1094”12”1”817”13”1”1246”14”1”583”15”1”89”16”1”233”17”1”771”18”1”405”19”1”245”20”1”528”21”1”325”0”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL”NULL

These were an export done from SQL that I did not set up.

I am changing the NULL text to "" so that it is truly NULL. Once data has been corrected I am running a bulk insert.

Here is a copy of my Find/Replace code.

Dim fileReader As String = My.Computer.FileSystem.ReadAllText(LBRQFileName).Replace("NULL", "")
    My.Computer.FileSystem.WriteAllText(LBRQFileName, fileReader, False)

This code works great if the delimiter is ',' or ';' or anything normal. However, a lot of these files have a character (U+201D : RIGHT DOUBLE QUOTATION MARK {double comma quotation mark}) as the delimiter. In this situation, when I run the find and replace for NULL I end up with something like this (Small example):

1�245�20�1�528�21�1�325�0�������������� 

(several in a row where "Null" was the value)

I have tried running a find and replace on that double quote, only to have same results. I have tried coming up with ways of setting up the delimiter as the RIGHT DOUBLE QUOTATION MARK(Works in SSMS, but not from VB).

Running the replace for both the RIGHT DOUBLE QUOTATION MARK and "NULL" from notepad......works fine.....

But I have hundreds of files to do. I don't have time to open each one up in notepad.

Update:

Changed code to Read and Write in ASCII. After I ran the replaced code, I was left with a usable delimiter.

Dim fileReader As String = My.Computer.FileSystem.ReadAllText(LBRQFileName, Encoding.ASCII).Replace("NULL", "")
    My.Computer.FileSystem.WriteAllText(LBRQFileName, fileReader, False, System.Text.Encoding.ASCII)

Upvotes: 1

Views: 144

Answers (2)

Hadi
Hadi

Reputation: 37313

you can achieve the same thing Using System.IO Library

    Using str As New IO.StreamReader("C:\1.txt", System.Text.Encoding.Unicode, True)

        strText = str.ReadToEnd.Replace("NULL", "")
        str.Close()

    End Using

    Using stw As New IO.StreamWriter("C:\1.txt", False, System.Text.Encoding.Unicode)

        stw.Write(strText)
        stw.Close()

    End Using

It works fine for me

Upvotes: 0

Trevor_G
Trevor_G

Reputation: 1331

Probably because those are right quotes quotes... you need to change the text file encoding

 Dim fileReader As String = My.Computer.FileSystem.ReadAllText(LBRQFileName, System.Text.Encoding.UTF8).Replace("NULL", "")

 My.Computer.FileSystem.WriteAllText(LBRQFileName, fileReader, False, System.Text.Encoding.UTF8)

Default vb.net text files do not support that character.

Upvotes: 1

Related Questions