String.split method problems

dilbert0610

Member
Joined
Aug 3, 2006
Messages
22
Programming Experience
Beginner
I am currently using the string.split method to parse a delimited file. The files is using "?" to seperate the fields. The problem I am having is sometimes the field is blank (thus leaving data?data??data?data). When the field is blank it is totally ignoring it all together which is messing up how I am referencing the data.

I am only pulling out specific fields from the array by using the index number. If it skips a blank field then it is messing up my index numbers.

Does anyone know a way to force it to assign an index even though the field is blank? Or can anyone help me in figuring out how to solve this? I will psot my current code below.

VB.NET:
Me.Text = "Please Wait"
        'Start Database connection info and open database
        Dim DBFconn As String
        DBFconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=I:\DBASE\ORDER\AUDIT\;Extended Properties=dbase IV"
        Dim objConn As New System.Data.OleDb.OleDbConnection(DBFconn)
        objConn.Open()
        'end database info
        Dim Line As String
        Dim Counter As Integer = 0
        Dim buyer As String
        Dim Fields() As String
        Dim Delimiters() As Char = {"?"c}
        If File.Exists(FilePath) Then
            'Do The Work
            FileOpen(1, FilePath, OpenMode.Input)
            Do Until EOF(1)
                Line = LineInput(1)
                Fields = Line.Split(Delimiters)
                buyer = Fields(17).Replace("'", "")


                Dim cmdInsert As New OleDb.OleDbCommand("INSERT INTO ordaudit (projref, poref, buyer) VALUES ('" & Fields(13) & "', '" & Fields(14) & "', '" & buyer & "')", objConn)
                cmdInsert.ExecuteNonQuery()
            Loop
            FileClose(1)
            Me.Text = "Finished"
        Else
            MsgBox(FilePath & " Is not a valid path. Please check with Todd")
            Me.Text = "Error"
        End If
What I am doing is pulling out 3 specific fields from the text file and placing them into a database. Below is a few lines from the text file so you can see what they look like.

VB.NET:
Vendor Name?Project Ref?P.O Ref?Reason for Requistion?IssueDate?Buyer?Budget?Budget(US$)?Price?ExngRate?Price(US$)?Variance? 0?0639570-00-117?111406-2??03/23/07?THOMAS  KAUFFMAN?USD? 0? 0?***********?***********?***********?***********? 321794? 3275? 191?Vendor Total:?USD?USD?P.O Count:?Sup.Count:?F.L.SMIDTH INC. - MANHEIM PLAN?MANHEIM, PENNSYLVANIA?Grand Total :? 3275? 191?***********?***********? 321794?
Vendor Name?Project Ref?P.O Ref?Reason for Requistion?IssueDate?Buyer?Budget?Budget(US$)?Price?ExngRate?Price(US$)?Variance??0614385-00-116?111546-2??02/21/07?KATIE L DELONG?USD? 0? 0?***********?***********?***********?***********? 321794? 3275? 191?Vendor Total:?USD?USD?P.O Count:?Sup.Count:?F.L.SMIDTH INC. - MANHEIM PLAN?MANHEIM, PENNSYLVANIA?Grand Total :? 3275? 191?***********?***********? 321794?
Vendor Name?Project Ref?P.O Ref?Reason for Requistion?IssueDate?Buyer?Budget?Budget(US$)?Price?ExngRate?Price(US$)?Variance? 0?0620503-00-101?111836-2?Observation Ports?05/10/07?JOHN P O'MALIA?USD? 0? 0?***********?***********?***********?***********? 321794? 3275? 191?Vendor Total:?USD?USD?P.O Count:?Sup.Count:?F.L.SMIDTH INC. - MANHEIM PLAN?MANHEIM, PENNSYLVANIA?Grand Total :? 3275? 191?***********?***********? 321794?

as you can see some of the fields are blank just leaving a ?? in the delimited file.

Thanks in advance. I really hope I can get some help on this.
 
String.Split is not ignoring empty fields. "data??data" and "data?data?data" both return three array items.

Also use parameters for your query and you don't have to cripple the data like removing quote chars or struggle with escapes. Example:
VB.NET:
    Sub insert()
        Dim sr As New IO.StreamReader("quest.txt")
        Dim DBFconn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=I:\DBASE\ORDER\AUDIT\;Extended Properties=dbase IV"
        Dim objConn As New OleDb.OleDbConnection(DBFconn)
        objConn.Open()
        Dim cmdInsert As New OleDb.OleDbCommand("INSERT INTO ordaudit (projref, poref, buyer) VALUES (?,?,?)", objConn)
        cmdInsert.Parameters.Add("projref", OleDb.OleDbType.Char)
        cmdInsert.Parameters.Add("poref", OleDb.OleDbType.Char)
        cmdInsert.Parameters.Add("buyer", OleDb.OleDbType.Char)
        While sr.Peek <> -1
            Dim fields() As String = sr.ReadLine.Split("?")
            cmdInsert.Parameters.Item("projref").Value = fields(13)
            cmdInsert.Parameters.Item("poref").Value = fields(14)
            cmdInsert.Parameters.Item("buyer").Value = fields(17)
            cmdInsert.ExecuteNonQuery()
        End While
        sr.Close()
        objConn.Close()
    End Sub
 
Back
Top