Import parent/child from txt file in MS Access - Programmers Heaven

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Import parent/child from txt file in MS Access

SSgtGlassSSgtGlass Posts: 3Member
I am creating an inventory database where I need to import data from a text file created by a barcode scanner. The software associated with the scanner saves scanner data as a text file that looks something like this:

1234561235
12335
12365
45645
1234567895
12398
45659
4567890123
12397

The 10 digit codes are the barcodes for the location where the equipment is. The following 5 digit codes are the barcodes of the equipment in the location scanned above.
I would like to set up an import parent child relationship so that the equipment is associated with the location. I have limited access/coding experience but I pick things up quickly.
Let me know if you guys have any ideas. I realize this could get complicated.


Much thanks,
SSgt Jonathan Glass
USAF



Comments

  • dokken2dokken2 Posts: 532Member
    : I am creating an inventory database where I need to import data from
    : a text file created by a barcode scanner. The software associated
    : with the scanner saves scanner data as a text file that looks
    : something like this:
    :
    : 1234561235
    : 12335
    : 12365
    : 45645
    : 1234567895
    : 12398
    : 45659
    : 4567890123
    : 12397
    :
    : The 10 digit codes are the barcodes for the location where the
    : equipment is. The following 5 digit codes are the barcodes of the
    : equipment in the location scanned above.
    : I would like to set up an import parent child relationship so that
    : the equipment is associated with the location. I have limited
    : access/coding experience but I pick things up quickly.
    : Let me know if you guys have any ideas. I realize this could get
    : complicated.
    :
    :
    : Much thanks,
    : SSgt Jonathan Glass
    : USAF
    :
    :
    :
    :
    I think is fairly simple. one way would be to-
    import the entire text file into a table
    run a query to append for the 10-digit parent records
    run another append query for the 5-digit child records

    try this code, put in a module, then Run Sub/Userform in visual basic-

    Option Compare Database

    'requires microsoft dao 3.51/3.6 object library [under tools/references]
    '
    'required tables-
    't0_import - with field1 as text
    't1_parent - with field1 as text
    't2_child - with field1 as text, field2 as text
    '
    Sub import()
    Dim db As DAO.Database, rs As DAO.Recordset
    Dim tmp As String
    Dim myParent As String
    Dim myChild As String

    'error handler
    On Error GoTo erh

    'open table/recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("t0_import", dbOpenSnapshot)
    '1st record
    rs.MoveFirst
    'turn off access messages [append records]
    DoCmd.SetWarnings False

    'loop thru all imported records
    Do While Not rs.EOF
    tmp = "" & rs!field1

    If Len(tmp) = 10 Then
    'append parent
    myParent = tmp

    SQL = "INSERT INTO t1_parent ( field1 )" & _
    " SELECT " & myParent & " AS expr1;"
    DoCmd.RunSQL SQL
    Else
    'append child
    myChild = tmp
    SQL = "INSERT INTO t2_child ( field1, field2 )" & _
    " SELECT " & myParent & " AS expr1, " & myChild & " AS expr2;"
    DoCmd.RunSQL SQL
    End If

    rs.MoveNext
    Loop

    xit:
    'turn on access messages
    DoCmd.SetWarnings True
    'release resources
    rs.Close
    db.Close
    Set rs = Nothing
    Set db = Nothing
    Exit Sub

    erh:
    MsgBox Err.Description, vbCritical, Err.Number
    Resume xit
    End Sub



    note- this only works if your barcode data is numeric, if its a string then need to concatenate double quotes around data to insert-

    tmp = chr(34) & rs!field1 & chr(34)
  • SSgtGlassSSgtGlass Posts: 3Member
    This does work fantastic however my barcode data is alpha-numeric and I am having trouble with the using the code you included with the note.

    Thanks for all of your work on this.

    SSgt Glass
  • dokken2dokken2 Posts: 532Member
    : This does work fantastic however my barcode data is alpha-numeric
    : and I am having trouble with the using the code you included with
    : the note.
    :
    : Thanks for all of your work on this.
    :
    : SSgt Glass
    :

    the key is that alpha data needs to be in quotes [single or double] in order to perform the Sql INSERT

    therefore, look at the [tmp...] line and replace with the one at bottom, should solve the problem

    'loop thru all imported records
    Do While Not rs.EOF
    tmp = "" & rs!field1 'numeric-only

    tmp = chr(34) & rs!field1 & chr(34) 'alpha-characters
  • SSgtGlassSSgtGlass Posts: 3Member
    AH!

    Thanks. A lot.
  • etkietki Posts: 5Member
    Jonathon,

    You should really do something a bit more slick, and more convenient.

    Why don't you try writing a simple socket server in Python (should be very easy), and use the PyWin32 extension library to make a connection to your Access database using the Python ADO library provided.

    This way you could have your barcode reader possibly communicating wirelessly to a server receiving your barcode data, and automatically entering it into Access in real-time, i.e. read streaming data instead of file data.

    You could also connect your barcode reader to the serial port, and read the serial port in Python + do the ADO stuff I mentioned.

    If you want to talk to me about this approach feel free, send me an email through here first. I support your kind of work.

    Kind regards, David


    : I am creating an inventory database where I need to import data from
    : a text file created by a barcode scanner. The software associated
    : with the scanner saves scanner data as a text file that looks
    : something like this:
    :
    : 1234561235
    : 12335
    : 12365
    : 45645
    : 1234567895
    : 12398
    : 45659
    : 4567890123
    : 12397
    :
    : The 10 digit codes are the barcodes for the location where the
    : equipment is. The following 5 digit codes are the barcodes of the
    : equipment in the location scanned above.
    : I would like to set up an import parent child relationship so that
    : the equipment is associated with the location. I have limited
    : access/coding experience but I pick things up quickly.
    : Let me know if you guys have any ideas. I realize this could get
    : complicated.
    :
    :
    : Much thanks,
    : SSgt Jonathan Glass
    : USAF
    :
    :
    :
    :

Sign In or Register to comment.