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
by: SanclerPosted on 2006-12-21 at 16:29:42ID: 18184500
So far as I know, you cannot use the argument in the Delimited attribute to indicate a pipe symbol. If you want to use the pipe symbol, you will need to use a schema.ini file. Here's a PAQ illustrating that
http://www.experts-exchang
And I don't know (I've never tried it) whether you could - even doing that - use the input from a read by the Text engine as direct output for the "normal" (Access) engine. My inclination would be to use the Text engine to read the data into a datatable and the the normal engine to read it out to Access.
I've put that in very general terms as your question suggests that you know what you're doing. But if you would like me to amplify any of it, please ask.
Roger