Import Template Question

Hi,

I'm trying import some information from excel file into Access Database but I need have imported information formatted in specific way.

Import file is looking like:

City - Week / 1 / 2 / 3 / 4 / 5 / 6 / 7 / 8
London / 3 / 1 / 4 / 5/ 5/ 9/ 1/ 3/ 4
Chicago / 2 / 1 / 4/ 8/ 3/ 3/ 2/ 1 / 5
Paris / 9 / 4/ 1/ 7/ 8/ 9 / 1 / 1 / 2

And i need table in Access looking more like

City / Week / Value
London / 1 / 3
London / 2 / 1
London / 3/ 4
London / 4 / 5

and so on for each city.

Is there any option that this can be done within DB or It would need be some kind of macro to transfer this into other format?

Thanks for any help or hints.

You would need to custom write this. You would import your data as is into a temporary table (tmpTable) and then create the table with the correct structure (FinalTable). From there you could create an append query based on tmpTable that inserts 1 week's values, updating it to point to the correct week for every week you have.

Or, you could write some VBA that reads in tmpTable, then loops through every record, loops through the correct number of weeks and customizes an APPEND query for each piece of data.