code logic help

[jscriptor09]

Hi folks

I have regular csv file that I regularly import into an access 2010 db. right now, the csv come in in this format



row title ... Dept , Resources, location, week1, week2, week3 ....
row 1 ... PROD, Jim , SWE ,0 , 10 , 45 ....
row 2 ... RD , Sue , N ,0 , 10 , 45 ....
...
....


there are 150+ weeks (3 years)
and 4600 rows of data

before importing into ACCESS, I need to convert the FW into single field. Bascially, it needs to be:
FW, Dept, Resources, Location, Values

I have a for i and for j loop to do the transpose. I do in in Excel VBA before importing the file into access.

As you can imagine, the resulting trspose is 600,000+ lines

The Excel process to do the transpose is very inefficient. The looping takes over 1/2 hr.

Any thought on how to improve this, or even abetter way would be appreciated.

thanks
Mike

[orange]

FW???

Suggest you import to an Access temporary table; then create some queries to move the appropriate data to normalized tables. You do not want week1,week2,week3 in the same table --
I can see tables along this: (along with appropriate junction tables)

Dept
Resources
Location
TimePeriod

But we really don't know anything about your Business or environment.