Best structure or format for importing an excel database

[tyhand]

hi all,

Happy Holidays!

What is the best structure or format for importing an Excel database to a
Full fledged database management software like access or foxpro?

I have a database with several thousand records in an Excel file.
The database is basically a customer information and orders database.

The structure of the database is basically in a list format where all records
are listed neatly one below the other under their corresponding fields.
Even the same invoices with multiple orders are listed one below the
other like the example below. >>>

invoice# item# itemname qty

1001 175 blue widgets 2
1002 275 small widgets 1
1003 595 green widgets 2
1003 375 x-large widgets 3
1004 275 small widgets 2
1005 175 blue widgets 1
1005 675 red widgets 1

Should I import the database to a RDBMS using the above structure or the
one below??? The difference with the structure below is that each
record with multiple orders is listed in only one (1) line
with any additional items ordered listed immediately to the side in its
corresponding field. Which is easiest to work with and develop further
and / or modify in a rdbms like access or foxpro etc?
NOTE: the lists are easier to read in an excel file.

invoice# item# itemname qty item# itemname qty

1001 175 blue widgets 2
1002 275 small widgets 1
1003 595 green widgets 2 375 x-large widgets 3
1004 275 small widgets 2
1005 175 blue widgets 1 675 red widgets 1

Thanks in advance for the help. Peace!
~John 3:16

 

[DanJR]

Hi there,

I think you would find the first data structure you describe as the easiest to deal with in Access (or any other database) - you can then use SQL to 'transfer' the data into a normalized database. Neither of the data structure you describe are normalized. Have a search on google if you're unsure about normalization and creating databases, there should be plenty of free tutorials.

You may find that you need several tables to hold your data, e.g.

tblInvoices table (info about the invoice e.g. Invoice# and date)
tblInvoiceItems table (info about items ordered & quantity per invoice)
tblCustomers (info about customers)
tblItems (info about the items)

I'm not sure if i've answered all you questions, but the key is to learn about normalization which help you determine how many tables you need and what each table contains.

Hope this helps.
Cheers.
Dan