Getting started with PowerPivot

[dugdugdug]

Can PP be used with VBA or is it only for displaying large (> 1 million rows) data?

 

[RoryA]

What are you actually trying to do with these csv files?

 

[prez02]

Yes, a very simple example would be to open say 10 large csv files and do something to it.

The first stumbling block is opening it in normal Excel 2010 (because it's large) but PP can manage that except I can't systematically tell it to do 10 times over.

How do I go about setting up a PP template to do the extraction?

You have to open Excel, open the Powerpivot Window, select the home tab, click on other sources, scroll down to text file, locate the csv file on your computer and make a few choices. If you click ok, the content of the csv is loaded into excel and displayed in the powerpivot window. The table and the csv stay connected to each other, so if you have a newer version of the csv file with fresh data, you change the csv files on your computer and then refresh the powerpivot table.

In the powerpivot window you can add calculated columns, e.g. adding the values of two columns, etc. A lot of the function you'll find in excel are also available here.

On the basis of this table, you then can create a pivottable in an excel sheet. You can also create a more "simple" or flat pivot table, which is more like a list, depending on what your report should look like. You can also create measures, or calculated fields in the pivottable. Here lies the real power of powerpivot.

Once you have done this, you can then use a newer/different csv while, provided it has the same columns, refresh the tabel in the powerpivot window, then refresh the pivottabel in Excel.

This is a bit cumbersome, only in Excel 2013 this can be done via VBA, so there is not really a one-click solution.

Powerpivot is for creating reports in form of a pivottable using its own formula language and allowing you to combine data from different sources. I am not sure if this is really what you are looking for. A bit of extra information might be helpful...

Carsten
P.S.:You can connect several csv files to one excel file, creating several tables in the powerpivot window.

 

[dugdugdug]

You have to open Excel, open the Powerpivot Window, select the home tab, click on other sources, scroll down to text file, locate the csv file on your computer and make a few choices. If you click ok, the content of the csv is loaded into excel and displayed in the powerpivot window. The table and the csv stay connected to each other, so if you have a newer version of the csv file with fresh data, you change the csv files on your computer and then refresh the powerpivot table.

In the powerpivot window you can add calculated columns, e.g. adding the values of two columns, etc. A lot of the function you'll find in excel are also available here.

On the basis of this table, you then can create a pivottable in an excel sheet. You can also create a more "simple" or flat pivot table, which is more like a list, depending on what your report should look like. You can also create measures, or calculated fields in the pivottable. Here lies the real power of powerpivot.

Once you have done this, you can then use a newer/different csv while, provided it has the same columns, refresh the tabel in the powerpivot window, then refresh the pivottabel in Excel.

This is a bit cumbersome, only in Excel 2013 this can be done via VBA, so there is not really a one-click solution.

Powerpivot is for creating reports in form of a pivottable using its own formula language and allowing you to combine data from different sources. I am not sure if this is really what you are looking for. A bit of extra information might be helpful...

Carsten
P.S.:You can connect several csv files to one excel file, creating several tables in the powerpivot window.

Thanks for the instructions but it doesn't sound like at any stage, it's automatable.

 

[dugdugdug]

What are you actually trying to do with these csv files?

There are various tasks but a simple one would be to look for identical values.

Assuming there are only 2 fields, I sort by field A, then apply a similar formula to this:

=IF(AND(AND(A4=A3,A4=A2),B4=B3,B4=B2),"Same","Not")

This checks for a difference of 2 but obviously I need it to be dynamic so the user can choose the desired number.

If 3 was chosen, it would be:

=IF(AND(AND(AND(A8=A7,A8=A6,A8=A5),B8=B7,B8=B6,B8=B5)),"Same","Not")

I have done all this looping in VBA and have got my desired results. The problem is the first step, reading the csv file when it contains a few million rows.

 

[prez02]

Thanks for the instructions but it doesn't sound like at any stage, it's automatable.

Well, Powerpivot will not allow you to load more than a million rows in an ordinary excel sheet, and then transform it with your vba code. There is no way to do this.

That said, large tables combined with vba not working well was exactly what drove me to powerpivot, so I am sure it could help you, but only after you spend some time learning new stuff.

Carsten

 

[miguel.escobar]

There are various tasks but a simple one would be to look for identical values.

Assuming there are only 2 fields, I sort by field A, then apply a similar formula to this:

=IF(AND(AND(A4=A3,A4=A2),B4=B3,B4=B2),"Same","Not")

This checks for a difference of 2 but obviously I need it to be dynamic so the user can choose the desired number.

If 3 was chosen, it would be:

=IF(AND(AND(AND(A8=A7,A8=A6,A8=A5),B8=B7,B8=B6,B8=B5)),"Same","Not")

I have done all this looping in VBA and have got my desired results. The problem is the first step, reading the csv file when it contains a few million rows.

you should really try Data Explorer...let's say that you get the csv file from an application or a web location. You can query that data from data explorer, then create a transformation process within that load process and then once you have the data how you want it you can then load it into the data model for its usage with Powerpivot. I can't think of anything more systematically automated.

 

[RoryA]

To be honest it sounds as though a database would be a better option here.

 

[miguel.escobar]

I second that, Rory.Hopefully dug doesn't have any type of security issues. Many times I've had friends that can't connect to their databases due to corporate security concerns :/

 

[dugdugdug]

I second that, Rory.Hopefully dug doesn't have any type of security issues. Many times I've had friends that can't connect to their databases due to corporate security concerns :/

Re database, I assume you mean store the data in a db and ADO what you want?

Tried that but other problems exist such as the 2GB limit. Probably have to store it in an SQL Server, though that too has a 10GB limit!

Right now, I think Excel and Access are just about OK for my tasks (for example I've had to write extra code to convert filenames because Access can't handle filenames longer than a certain length) but all this adds to the memory usage and eventually will topple over.

Will try Data Explorer as mentioned (if IT have the budget / security allowance) for it!

 

[RoryA]

The database size limitation for SQL Server 2012 is 524,272 terabytes which should be enough...