Solved

Converting control characters in VB.Net

Posted on 2014-07-22
17
241 Views
Last Modified: 2014-07-25
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
0
Comment
Question by:abmcsltd
  • 9
  • 8
17 Comments
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40211456
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)
0
 

Author Comment

by:abmcsltd
ID: 40211565
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
0
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40211605
Based upon the record you provided and your logic here is what it should be 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

Open in new window

Your display for Address should show:
13-15 FINKLE ST REET,,KENDAL,,LA9 4AB
Is this what it is doing?
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:abmcsltd
ID: 40211636
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!
0
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40211667
can you upload the raw data record in a file so I can take a look at it.
0
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40211672
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?
0
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40211695
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

Open in new window

This will also trim all the elements so you don't need to do it when declaring above
0
 

Author Comment

by:abmcsltd
ID: 40211718
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
0
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40211780
Try this after my routine I gave you:
 Address = TestArray(2) & "," & TestArray(3) & "," & TestArray(4) & "," & TestArray(6) & "," & TestArray(5)

Open in new window

0
 

Author Comment

by:abmcsltd
ID: 40211843
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
0
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40211886
I also need you to paste the actual sub routine so I am using exactly what you are.
0
 

Author Comment

by:abmcsltd
ID: 40211925
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_Count) = ""
            Company_Name_Array(Array_Count) = ""
            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_Count) = ""
            Status_Array(Array_Count) = ""

            Array_Count += 1

            Using sr As StreamReader = File.OpenText(frmMain.Customers_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_Count) = Varstring0
                    Company_Name_Array(Array_Count) = 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_Count) = VarString7
                    Status_Array(Array_Count) = VarString8
                    Client_Discount_Array(Array_Count) = VarString9
                    Client_Payment_Terms_Array(Array_Count) = VarString10
                    Client_Credit_Limit_Array(Array_Count) = VarString11

                    Array_Count += 1

                    Outdata = ""
                Loop
            End Using
0
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40211986
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
0
 

Author Comment

by:abmcsltd
ID: 40212047
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.
0
 
LVL 21

Accepted Solution

by:
Randy Poole earned 500 total points
ID: 40212064
Understandable.  I am assuming that the file is in unicode or something of that sort and you would need to handle opening the file differently.  If you open your file in Hex Edit Mode, can you paste the 4 lines of the hex data
0
 

Author Comment

by:abmcsltd
ID: 40214125
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!!
0
 

Author Closing Comment

by:abmcsltd
ID: 40219415
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
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question