By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,084 Members | 1,969 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,084 IT Pros & Developers. It's quick & easy.

Import parent/child from txt file

P: 2
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
May 14 '07 #1
Share this Question
Share on Google+
2 Replies


Rabbit
Expert Mod 10K+
P: 12,359
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.
May 17 '07 #2

nico5038
Expert 2.5K+
P: 3,072
You could create a function like:

Expand|Select|Wrap|Line Numbers
  1. Function fncGetImportData()
  2. 'This function imports a file from disk like:  "D:\Data\myfile.txt"
  3. 'Into two tables
  4.  
  5. Dim rsL As Recordset
  6. Dim rsI As Recordset
  7. Dim strLine As String
  8. Dim strLocation As String
  9.  
  10. Set rsL = currentdb.OpenRecordset("tblLocation")
  11. Set rsI = currentdb.OpenRecordset("tblItem")
  12.  
  13. Open "D:\Data\myfile.txt" For Input As #1
  14.  
  15. While Not EOF(1)
  16.    Line Input #1, strLine
  17.    ' This test is used to skip empty lines..
  18.    If Len(strLine) > 0 Then
  19.      ' process 10 and within that the 5 long records
  20.       If Len(strLine) >= 10 Then
  21.          ' add location record
  22.          rsL.AddNew
  23.          ' save loaction for details
  24.          strLocation = Trim(strLine)
  25.          rsL!Location = Trim(strLine)
  26.          rsL.Update
  27.       else ' not 10 so we assume 5 long...
  28.          ' add Item record
  29.          rsI.AddNew
  30.          ' location
  31.          rsI!Location = strLocation
  32.          rsI!Item = Trim(strLine)
  33.          rsI.Update
  34.       end if
  35.    End If
  36. Wend
  37. Close #1
  38.  
  39. Set rsL = Nothing
  40. Set rsI = Nothing
  41.  
  42. 'As all is "invisible" just a reminder when file has been processed...
  43. MsgBox "Table import ready"
  44.  
  45. End Function
  46.  
Just copy paste the code into a module and change the filename.

Nic;o)
May 19 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.