[thelist] populating a 3 NF relational database with spreadsheet data

Ken Schaefer ken at adOpenStatic.com
Wed Dec 3 18:20:08 CST 2003


What technologies you do have at hand? You don't even tell us what platform
you're using, or what database software you have...

It seems like some looping logic will get your through this. If it's an
Excel spreadsheet you can query it using the Jet.OLEDB.4.0 OLEDB Provider.
You can read in each row as a single record.

Then, you'd insert:
a) each field as a new record into your database,
b) along with the value of your first counter (this counter variable would
increment for each *row* in the spreadsheet),
c) along with the value of your second counter (this would increment for
each *field* in the current record, and reset itself to 1 when you start
inserting the next record)

Cheers
Ken

Microsoft MVP - Windows Server (IIS)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Smith, Jason, (C)" <CSmith3 at dow.com>
Subject: [thelist] populating a 3 NF relational database with spreadsheet
data


: I have developed a relational database from a spreadsheet and
: now need to get the spreadsheet data into the tables.  I
: checked the archives of this list and found info that helped
: me to get most of the data into the right tables.
...
:
: Thanks for any help..........
: ID Field1 Field2 Field3 Field4 Field5 Field6
: 1 Magnolia Cold Bed Jenny Brown Grave
: 2 Meat Not Wrong Web Caledonia Joe Teller
:
: (note: the data is tab delimited)
:
: [here is the table]
:
: ID Song Position
: 1 Magnolia 1
: 1 Cold Bed 2
: 1 Jenny Brown 3
: 1 Grave 4
: 2 Meat 1
: 2 Not Wrong 2
: 2 Web 3
: 2 Caledonia 4
: 2 Joe 5
: 2 Teller 6
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



More information about the thelist mailing list