Import parent/child from txt file

[SSgtGlass]

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

[Rabbit]

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

Well, you're going to have to write a procedure that opens the text file, reads and stores the first number, which should always be ten digits, then it reads the next line and together those two get put into a table that has two fields, one a location field and the other the equipment field. Then it continues doing this until it finds another record with a 10 digit number, at this point, the new number replaces the old one and continues along until the end of the file is reached.

[nico5038]

You could create a function like:

Function fncGetImportData()
'This function imports a file from disk like:  "D:\Data\myfile.txt"
'Into two tables
 
Dim rsL As Recordset
Dim rsI As Recordset
Dim strLine As String
Dim strLocation As String
 
Set rsL = currentdb.OpenRecordset("tblLocation")
Set rsI = currentdb.OpenRecordset("tblItem")
 
Open "D:\Data\myfile.txt" For Input As #1
 
While Not EOF(1)
   Line Input #1, strLine
   ' This test is used to skip empty lines..
   If Len(strLine) > 0 Then
     ' process 10 and within that the 5 long records
      If Len(strLine) >= 10 Then
         ' add location record
         rsL.AddNew
         ' save loaction for details
         strLocation = Trim(strLine)
         rsL!Location = Trim(strLine)
         rsL.Update
      else ' not 10 so we assume 5 long...
         ' add Item record
         rsI.AddNew
         ' location
         rsI!Location = strLocation
         rsI!Item = Trim(strLine)
         rsI.Update
      end if
   End If
Wend
Close #1
 
Set rsL = Nothing
Set rsI = Nothing
 
'As all is "invisible" just a reminder when file has been processed...
MsgBox "Table import ready"
 
End Function
 

Just copy paste the code into a module and change the filename.

Nic;o)