Converting a large Excel matrix to rows for upload into Access.

[Tyler Durdin]

I have a table of data in Excel with around 1,500 columns and around the same number of rows and I'd like to transfer the data into Access for further calculations. Excel is struggling to cope given the size of the data set.

I cannot reference the table directly from within Access because of Access's column limit. The source data needs to stay in Excel because it's the output of a number of other files/models which write directly into Excel.
A complicating factor is the number of records. If I convert the data into row format (from row and column format) to upload into access there will be around 2.25 million rows i.e. too large for a single Excel worksheet. Hence I have to split the data across multiple Excel worksheets, use lookup formulas to transform the data, then upload into access one by one. Obviously this is a laborious process.

Here is the current format :

[TABLE="width: 100"]
<tbody>[TR]
[TD][/TD]
[TD]Dest[/TD]
[TD]D1[/TD]
[TD]D1[/TD]
[TD]D1[/TD]
[TD]D2[/TD]
[TD]D2[/TD]
[/TR]
[TR]
[TD]Site[/TD]
[TD]Quality[/TD]
[TD]Q1[/TD]
[TD]Q2[/TD]
[TD]Q3[/TD]
[TD]D1[/TD]
[TD]Q2[/TD]
[/TR]
[TR]
[TD]S1[/TD]
[TD]Q1[/TD]
[TD]0.26[/TD]
[TD]0.74[/TD]
[TD]0.48[/TD]
[TD]0.11[/TD]
[TD]0.58[/TD]
[/TR]
[TR]
[TD]S1[/TD]
[TD]Q2[/TD]
[TD]0.45[/TD]
[TD]0.54[/TD]
[TD]0.57[/TD]
[TD]0.75[/TD]
[TD]0.47[/TD]
[/TR]
[TR]
[TD]S1
[/TD]
[TD]Q3[/TD]
[TD]0.75[/TD]
[TD]0.99[/TD]
[TD]0.24[/TD]
[TD]0.67[/TD]
[TD]0.26[/TD]
[/TR]
[TR]
[TD]S2[/TD]
[TD]Q1[/TD]
[TD]0.268[/TD]
[TD]0.98[/TD]
[TD]0.36[/TD]
[TD]0.29[/TD]
[TD]0.71[/TD]
[/TR]
[TR]
[TD]S2[/TD]
[TD]Q2[/TD]
[TD]0.87[/TD]
[TD]0.39[/TD]
[TD]0.74[/TD]
[TD]0.54[/TD]
[TD]0.28[/TD]
[/TR]
[TR]
[TD]S2[/TD]
[TD]Q3[/TD]
[TD]0.47[/TD]
[TD]0.14[/TD]
[TD]0.73[/TD]
[TD]0.37[/TD]
[TD]0.88[/TD]
[/TR]
</tbody>[/TABLE]

And here is the desired format:


[TABLE="width: 100"]
<tbody>[TR]
[TD]Site[/TD]
[TD]SiteQ[/TD]
[TD]Dest[/TD]
[TD]DestQ[/TD]
[TD]Rate[/TD]
[/TR]
[TR]
[TD]S1[/TD]
[TD]Q1[/TD]
[TD]D1[/TD]
[TD]Q1[/TD]
[TD]0.26[/TD]
[/TR]
[TR]
[TD]S1[/TD]
[TD]Q1[/TD]
[TD]D1[/TD]
[TD]Q2[/TD]
[TD]0.74[/TD]
[/TR]
[TR]
[TD]S1[/TD]
[TD]Q1[/TD]
[TD]D1[/TD]
[TD]Q3[/TD]
[TD]0.48[/TD]
[/TR]
</tbody>[/TABLE]

etc.

Can anyone a) tell me the best format to convert the data to, (I'm assuming csv but Excel can't hold 2.25 million rows then convert to csv) and b) suggest a way of doing this with vba ?

Any advice will be greatly appreciated.

JC.

 

[jkpieterse]

I wonder whether you might use PowerQuery and load the data into the datamodel rather than into Excel and use Powerpivot to do your calculations?

 

[Tyler Durdin]

Hi Jan and thanks for your suggestion. I've recently started experimenting with Power Query and I love the unpivot function. However, I haven't been able to find a way of using it to 'unpivot' based on 2 header rows. I could concatentate the data but it will add time and make subsequent manipulation difficult. Can you suggest a method within power query of achieving this, or suggest somewhere I can start looking ?