Updating database table from | delimeted text file

[supersal666]

Hi everyone, I have an access table and I need to be able to update the table with data from a | delimeted text file.

The textfile is like below:

Card Number|Title|Forename|Surname|Address Line 1|Address Line 2|Address Line 3|Address Line 4|Address Line 5|PostCode|NOB|BarCode
123456|Mr|Mo|EYEOYIBO|43 Some Road|||Any Town|Kent|AB1 2CD|Plumber|0000200063571
654321|Mr|Terrence|Denman|29 Silly Avenue|||BlackTown|East Sussex|T22 7BG|Tiling / Kitchen & Bathroom Installation|0000200063623

At the moment I have some vb.net code which puts the data into a table (creates a new table) I actually need it to update the table (rather than create it) and seperate the values by the | character and put them into
the different fields.

Module Module1

    Sub Main()

        'Function ImportTextToAccess()

        Dim myConn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=mydatabase.mdb;")

        myConn.Open()

        Dim myCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO [Table3] FROM[Text;DATABASE=Text;DATABASE=C:\].[textfile.txt]", myConn)

        myCommand.ExecuteNonQuery()
        myConn.Close()

    End Sub

End Module

Has anyone got any sample code of how to add the data from the textfile using | as delimeters to put each value into the table into different fields?

Thanks
Sally

 

[supersal666]

Ok have worked this out now

first I replaced the | with a , and dropped the table:

Module Module1

    Sub Main()

        Dim fso, inputFile, outputFile
        Dim str As String

        fso = CreateObject("Scripting.FileSystemObject")

        '1 means for reading

        inputFile = fso.OpenTextFile("mytextfile.txt", 1)

        str = inputFile.ReadAll

        'modify this string, replace required characters

        str = Replace(str, "|", ",")

        'write back

        outputFile = fso.CreateTextFile("C:\mytextfile.txt", True)

        outputFile.Write(str)

        Dim myConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\mydatabase.mdb;")

        myConn.Open()

        Dim myCommand As New OleDbCommand("DROP TABLE Table1", myConn)

        myCommand.ExecuteNonQuery()
        myConn.Close()

    End Sub
  
End Module

and then I created the table and inserted the data:

Imports System.Data.OleDb

Module Module1

    Sub Main()

        Dim myConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\mydatabase.mdb;")

        myConn.Open()

        Dim myCommand As New OleDbCommand("SELECT * INTO [Table1] FROM [Text;DATABASE=C:\;HDR=no;FMT=Delimited(,)].[mytextfile.txt]", myConn)

        myCommand.ExecuteNonQuery()
        myConn.Close()

    End Sub

End Module

 

[cjard]

yeah. um. the purpose of code tags is manifold:

preserve indentation
show in monospace
give a nice blue coloured box so we can tell what is code and what is your post words

http://www.vbdotnetforums.com/misc.php?do=bbcode

 

[supersal666]

i agree

errm yes it is a bit hard to read, I will make my posts more readable in the future!