create pivot table from external csv file

[Sunny]

I am dumping huge data to csv file, Now what I want to do is, create pivot
table/chart from csv file without importing data into it. Reason behind
doing this is, end user view updated report without messing with thier
limited excel knowledge.

[Shane Devenshire]

Hi,

So what's your question?

If you're asking if it can be done, the answer is Yes.

If you're asking how to do it, the question is - what version of Excel are
you using?

For Example in 2003 you would choose Data, Pivot Table and Pivot Chart
Report, and choose External data source, Next, Get Data, choose New Data
Source and click OK. Enter a name like myCSVData and open the second
dropdown. Select Microsoft Text Driver (*.txt,*.csv), click Connect.
Uncheck Use Current Directory, and then choose Select Directory. Find your
file and open it, you will be in a wizard, whose steps are fairly straight
forward.
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire

Post by Sunny
I am dumping huge data to csv file, Now what I want to do is, create pivot
table/chart from csv file without importing data into it. Reason behind
doing this is, end user view updated report without messing with thier
limited excel knowledge.

[Sunny]

Sorry for not clear about my question.

I am using excel 2007 and I tried to follow your steps for excell 2003 but
there is no Get Data option. Can you please guide me for excel 2007? Would
that import data in excel or create link to csv? Do i have to do same
excercise every time I create new csv file?

Thanks for looking into it.

Post by Shane Devenshire
Hi,
So what's your question?
If you're asking if it can be done, the answer is Yes.
If you're asking how to do it, the question is - what version of Excel are
you using?
For Example in 2003 you would choose Data, Pivot Table and Pivot Chart
Report, and choose External data source, Next, Get Data, choose New Data
Source and click OK. Enter a name like myCSVData and open the second
dropdown. Select Microsoft Text Driver (*.txt,*.csv), click Connect.
Uncheck Use Current Directory, and then choose Select Directory. Find your
file and open it, you will be in a wizard, whose steps are fairly straight
forward.
--
If this helps, please click the Yes button
Cheers,
Shane Devenshire

Post by Sunny
I am dumping huge data to csv file, Now what I want to do is, create pivot
table/chart from csv file without importing data into it. Reason behind
doing this is, end user view updated report without messing with thier
limited excel knowledge.

[Shane Devenshire]

Hi,

Sorry to take so long getting back to you, but I work and have a long commut.

In 2007, Press Alt D P this will put you in the 2003 pivot table wizard.
choose the External Data Source option and proceed as described in my
previous email.
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire

Post by Sunny
Sorry for not clear about my question.
I am using excel 2007 and I tried to follow your steps for excell 2003 but
there is no Get Data option. Can you please guide me for excel 2007? Would
that import data in excel or create link to csv? Do i have to do same
excercise every time I create new csv file?
Thanks for looking into it.

Post by Shane Devenshire
Hi,
So what's your question?
If you're asking if it can be done, the answer is Yes.
If you're asking how to do it, the question is - what version of Excel are
you using?
For Example in 2003 you would choose Data, Pivot Table and Pivot Chart
Report, and choose External data source, Next, Get Data, choose New Data
Source and click OK. Enter a name like myCSVData and open the second
dropdown. Select Microsoft Text Driver (*.txt,*.csv), click Connect.
Uncheck Use Current Directory, and then choose Select Directory. Find your
file and open it, you will be in a wizard, whose steps are fairly straight
forward.
--
If this helps, please click the Yes button
Cheers,
Shane Devenshire

Post by Sunny
I am dumping huge data to csv file, Now what I want to do is, create pivot
table/chart from csv file without importing data into it. Reason behind
doing this is, end user view updated report without messing with thier
limited excel knowledge.

[Roger Govier]

Hi Sunny

In Excel 2007>Data tab>Get External Data>From Other Source>from Microsoft
Query>
It then proceeds the same as XL2003
Text files>OK>Choose your CSV file>Select fields>Return Data>Pivot Table
Report
--
Regards
Roger Govier

Post by Sunny
Sorry for not clear about my question.
I am using excel 2007 and I tried to follow your steps for excell 2003 but
there is no Get Data option. Can you please guide me for excel 2007? Would
that import data in excel or create link to csv? Do i have to do same
excercise every time I create new csv file?
Thanks for looking into it.

Post by Shane Devenshire
Hi,
So what's your question?
If you're asking if it can be done, the answer is Yes.
If you're asking how to do it, the question is - what version of Excel are
you using?
For Example in 2003 you would choose Data, Pivot Table and Pivot Chart
Report, and choose External data source, Next, Get Data, choose New Data
Source and click OK. Enter a name like myCSVData and open the second
dropdown. Select Microsoft Text Driver (*.txt,*.csv), click Connect.
Uncheck Use Current Directory, and then choose Select Directory. Find your
file and open it, you will be in a wizard, whose steps are fairly straight
forward.
--
If this helps, please click the Yes button
Cheers,
Shane Devenshire

Post by Sunny
I am dumping huge data to csv file, Now what I want to do is, create pivot
table/chart from csv file without importing data into it. Reason behind
doing this is, end user view updated report without messing with thier
limited excel knowledge.

[Sunny]

Shane and Roger,

Thanks you very much and it worked great. Although I have couple more
questions:

1. I notice file size is very big as my csv file. I assumes it imports data
and not link the data. Would that be possible to link it only.

2. Is there any way in pivot table to find unique count? Like I want total
sales amount along with total sales person involved in that sales. Total
sales can be grouped in Region/county and some other filter. If I use count
that shows # of sales in the group which is different from # of unique sales
person.

I really appreciate your help.

Thanks.

Post by Roger Govier
Hi Sunny
In Excel 2007>Data tab>Get External Data>From Other Source>from Microsoft
Query>
It then proceeds the same as XL2003
Text files>OK>Choose your CSV file>Select fields>Return Data>Pivot Table
Report
--
Regards
Roger Govier

Post by Sunny
Sorry for not clear about my question.
I am using excel 2007 and I tried to follow your steps for excell 2003
but there is no Get Data option. Can you please guide me for excel 2007?
Would that import data in excel or create link to csv? Do i have to do
same excercise every time I create new csv file?
Thanks for looking into it.

Post by Shane Devenshire
Hi,
So what's your question?
If you're asking if it can be done, the answer is Yes.
If you're asking how to do it, the question is - what version of Excel are
you using?
For Example in 2003 you would choose Data, Pivot Table and Pivot Chart
Report, and choose External data source, Next, Get Data, choose New Data
Source and click OK. Enter a name like myCSVData and open the second
dropdown. Select Microsoft Text Driver (*.txt,*.csv), click Connect.
Uncheck Use Current Directory, and then choose Select Directory. Find your
file and open it, you will be in a wizard, whose steps are fairly straight
forward.
--
If this helps, please click the Yes button
Cheers,
Shane Devenshire

Post by Sunny
I am dumping huge data to csv file, Now what I want to do is, create pivot
table/chart from csv file without importing data into it. Reason behind
doing this is, end user view updated report without messing with thier
limited excel knowledge.