Reputation: 13
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
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
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