Import Template Issue

[kevin28]

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.

K

[orange]

No direct option. You'll have to write special code/macro.
Do you have any experience with relational database?
Do you understand tables and normalization?

When you post the same question on multiple sites/forums, it is considered proper etiquette to inform users that you have done so

It isn't wrong to "cross post", but show readers that you have posted elsewhere. This is the rationale.

cross posted http://www.access-programmers.co.uk/...d.php?t=274271

[kevin28]

I don't have a lot experience with MS Access but managed create couple database to pull reports. I understand point of tables and have minimal knowledge on normalization.
OK then only way to do it will be VBA ?

Sorry for cross posting. I was only trying get some feedback and finish project.

[June7]

It's a good sign that the final structure you want the data in is normalized. I wonder if year and month are important?

Could possibly import the data into a temporary table and use queries and/or VBA code to restructure into the permanent table.

The only other alternative is to first fix the data structure in Excel so that the import would be simple.

Start with a copy/transpose in Excel which could result in:

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

Then a lot of copy/paste to normalize the structure or import that transposed structure into a temp table and finish manipulating in Access.

A UNION query might be involved to rearrange the data into normalized structure.

How many weeks and cities are there? Is this a one-time event or a regular process?