Import | Delimited text file into Access   Database

vb.net 2003
Office 2003

What I have:
C:\TestData\Input.txt  
Text File Pipe Delimited    :  4 columns Of data
example:
00001|NO BRAND NAME ASSIGNED|6DU27|M3-.5 X 6 FLAT HD SOCKET CAP SCREW, CL10.9, ALLOY STEEL
00002|NO BRAND NAME ASSIGNED|6DU28|M3-.5 X 8 FLAT HD SOCKET CAP SCREW, CL10.9, ALLOY STEEL
00003|NO BRAND NAME ASSIGNED|6DU29|M3-.5 X 10 FLAT HD SOCKET CAP SCREW, CL10.9, ALLOY STEEL
00004|NO BRAND NAME ASSIGNED|6DU30|M3-.5 X 12 FLAT HD SOCKET CAP SCREW, CL10.9, ALLOY STEEL
00005|NO BRAND NAME ASSIGNED|6DU31|M3-.5 X 14 FLAT HD SOCKET CAP SCREW, CL10.9, ALLOY STEEL
00006|NO BRAND NAME ASSIGNED|6DU32|M3-.5 X 16 FLAT HD SOCKET CAP SCREW, CL10.9, ALLOY STEEL
00007|NO BRAND NAME ASSIGNED|6DU33|M3-.5 X 18 FLAT HD SOCKET CAP SCREW, CL10.9, ALLOY STEEL
00008|NO BRAND NAME ASSIGNED|6DU34|M3-.5 X 20 FLAT HD SOCKET CAP SCREW, CL10.9, ALLOY STEEL
00009|NO BRAND NAME ASSIGNED|6FB98|M3-.5 X 20 SOCKET SET SCREW, CUP PT., DIN 916, BLACK
00010|NO BRAND NAME ASSIGNED|1CB70|5/8 X 2" STANDARD SOCKET SHOULDER SCREW, 1/2-13 THRD, ALLOY

Access Database:
C:\TestData\InputData.mdb
1 Table
ImpData:   3 Fields
All text:
ImportID -Text   15  
Mfgname - Text 100
Gnum- Text 25
Desc- Text- 255

What I need:
I need a procedure that will import the "Input.txt"  into the Access Table

'' current code

Function ImportTextToAccess() As Boolean

        Dim AccessConn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\dm2007\ImpData.mdb")
        AccessConn.Open()
        'New table
' This works  but the the delimiter  is not working correctly  !
        Dim AccessCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO [InpTable2] FROM [Text;DATABASE=C:\DM2007;HDR=no;FMT=Delimited(|)].[INPUT.txt]", AccessConn)




' This does not work at All.... ?
 'Existing table
        '   Dim AccessCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO [InpTable] FROM [Text;Database=c:\dm2007;Hdr=No;Fmt=Delimited(|)].[].[Input.txt]", AccessConn)

        AccessCommand.ExecuteNonQuery()
        AccessConn.Close()

    End Function

Thanks
fordraiders





Start Free Trial