Importing text data into a relational database - how?

  • Thread starter Thread starter Rmarkham
  • Start date Start date
R

Rmarkham

I am having some difficulty figuring out how to import a flat text
data file imported from a barcode scanner into a relational database.
The text data is essentially a flat database with a list of records
with the following attributes:

Item_Lot_Number, Origin, Destination, Piece Number, Scan Date


The relational database setup I am trying to import it into has
Item_Lot_Number, Origin and Destination in one table, Piece Number in
another table, and a third table for Scan Date and Scan type (we scan
on item arrival, item departure, and periodic warehouse inventory
scans). Each Item_Lot_Number can have multiple Piece Numbers
associated with it, and each piece number can have multiple scan
dates.

So essentially, the text outpot from the barcode scanner comes out
like:

Lot123,JFK,LAX,1,10/08/03
Lot123,JFK,LAX,2,10/08/03
....
Lot123,JFK,LAX.101,10/08/03
Lot124,JFK,MIA,1,10/08/03

and so on.

I'd like to have an import function that will take a scan of multiple
items from multiple lot numbers, and properly distribute the data into
the related tables, such that there would be one Lot123 record in the
Item_Lot_Number table with all 101 individual pieces related to it. I
can't seem to figure out exactly how to do so though. Any assistance
would be immensely appreciated.
 
I am having some difficulty figuring out how to import a flat text
data file imported from a barcode scanner into a relational database.
The text data is essentially a flat database with a list of records
with the following attributes:

Item_Lot_Number, Origin, Destination, Piece Number, Scan Date


The relational database setup I am trying to import it into has
Item_Lot_Number, Origin and Destination in one table, Piece Number in
another table, and a third table for Scan Date and Scan type (we scan
on item arrival, item departure, and periodic warehouse inventory
scans). Each Item_Lot_Number can have multiple Piece Numbers
associated with it, and each piece number can have multiple scan
dates.

So essentially, the text outpot from the barcode scanner comes out
like:

Lot123,JFK,LAX,1,10/08/03
Lot123,JFK,LAX,2,10/08/03
...
Lot123,JFK,LAX.101,10/08/03
Lot124,JFK,MIA,1,10/08/03

and so on.

I'd like to have an import function that will take a scan of multiple
items from multiple lot numbers, and properly distribute the data into
the related tables, such that there would be one Lot123 record in the
Item_Lot_Number table with all 101 individual pieces related to it. I
can't seem to figure out exactly how to do so though. Any assistance
would be immensely appreciated.
Click to expand...


It's relatively trivial, though tedious, to do this in code with
low-level file functions, read a line, parse it out, put the fields
into the proper tables and repeat until EOF on input. That would be a
one-step method. The only other way to do this involves an import
into a temp table(or maybe not so temporary if it's going to be done
repeatedly) and then run update, append and, maybe, delete queries to
update your various tables. Not going to be one step that way but
easier if you're not into coding.

Stan
 
Back
Top