Quickest way to transpose data from Excel to Access?

[JeffK627]

I have to copy data from an Excel spreadsheet to a table in an Access database. The problem is I have to transpose some of it. The first 7 columns can go in as is, but the next 13 columns are each assigned to a calendar quarter and each column contains the units sold for that quarter. In the database, there is one column for Quarter and another for Units Sold. I'm using a nested For/Next loop to assign the quarter to a variable, then loop through each row and insert that quarter's data into the table, then advance the outer loop and do it all again for the next quarter until all 13 quarters are processed. Of course, this is taking forever because there are 39,000 rows X 13 columns = 507,000 rows in the final database table. Can anyone suggest a faster way to do this? Thanks in advance!

 

[JeffK627]

Still struggling with this - can anyone help? Please?

 

[Hermanito]

Could you show details about your data structure? Does it all need to end up in one table? Somehow I get the impression you need to normalize the data a bit, to more than one table, so that you won't end up with 507K records... but I can't tell without knowing the data structure...

Are you creating the 507K rows in Excel first (so you have at least Excel 2007?)?
How do you transfer them to Access? Via Import Data in Access or via a copy/paste?

 

[obiron]

So you effectively want to un-pivot the table

[/FONT]
[FONT=Courier New]Product   Qtr1  Qtr2  Qtr3 [/FONT]
[FONT=Courier New][/FONT] 
[FONT=Courier New]Prod001    100  101   102[/FONT]
[FONT=Courier New]Prod002     10   35    44[/FONT]
[FONT=Courier New]

to

[FONT=Courier New]Product   Qtr    Units[/FONT]
[FONT=Courier New]Prod001   Qtr1   100[/FONT]
[FONT=Courier New]Prod001   Qtr2   101[/FONT]
[FONT=Courier New]Prod001   Qtr3   102[/FONT]
[FONT=Courier New]Prod002   Qtr1    10[/FONT]
[FONT=Courier New]Prod002   Qtr2    35[/FONT]
[FONT=Courier New]Prod002   Qtr3    44[/FONT]

If this is a one off excercise then I would suggest the easiest (not fastest or most elegant) would be to load the flat table into Access with 13 quarterly columns and create 13 queries that take the productID and qtrcolumn ( a different qtr column for each query)

then create a maketable query that UNIONs the 13 quarterly queries together.

You can then add indexes to the made table and delete the original table, 13 quarterly queries and maketable query.

 

[obiron]

Another thought -if you really want to do it in Excel.

Posting values cell by cell is slow, you can post a range of values as quickly as you can a single cell so writing the VBA to work with a multidimensional array will dramatically speed things up.

If a product has no units in a particular quarter, do you want a line in the results file

e.g.

[FONT=Courier New]Prod001      Qtr1     100[/FONT]
[FONT=Courier New]Prod002      Qtr2       0[/FONT]
[FONT=Courier New]Prod002      Qtr3     101[/FONT]

or should empty rows be dropped.

Turn the code round so that you write 13 lines for each product, rather than 39000 rows for each quarter because there is common data in the products.
copy the 7 product columns to a range
copy the 13 qtr columns to a range
create a multidimensional array (13,8) 13 rows of 8 values each
loop through the MDA and post the product range into the MDA and the offset value from the qtr array that matches the loop counter
On exiting the loop paste the MDA to the output worksheet in the first row that is not used.

This process should not take more than a couple of minutes max if written properly.

 

[JeffK627]

obiron - yes, I need to un-pivot the Excel table and insert all of it into a single Access table. I have to do it in VBA because the client is using the Access database from a server, like a SQL Server database (don't ask, I vigorously opposed this approach but was told to shut up). The end user will have no direct access to the database (no pun intended), they will be using the Excel sheet as a front end for quartely updates.

Empty rows for a product within a given quarter will be processed as 0.

The values for the quarters include the year: Q1_2008, Q2_2008 etc. up to the current calendar quarter.

I'd like to give your suggestion a try, but I'm not that experienced with MDAs in Excel VBA, can you help me to get started?

 

[obiron]

Will do,

I will dig out a file that I did it on tonight and post it later.

your problem is made harder by the fact that the number of quartely columns is going to increase. How does the heading of the quartely column need to be transformed so that it can be read by the access database.

e.g. Qtr1,Qtr2,Qtr3,Qtr4,Qtr5 or
2011-1,2011-2,2011-3,2011-4,2012-1

what is the structure of the access database - it sounds like there may be product data that is being repeated in each of the quartely records which is not good database design - but then I probably don't need to tell you that!!

 

[JeffK627]

OK, I managed to gather enough information to figure out a way to transpose the data onto another sheet in the Excel workbook with a run time of < 5 seconds. However, I still need a fast way to get those 500k + rows into the database table. Yes, they all have to go into one table, that decision is not mine to change. Any tips on the fastest way to copy 500,000 rows from Excel into Access using VBA that won't take forever?

 

[JeffK627]

The Quarter headers can stay in the "Qx_yyyy" format. And no, that table is not well normalized, it does repeat Product information over and over. If I were able to change the design I would!

Also, yes, the fact that there will be a new Quarter column every 3 months does complicate things!