Getting started with PowerPivot

[dugdugdug]

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

 

[RoryA]

In 2013 some of the data model is exposed to VBA but PowerPivot itself is not.

 

[dugdugdug]

My task is to read csv files containing a few million records, select certain criteria and create a report of the output.

So PP would not be of use, despite it being able to handle very large volumes of data?

 

[RoryA]

Why would PP not be of use? What does VBA have to do with it?

 

[miguel.escobar]

ADVERTISEMENT

actually, it could be of great use! shoot some questions on what specifically you're trying to do and I can guarantee that you'll be able to perform those tasks using Powerpivot

 

[dugdugdug]

Why would PP not be of use? What does VBA have to do with it?

actually, it could be of great use! shoot some questions on what specifically you're trying to do and I can guarantee that you'll be able to perform those tasks using Powerpivot

One of things I'm trying to achieve is import large (> 1million rows) csv files, look for a certain criteria, if it exists then extract them, followed by other manipulation stages.

All this needs to be done at a click of a button, so if VBA cannot instruct PP to "do things", ie recording a macro involving PP does not show up any code, then how can PP be of use for automating the process?

 

[RoryA]

ADVERTISEMENT

You never mentioned automating an entire process before. If you can reuse the same csv file names, you could probably set up a PP template to do the extraction you need and then just refresh the model (but you would have to do that manually unless using 2013).

 

[miguel.escobar]

One of things I'm trying to achieve is import large (> 1million rows) csv files, look for a certain criteria, if it exists then extract them, followed by other manipulation stages.

All this needs to be done at a click of a button, so if VBA cannot instruct PP to "do things", ie recording a macro involving PP does not show up any code, then how can PP be of use for automating the process?

You might want to take a look at Data Explorer for the ETL process.

 

[dugdugdug]

You never mentioned automating an entire process before. If you can reuse the same csv file names, you could probably set up a PP template to do the extraction you need and then just refresh the model (but you would have to do that manually unless using 2013).

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?

 

[miguel.escobar]

One of things I'm trying to achieve is import large (> 1million rows) csv files, look for a certain criteria, if it exists then extract them, followed by other manipulation stages.

All this needs to be done at a click of a button, so if VBA cannot instruct PP to "do things", ie recording a macro involving PP does not show up any code, then how can PP be of use for automating the process?

You might want to take a look at Data Explorer for the ETL process.