Load DB table from text file


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Load DB table from text file

  1. #1
    jill Guest

    Load DB table from text file


    I would like to able to load an Access '97 table from a text file using ADO
    but I am unsure as how to accoplish this. The file has about 10,000 records
    with three fields seperated by a single space (ex. 123456 ABCD 1250). I
    have opened the file for input in code and set each field equal to a variable.
    Here is the beginning of mycode:
    Do Until EOF(1)
    Line Input #1, DataLine
    Field1 = Trim$(Mid$(DataLine, 1, 6))
    Field2 = Trim$(Mid$(DataLine, 8, 4))
    Field3 = Trim$(Mid$(DataLine, 13, 4))
    .......

    Now I would like to Add a record to the table if the record does not already
    exist. If it does, then I want to edit the existing record with the new
    data. Could someone please point me in the right direction to accomplish
    this or to recommend a better way?

    TIA
    jill

  2. #2
    Craig Brown Guest

    Re: Load DB table from text file


    Jill,

    I cut bits and pieces of an insert into an Access table process and pasted
    it below.

    Please, note, not all the variable definitions are in here.

    There are quite a few comments in the code itself, so, hopefully, it's easy
    to understand and adapt to your needs.

    Let us know if you need more help.

    Craig Brown

    '- - - - - - - - - - - - - -

    Option Explicit

    '- - - - - - - - - - - - - -
    'Private module variables
    '- - - - - - - - - - - - - -

    '- - - - - - - - - - - - - -
    Private cnn As ADODB.Connection


    '- - - - - - - - - - - - - -
    'Database To use
    'Read/Write
    '- - - - - - - - - - - - - -
    Public Property Get DataBaseName() As String

    DataBaseName = mDataBaseName

    End Property
    '- - - - - - - - - - - - - -
    Public Property Let DataBaseName(ByVal vDataBase As String)

    'Make sure the databse exists...
    If Len(Dir(vDataBase)) > 0 Then
    mDataBaseName = vDataBase

    'Set the database type
    cnn.Provider = "Microsoft.Jet.OLEDB.3.51"

    'Open the database file
    cnn.Open "Data Source=" & mDataBaseName
    Else
    End If

    End Property


    '- - - - - - - - - - - - - -
    'Class initialize method
    '- - - - - - - - - - - - - -
    Private Sub Class_Initialize()

    'Initialize variables
    InitStuff

    'Create the ADO database connection
    Set cnn = New ADODB.Connection

    End Sub

    '- - - - - - - - - - - - - -
    'Class terminate method
    'Release all the resources we're holding down....
    '- - - - - - - - - - - - - -
    Private Sub Class_Terminate()

    cnn.Close
    Set cnn = Nothing

    End Sub


    '- - - - - - - - - - - - - -
    'Insert the formatted fields as a row
    '- - - - - - - - - - - - - -
    Private Sub InsertRec()

    'Build the Insert statement
    sqlInsert = "INSERT INTO " _
    & TableName _
    & " " _
    & " VALUES (" & cDblQs & Field1 & cDblQs & _
    & "," & cDblQs & Field2 & cDblQs _
    & "," & cDblQs & Field3 & cDblQs

    sqlInsert = sqlInsert & ")"

    'Ok, now insert the row...
    cnn.Execute sqlInsert

    ' Jill, check for "errors", i.e duplicates, here
    End Sub


    "jill" <jmall@bostwick.com> wrote:
    >
    >I would like to able to load an Access '97 table from a text file using

    ADO
    >but I am unsure as how to accoplish this. The file has about 10,000 records
    >with three fields seperated by a single space (ex. 123456 ABCD 1250). I
    >have opened the file for input in code and set each field equal to a variable.
    > Here is the beginning of mycode:
    > Do Until EOF(1)
    > Line Input #1, DataLine
    > Field1 = Trim$(Mid$(DataLine, 1, 6))
    > Field2 = Trim$(Mid$(DataLine, 8, 4))
    > Field3 = Trim$(Mid$(DataLine, 13, 4))
    > .......
    >
    >Now I would like to Add a record to the table if the record does not already
    >exist. If it does, then I want to edit the existing record with the new
    >data. Could someone please point me in the right direction to accomplish
    >this or to recommend a better way?
    >
    >TIA
    >jill



  3. #3
    Paul Clement Guest

    Re: Load DB table from text file

    On 25 Jan 2001 10:19:25 -0800, "jill" <jmall@bostwick.com> wrote:


    I would like to able to load an Access '97 table from a text file using ADO
    but I am unsure as how to accoplish this. The file has about 10,000 records
    with three fields seperated by a single space (ex. 123456 ABCD 1250). I
    have opened the file for input in code and set each field equal to a variable.
    Here is the beginning of mycode:
    Do Until EOF(1)
    Line Input #1, DataLine
    Field1 = Trim$(Mid$(DataLine, 1, 6))
    Field2 = Trim$(Mid$(DataLine, 8, 4))
    Field3 = Trim$(Mid$(DataLine, 13, 4))
    .......

    Now I would like to Add a record to the table if the record does not already
    exist. If it does, then I want to edit the existing record with the new
    data. Could someone please point me in the right direction to accomplish
    this or to recommend a better way?

    Here is another method you can use that implements a schema.ini file which allows you define the
    structure of the input file and Access table to be created.

    Sub ImportTextToAccessADO()

    Dim cnn As New ADODB.Connection
    Dim sqlString As String

    cnn.Open _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=c:\My Documents\DB1.mdb;" & _
    "Jet OLEDB:Engine Type=4;"

    sqlString = "SELECT * INTO [tblPeople] FROM [Text;DATABASE=C:\My Documents\TextFiles].[People.txt]"

    cnn.Execute sqlString

    Set cnn = Nothing

    End Sub

    schema.ini file looks like this:

    [People.txt]
    ColNameHeader=False
    Format=FixedLength
    CharacterSet=ANSI
    Col1=ID long width 11
    Col2=LastName text width 30
    Col3=FirstName text width 20


    Paul ~~~ pclement@ameritech.net
    Microsoft MVP (Visual Basic)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center