schema.ini Pipe Delimited to Access Database

22 Dec 2006 2:09 AM

Fordraiders

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 sometimes works but the the delimiter is not working correctly
at all
        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

fordraiders

--
Sent via .NET Newsgroups
http://www.dotnetnewsgroups.com

22 Dec 2006 5:16 PM

Paul Clement

On Thu, 21 Dec 2006 18:09:21 -0800, Fordraiders <sta***@insightbb.com> wrote:

¤ 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 sometimes works but the the delimiter is not working correctly
¤ at all
¤         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
¤

Any delimiter other than a comma requires a schema.ini file. Below is an example of what the
contents would look like:

[Input.txt]
ColNameHeader=False
CharacterSet=ANSI
Format=Delimited(|)

In addition, if the destination table already exists you need to use INSERT INTO...SELECT...FROM:

INSERT INTO [InpTable2] (ImportID, Mfgname, Gnum, Desc) SELECT F1, F2, F3, F4 FROM
[Text;DATABASE=C:\DM2007;HDR=No].[Input.txt]

Paul
~~~~
Microsoft MVP (Visual Basic)