Access into Excel Pivot Tables

[RET79]

Hi.

I have a MASSIVE text file, which has 1.7million rows * 180 characters wide.

It's a fixed width text file, and I am only interested in the sums.

First step involves importing the fixed width file into access, which is much the same as with the excel process. Then, after seperating the text into 40 meaningful columns, I want to summarize a few of these columns as sums. I tried doing this via an excel pivot table, letting the excel pivot table receive the data from an external source.

Needless to say, this process of reading in SO MUCH data from Access took an awful long time and it crashed a few times.

Am I being an over ambitious idiot here?

RET79

[ This Message was edited by: RET79 on 2002-05-01 13:46 ]

[dk]

Hi Ret,

Depends on your hardware. I recently tried to create a pivot table based on an Access table of around 1 million records. It got to three quarters the way through and gave me an Out of memory message. This was on a 1.6 ghz with 512mb RAM so was fairly powerful.

Could you not create a another query in Access which reduces the number of records (either by restricting the data e.g. date ranges or by summarising sum how)? This is what I did. I removed any unnecessary fields from my query, made sure that my tables in Access were as efficient as possible (i.e. field sizes and types, relationships) and then created a query which only returned records from 2001-2002 rather than the 5 year range of data the table holds. You could always write some VBA code which allows you to change the paramters of your query from Excel.

HTH,
Dan

[RET79]

Dan,

Yes, that was exactly my approach too. In the end I set up access queries instead but even that took a while for it to produce the query. And yes, your computer is much faster and powerful than the one I have !!!

RET79