How to set up a pivot table from a csv file

H

Holly

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

Roger Govier

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
 
D

Debra Dalgleish

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 Data>PivotTable
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.
 
Top