ABM Support
asked on
Converting control characters in VB.Net
Hi
I have a text file which is the result of an export from an SQL table. At first glance the data in the file is good, however it seems littered with strange characters which are causing data to be ignored when I try and manipulate it within a VB.Net application.
I have attached a hex view of the data to demonstrate.....wherever there is an 20 surrounded by 09 (I believe the 09 is a tab), this is the cause of the issue.
How can I replace / convert the 20 with a null of even a single character space?
Thanks
Steve
Capture.JPG
I have a text file which is the result of an export from an SQL table. At first glance the data in the file is good, however it seems littered with strange characters which are causing data to be ignored when I try and manipulate it within a VB.Net application.
I have attached a hex view of the data to demonstrate.....wherever there is an 20 surrounded by 09 (I believe the 09 is a tab), this is the cause of the issue.
How can I replace / convert the 20 with a null of even a single character space?
Thanks
Steve
Capture.JPG
they are not strange characters, that is a tab delimited text file instead of a comma delimited. The 20 is a space which would denote most likely a blank field, just read in the string and do a split on the string using the tab character(09)
ASKER
you are indeed correct and it is a tab delimited text file. You are also correct in the way to manipulate the data as that is the method I have already used, however, the data is still missing after manipulation. The code for manipulation is as below:
Indata = sr.ReadLine
TestArray = Indata.Split(vbTab)
Varstring0 = Trim(TestArray(1))
VarString1 = Trim(TestArray(0))
VarString2 = Trim(TestArray(2))
VarString3 = Trim(TestArray(3))
VarString4 = Trim(TestArray(4))
VarString5 = Trim(TestArray(5))
VarString6 = Trim(TestArray(6))
VarString7 = Trim(TestArray(8))
VarString8 = Trim(TestArray(9))
VarString9 = Trim(TestArray(10))
VarString10 = Trim(TestArray(11))
VarString11 = Trim(TestArray(12))
Address = Trim(VarString2 & "," & VarString3 & "," & VarString4 & "," & VarString6 & "," & VarString5)
The address data, after manipulation, displays as 13-15 Finkle Street,
Kendal and the postcode, as per the hex data supplied do not display
This only happens where there are spaces like this in the data
Indata = sr.ReadLine
TestArray = Indata.Split(vbTab)
Varstring0 = Trim(TestArray(1))
VarString1 = Trim(TestArray(0))
VarString2 = Trim(TestArray(2))
VarString3 = Trim(TestArray(3))
VarString4 = Trim(TestArray(4))
VarString5 = Trim(TestArray(5))
VarString6 = Trim(TestArray(6))
VarString7 = Trim(TestArray(8))
VarString8 = Trim(TestArray(9))
VarString9 = Trim(TestArray(10))
VarString10 = Trim(TestArray(11))
VarString11 = Trim(TestArray(12))
Address = Trim(VarString2 & "," & VarString3 & "," & VarString4 & "," & VarString6 & "," & VarString5)
The address data, after manipulation, displays as 13-15 Finkle Street,
Kendal and the postcode, as per the hex data supplied do not display
This only happens where there are spaces like this in the data
Based upon the record you provided and your logic here is what it should be doing:
13-15 FINKLE ST REET,,KENDAL,,LA9 4AB
Is this what it is doing?
1=NEVKEN
0=NEVISPORT
2=13-15 FINKLE ST REET
3=B
4=KENDAL
5=LA9 4AB
6=B
B
7=B
8=0
9=B
10=60D
11=.00000000000000000000
Your display for Address should show:13-15 FINKLE ST REET,,KENDAL,,LA9 4AB
Is this what it is doing?
ASKER
Once again you are are correct, that is what it should be showing but alas not!
I put a display in and it only displays '13-15 Finkle Street,' however it does also display a length of 38 so not really sure what is going on here! As I said previously, if all the address fields contain actual data, the code works perfectly and to be fair, the code is not what what we would call complicated!
I put a display in and it only displays '13-15 Finkle Street,' however it does also display a length of 38 so not really sure what is going on here! As I said previously, if all the address fields contain actual data, the code works perfectly and to be fair, the code is not what what we would call complicated!
can you upload the raw data record in a file so I can take a look at it.
Normally if the data is cut off, then that means their is a null, if you look at the value of VarString3 in the debugger, is it by chance a null?
If this is the case after doing your split do the following:
Dim c as Integer
For c = 0 To TestArray .Length - 1
If TestArray (c) = vbNullString Then TestArray (c) = ""
TestArray(c)=Trim(TestArray(c))
Next
This will also trim all the elements so you don't need to do it when declaring above
ASKER
The varstrings display as follows:
2 - 13-15 Finkle Street
3 - Blank
4 - Kendal
5 - LA9 4AB
6 - Blank
It is only when concatenated into the address variable that varstrings 4 and 5 seem to disappear! I also inserted your code and that made no difference to the address string
2 - 13-15 Finkle Street
3 - Blank
4 - Kendal
5 - LA9 4AB
6 - Blank
It is only when concatenated into the address variable that varstrings 4 and 5 seem to disappear! I also inserted your code and that made no difference to the address string
Try this after my routine I gave you:
Address = TestArray(2) & "," & TestArray(3) & "," & TestArray(4) & "," & TestArray(6) & "," & TestArray(5)
ASKER
No change I'm afraid! I'm sure you agree, this shouldn't be that difficult!
I've attached a file with this record and another that works!
CustomerTest.txt
I've attached a file with this record and another that works!
CustomerTest.txt
I also need you to paste the actual sub routine so I am using exactly what you are.
ASKER
I've removed reference to what you have suggeted to date and set back to my original.
Dim TestArray As String()
Dim Indata As String
Dim Outdata As String = ""
Dim Address As String
Dim Varstring0 As String
Dim VarString1 As String
Dim VarString2 As String
Dim VarString3 As String
Dim VarString4 As String
Dim VarString5 As String
Dim VarString6 As String
Dim VarString7 As String
Dim VarString8 As String
Dim VarString9 As String
Dim VarString10 As String
Dim VarString11 As String
Dim Array_Count As Integer = 0
Dim c As Integer
cmbCustomerName.Items.Add( "")
Company_ID_Array(Array_Cou nt) = ""
Company_Name_Array(Array_C ount) = ""
Address_Line1_Array(Array_ Count) = ""
Address_Line2_Array(Array_ Count) = ""
Address_Line3_Array(Array_ Count) = ""
Address_Line4_Array(Array_ Count) = ""
Postcode_Array(Array_Count ) = ""
Telephone_Array(Array_Coun t) = ""
Status_Array(Array_Count) = ""
Array_Count += 1
Using sr As StreamReader = File.OpenText(frmMain.Cust omers_File )
Do While sr.Peek() >= 0
Indata = sr.ReadLine
TestArray = Indata.Split(vbTab)
Varstring0 = TestArray(1)
VarString1 = TestArray(0)
VarString2 = TestArray(2)
VarString3 = TestArray(3)
VarString4 = TestArray(4)
VarString5 = TestArray(5)
VarString6 = TestArray(6)
VarString7 = TestArray(8)
VarString8 = TestArray(9)
VarString9 = TestArray(10)
VarString10 = TestArray(11)
VarString11 = TestArray(12)
Address = Trim(VarString2 & "," & VarString3 & "," & VarString4 & "," & VarString6 & "," & VarString5)
If Len(Varstring0) < 35 Then
Outdata = Outdata & Varstring0 & Space(35 - Len(Varstring0))
Else
Outdata = Outdata & Mid(Varstring0, 1, 35)
End If
Outdata = Outdata & "|"
If Len(VarString1) < 10 Then
Outdata = Outdata & VarString1 & Space(10 - Len(VarString1))
Else
Outdata = Outdata & Mid(VarString1, 1, 10)
End If
Outdata = Outdata & "|"
If Len(Address) < 85 Then
Outdata = Outdata & Address & Space(85 - Len(Address))
Else
Outdata = Outdata & Mid(Address, 1, 85)
End If
Outdata = Outdata & "|"
Outdata = Outdata & VarString7
Outdata = Outdata & "|"
Outdata = Outdata & VarString8 & "|" & VarString9 & "|" & VarString10 & "|" & VarString11
cmbCustomerName.Items.Add( Outdata)
Company_ID_Array(Array_Cou nt) = Varstring0
Company_Name_Array(Array_C ount) = VarString1
Address_Line1_Array(Array_ Count) = VarString2
Address_Line2_Array(Array_ Count) = VarString3
Address_Line3_Array(Array_ Count) = VarString4
Address_Line4_Array(Array_ Count) = VarString6
Postcode_Array(Array_Count ) = VarString5
Telephone_Array(Array_Coun t) = VarString7
Status_Array(Array_Count) = VarString8
Client_Discount_Array(Arra y_Count) = VarString9
Client_Payment_Terms_Array (Array_Cou nt) = VarString10
Client_Credit_Limit_Array( Array_Coun t) = VarString11
Array_Count += 1
Outdata = ""
Loop
End Using
Dim TestArray As String()
Dim Indata As String
Dim Outdata As String = ""
Dim Address As String
Dim Varstring0 As String
Dim VarString1 As String
Dim VarString2 As String
Dim VarString3 As String
Dim VarString4 As String
Dim VarString5 As String
Dim VarString6 As String
Dim VarString7 As String
Dim VarString8 As String
Dim VarString9 As String
Dim VarString10 As String
Dim VarString11 As String
Dim Array_Count As Integer = 0
Dim c As Integer
cmbCustomerName.Items.Add(
Company_ID_Array(Array_Cou
Company_Name_Array(Array_C
Address_Line1_Array(Array_
Address_Line2_Array(Array_
Address_Line3_Array(Array_
Address_Line4_Array(Array_
Postcode_Array(Array_Count
Telephone_Array(Array_Coun
Status_Array(Array_Count) = ""
Array_Count += 1
Using sr As StreamReader = File.OpenText(frmMain.Cust
Do While sr.Peek() >= 0
Indata = sr.ReadLine
TestArray = Indata.Split(vbTab)
Varstring0 = TestArray(1)
VarString1 = TestArray(0)
VarString2 = TestArray(2)
VarString3 = TestArray(3)
VarString4 = TestArray(4)
VarString5 = TestArray(5)
VarString6 = TestArray(6)
VarString7 = TestArray(8)
VarString8 = TestArray(9)
VarString9 = TestArray(10)
VarString10 = TestArray(11)
VarString11 = TestArray(12)
Address = Trim(VarString2 & "," & VarString3 & "," & VarString4 & "," & VarString6 & "," & VarString5)
If Len(Varstring0) < 35 Then
Outdata = Outdata & Varstring0 & Space(35 - Len(Varstring0))
Else
Outdata = Outdata & Mid(Varstring0, 1, 35)
End If
Outdata = Outdata & "|"
If Len(VarString1) < 10 Then
Outdata = Outdata & VarString1 & Space(10 - Len(VarString1))
Else
Outdata = Outdata & Mid(VarString1, 1, 10)
End If
Outdata = Outdata & "|"
If Len(Address) < 85 Then
Outdata = Outdata & Address & Space(85 - Len(Address))
Else
Outdata = Outdata & Mid(Address, 1, 85)
End If
Outdata = Outdata & "|"
Outdata = Outdata & VarString7
Outdata = Outdata & "|"
Outdata = Outdata & VarString8 & "|" & VarString9 & "|" & VarString10 & "|" & VarString11
cmbCustomerName.Items.Add(
Company_ID_Array(Array_Cou
Company_Name_Array(Array_C
Address_Line1_Array(Array_
Address_Line2_Array(Array_
Address_Line3_Array(Array_
Address_Line4_Array(Array_
Postcode_Array(Array_Count
Telephone_Array(Array_Coun
Status_Array(Array_Count) = VarString8
Client_Discount_Array(Arra
Client_Payment_Terms_Array
Client_Credit_Limit_Array(
Array_Count += 1
Outdata = ""
Loop
End Using
Well, using this code and copying the text from the text file and making an ANSI file from it, I get the following for Address on the 2nd pass through is:
13-15 FINKLE STREET, ,KENDAL, ,LA9 4AB
No I am including the text file, so download it and try it. If it works then actually attach the file you are trying
13-15 FINKLE STREET, ,KENDAL, ,LA9 4AB
No I am including the text file, so download it and try it. If it works then actually attach the file you are trying
ASKER
That did actually work! The only difference betwen the files is the actual file I am using is exported from the SQL server and the one that I sent you was that same file edited using notepad and then saved as something else.
I'm not sure that I can upload the file as it is actual customer data....not sure where I stand with that.
I'm not sure that I can upload the file as it is actual customer data....not sure where I stand with that.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I think you are on the right lines here! I've done some research and it looks as though it may be due to a setting on the BCP program I'm using to extract the data from the SQL table. I'm using -c which apparently is a character extract but I've got a hunch this may not be all there is!!
ASKER
Thanks Randy, I believe that you have pointed me in the right direction and as such I have posted a new question, specific to the problem