Import xls data

[Guest]

I have a excell workbook that contains 4 sheets each sheet representing a
country and each have the same columns of data.

Entry Date - Client- Acct Name- Account Number - Invoice Num- Invoice
details-Invoice Amt

There are hundreds of rows and the data above needs to be imported into
several tables.Is there a way to avoid the manual entries into the tables?

Thank you

 

[Guest]

Yes, if you go to File->Get External Data, you can import Excel data into
Access Tables. You may have to import the same data several times to get it
into the tables you want.

 

[Guest]

That sounds simple enough but what happens when you want the client number as
the primary key since it is unique and this information is repeated many time
in the rows of excell? How does the import determine that the different row
headings belong in different tables?

 

[Guest]

I would like to know if when you do the import and the client number is the
primary key which is unique then how does access collect all the rows of data
in excell that belong to each of the unique client numbers?

 

[John W. Vinson]

I have a excell workbook that contains 4 sheets each sheet representing a
country and each have the same columns of data.

Entry Date - Client- Acct Name- Account Number - Invoice Num- Invoice
details-Invoice Amt

There are hundreds of rows and the data above needs to be imported into
several tables.Is there a way to avoid the manual entries into the tables?

Thank you

You'll probably want to create three new tables: Clients, with the
client and account information (this will be two tables in a
one-to-many relationship if each client can have multiple accounts);
Invoices (related one to many to Accounts); and InvoiceDetails,
related one to many to Invoices.

You'ld use File... Get External Data... Link to link to your
spreadsheet (either running it four times, linked to each sheet in
turn, or you could create four queries). You can then run Append
queries to migrate the data into your normalized tables.

I can't tell from here (since I can't see the data) which fields
should go in which table - does the EntryDate pertain to an invoice
detail or to an invoice? Is the Invoice Amt a sum of details? Are
there multiple columns of invoice details, or multiple rows? - so I
can't write the query for you, but don't hesitate to post back with
more details (perhaps a few rows of the spreadsheet, munged for
privacy if you wish).

John W. Vinson [MVP]