How to set up a pivot table from a csv file
 

I have a 200K records csv file, I need to set up a pivot table to analyse the
data. How should I start?

How to set up a pivot table from a csv file
 

Hi Holly

What version of Excel do you have?
If it is XL2007, you can import the whole 200,000 records to a single
sheet.
If you have an earlier version, then you would need to chop the file
into chunks of < 65535 records, to allow for a header row for each
sheet.

Chip Pearson has some code to do this on his site at
http://www.cpearson.com/excel/ImportBigFiles.htm

Be aware, however, that subsequently creating a Pivot Table from
Multiple Consolidation ranges, which is what you would have to do from
your 4 sheets, may produce slightly different results to those expected.

Take a look at Debra Dalgleish's site
http://www.contextures.com/xlPivot08.html

--
Regards

Roger Govier


"Holly" wrote in message
...
I have a 200K records csv file, I need to set up a pivot table to
analyse the
data. How should I start?

How to set up a pivot table from a csv file
 

Hi Holly

Since posting, I have just noticed in another thread in this NG, some
VBA code set up in an Excel file for carrying out this task that has
been created by J Latham.

The file can be downloaded at

http://www.jlathamsite.com/uploads/I...RowsOfData.xls

--
Regards

Roger Govier


"Roger Govier" wrote in message
...
Hi Holly

What version of Excel do you have?
If it is XL2007, you can import the whole 200,000 records to a single
sheet.
If you have an earlier version, then you would need to chop the file
into chunks of < 65535 records, to allow for a header row for each
sheet.

Chip Pearson has some code to do this on his site at
http://www.cpearson.com/excel/ImportBigFiles.htm

Be aware, however, that subsequently creating a Pivot Table from
Multiple Consolidation ranges, which is what you would have to do from
your 4 sheets, may produce slightly different results to those
expected.

Take a look at Debra Dalgleish's site
http://www.contextures.com/xlPivot08.html

--
Regards

Roger Govier


"Holly" wrote in message
...
I have a 200K records csv file, I need to set up a pivot table to
analyse the
data. How should I start?

How to set up a pivot table from a csv file
 

You could connect to the csv file, but the steps are different in Excel
2007 and earlier versions of Excel.

In Excel 2003, you could open a new workbook and choose DataPivotTable
and PivotChart report
Select External Data source, and click Next
Click Get Data
Select <New Data Source and click OK
Type a name for the data source, and select Microsoft text driver
Click Connect, select the directory, click OK
Select your table, click OK, click OK
In the Query Wizard, select your fields, and click Next 3 times, to get
to the end of the Wizard, click Finish
Click Next, click Layout, and set up the pivot table
Click OK, click Finish.

Holly wrote:
I have a 200K records csv file, I need to set up a pivot table to analyse the
data. How should I start?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html