Import xls to multiple tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi!
I have a table called Instrument with the fields

InstrumentID - counter, primary key
InstType - ex. 8753E, PN 300
InstDenomination - ex. Network analyzer, Oscilloscope
InstNotes - ex. Option 1DE
ManufacturerID - foreign key, links to table Manufacturer
PriceGroup - foreign key, links to table PriceGroup, ex. H23

and a table called Manufacturer with details about the manufacturer and a
third table called PriceGroup with information about prices - ex. PriceGroup
H23 may equal to $110.

I now have a number of Excel sheets with 5 columns: Denomination,
Manufacturer, Type, Price and Notes. The last two may have null-values.

What I want is to import these sheets into the database, but the problem is
that the manufacturer column contains the name of the manufacturer, not the
ID. The same problem is in the price column (price instead of price group).

So do I handle this?
Any help much appreciated.
Thanks!
 
I think the way I would do this is to link to the spreadsheet rather than
import it. You could import it, but you would have to import it into an
existing table you define that has the same structure as the spreadsheet.

Then I would use VBA to open the tables, read each row of the spreadsheet,
position the tables to the records so you can get the correct values like ID,
and create the new rows in the table you want them in.
 
Back
Top