none
Update table from delimited text file RRS feed

  • Question

  • x

    I'm synching an Access data table from a very old legacy application only capable of exporting a delimited text file.  (in this case using "|" delimiter).   I know how to do this in VB6 with DAO or ADODB but want to migrate to VB.Net and ADO.Net.

    So my questions are these:

    1.  Am I taking the right approach?  I'm reading the dilimited text records, using the split command, then determining if the record exists in my table.

    2.  I'm getting errors loading the customersRow object.  I've looked around for the best approach to load fields when you don't know the length of the data in the text file field.

     

    I'm suspecting that there is a more clever way to do this.

     

    Thanks,  JC

     

     

     

     

            Me.CustomersTableAdapter.Fill(Me.Rebate32DataSet.Customers)
    
            Try
                ' Create an instance of StreamReader to read from a file.
                ' The using statement also closes the StreamReader.
    
                Using sr As New StreamReader(My.Settings.strCustomer)
                    Dim line As String
                    ' Read and display lines from the file until the end of
                    ' the file is reached.
                    Do
                        line = sr.ReadLine()
                        If Not (line Is Nothing) Then
    
                            Dim split As String() = line.Split(New [Char]() {"|"c})
    
                            ListBox1.Items.Add(split(0).ToString & " / " & split(1).ToString & " / " & split(2).ToString)
                            ListBox1.SelectedIndex = ListBox1.Items.Count - 1
    
                            Dim customersRow As Rebate32DataSet.CustomersRow
    
                            customersRow = Rebate32DataSet.Customers.FindBycustnum(Trim(split(0).ToString))              '          NorthwindDataSet.Customers.FindByCustomerID("ALFKI")
    
                            If customersRow Is Nothing Then
    
                                With customersRow
                                    .custnum = Trim(split(0).ToString)
                                    .custname = Trim(split(1).ToString)
                                    .custaddr1 = Trim(split(2).ToString)
                                    .custaddr2 = Trim(split(3).ToString)
                                    .custcity = Trim(split(4).ToString)
                                    .custst = Trim(split(5).ToString)
                                    .custzip = Trim(split(6).ToString)
                                    .custattn = Trim(split(7).ToString)
                                    .custslmn = Trim(split(8).ToString)
                                    .custphn = Trim(split(9).ToString)
                                    .newCust = -1
                                End With
                                Rebate32DataSet.Customers.Rows.Add(customersRow)
                            Else
                                With customersRow
                                    .custname = Trim(split(1).ToString).Substring(1, .Table.Columns.Item("custname").MaxLength)
                                    .custaddr1 = Trim(split(2).ToString)        '   .Substring(1, 19)       '   .Table.Columns.Item("custaddr1").MaxLength)
                                    .custaddr2 = Trim(split(3).ToString).Substring(1, .Table.Columns.Item("custaddr2").MaxLength)
                                    .custcity = Trim(split(4).ToString).Substring(1, .Table.Columns.Item("custcity").MaxLength)
                                    .custst = Trim(split(5).ToString).Substring(1, .Table.Columns.Item("custst").MaxLength)
                                    .custzip = Trim(split(6).ToString).Substring(1, .Table.Columns.Item("custzip").MaxLength)
                                    .custattn = Trim(split(7).ToString).Substring(1, .Table.Columns.Item("custattn").MaxLength)
                                    .custslmn = Trim(split(8).ToString).Substring(1, .Table.Columns.Item("custslmn").MaxLength)
                                    .custphn = Trim(split(9).ToString).Substring(1, .Table.Columns.Item("custphn").MaxLength)
    
                                End With
    
                            End If
    
    
                        End If
                    Loop Until line Is Nothing
    
                End Using
    
            Catch ex As Exception
    
                MsgBox(ex.Message.ToString)
    
            End Try
    


    John Collett
    Saturday, December 31, 2011 1:35 AM

Answers

  • Hi John,

    Change the code in your loop to this:

    Dim customersRow As Rebate32DataSet.CustomersRow
    
    customersRow = Rebate32DataSet.Customers.FindBycustnum(Trim(split(0).ToString))              '          NorthwindDataSet.Customers.FindByCustomerID("ALFKI")
    
    If customersRow Is Nothing Then
        customersRow = Rebate32DataSet.Customers.NewCustomersRow()
        Rebate32DataSet.Customers.AddCustomersRow(customersRow)
    End If
    
    With customersRow
        .custnum = Trim(split(0).ToString)
        .custname = Trim(split(1).ToString)
        .custaddr1 = Trim(split(2).ToString)
        .custaddr2 = Trim(split(3).ToString)
        .custcity = Trim(split(4).ToString)
        .custst = Trim(split(5).ToString)
        .custzip = Trim(split(6).ToString)
        .custattn = Trim(split(7).ToString)
        .custslmn = Trim(split(8).ToString)
        .custphn = Trim(split(9).ToString)
        .newCust = -1
    End With
    
    

    That should help get rid of your errors.


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    • Proposed as answer by Alan_chenModerator Monday, January 2, 2012 6:33 AM
    • Marked as answer by JohnC1102 Tuesday, January 3, 2012 5:19 PM
    Sunday, January 1, 2012 1:03 AM

All replies

  • Hi John,

    Change the code in your loop to this:

    Dim customersRow As Rebate32DataSet.CustomersRow
    
    customersRow = Rebate32DataSet.Customers.FindBycustnum(Trim(split(0).ToString))              '          NorthwindDataSet.Customers.FindByCustomerID("ALFKI")
    
    If customersRow Is Nothing Then
        customersRow = Rebate32DataSet.Customers.NewCustomersRow()
        Rebate32DataSet.Customers.AddCustomersRow(customersRow)
    End If
    
    With customersRow
        .custnum = Trim(split(0).ToString)
        .custname = Trim(split(1).ToString)
        .custaddr1 = Trim(split(2).ToString)
        .custaddr2 = Trim(split(3).ToString)
        .custcity = Trim(split(4).ToString)
        .custst = Trim(split(5).ToString)
        .custzip = Trim(split(6).ToString)
        .custattn = Trim(split(7).ToString)
        .custslmn = Trim(split(8).ToString)
        .custphn = Trim(split(9).ToString)
        .newCust = -1
    End With
    
    

    That should help get rid of your errors.


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    • Proposed as answer by Alan_chenModerator Monday, January 2, 2012 6:33 AM
    • Marked as answer by JohnC1102 Tuesday, January 3, 2012 5:19 PM
    Sunday, January 1, 2012 1:03 AM
  • Thanks Bonnie:

     

    Now my problem is that Access is throwing an error because of the customers table relationship with referential integrity on another table.  However, I'm not doing anything that violates the relationship.  I'm adding several records and maintaining non-key data fields on other customer records.

     

    I need to keep my eye on the goal of getting the data to SQL Server, so I may just do it the old way in VB6 for this week and keep the code you helped with for the SQL Server version.  If you have thoughts or insights, they would be appreciated.

     

    Thanks again and have a great 2012.

     

    John


    John Collett
    Tuesday, January 3, 2012 5:23 PM
  • Hi John,

    Don't thank me yet ... you still have problems. ;0)  ;0)

    What is the relationship that the customers table is involved in? What table are you adding records to, is it to the customers table or the related table?


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Tuesday, January 3, 2012 5:55 PM
  • Bonnie:

     

    :)  The thanks was getting me past the errors I was having processing delimited files. 

     

    Next error is: The record cannot be deleted or changed because table "Customer Group Table" includes related records.

    I'm adding and modifying records in the Customers Table.  There is a related table called "Customer Group Table"  (could something as silly as spaces in the table name be a problem?).

     

    Here is my update command:     Me.CustomersTableAdapter.Update(Rebate32DataSet)

     

    I've taken several scenarios at this problem.

    1.  I tried with one tableadapter update command after looping through all my delimited records (hundreds of inserts and updates in the ado.net data table)

    2.  When I got the error, I moved the update command to execute after each delimited record, got the same error on the first record.  The first record was an existing record in the Customers table with no related record in the related table. 

    3.  Then I moved the update command to only execute on new customer records.  There would never be an existing related record for a new customer but I still get the same error on the first update.

     

    I should add this.  I'm writing to an Access 2003 db but all the tables are attached to an old Access 95 database, which is where the relationships live.  I should also point out that I can make this work in VB6 and ADO/DAO blindfolded.  However, my goal is to learn how to do this stuff using .Net.  Teaching old dog new tricks is never easy.

     

    JC

     

     


    John Collett
    Tuesday, January 3, 2012 7:35 PM
  • Hi John,

    I'm adding and modifying records in the Customers Table.  There is a related table called "Customer Group Table"  (could something as silly as spaces in the table name be a problem?).

    Access is not my strong point ... in fact, I've never used it. Consequently, I have no idea if the spaces in the table name is a problem. I'm not sure if there's an Access specific forum.

    How is the Customer Group Table related to the Customers Table?


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Wednesday, January 4, 2012 6:04 AM