Limits to Paste data ?

[Bilingual]

Hi, as i need to extract raw data from an external source and manually import it as .csv/.xls every month in Power pivot what is the solution?

The size is about 1 mio rows/20 columns each year.


1. I have tried to manually paste the data from the .csv as i would like to keep all data in one Power pivot table, but, even pasting 100.000 rows creates a system of out memory error, it does not make any sense as Excel can handle 1 mio columns.

2. It does not seem possible to first import from an Excel file via menu and then manually paste further data in the main file.

Have you any solutions? - i need to paste data from the external source into the powerpivot table each month.

 

[NickyvV]

Can you open the csv in Excel and create a table (I think Excel does that automatically). Otherwise: Convert text to columns under the Data Tab.
When you have that table (1) you could create a linked table to PowerPivot.

This procedure could be repeated every month except that you copy > paste the table from (1) over your linked table in Excel every time. The PowerPivot table then gets refreshed automatically (when you open PowerPivot).


HTH

 

[Laurent C]

As far as I know, there is no way to natively append data to a PowerPivot model. In your case, and given the size of your data, you might consider using a database backend to store your data. If you have an IT department and they have a DBMS Server then ask them to do the work for you. Otherwise, you can use Access or SQL Server Express.

 

[Bilingual]

Thanks a lot for both your answers! - i can now see two solutions.

NickyvV: If i do as you describes, will data not be overwritten every month by the new one when i update? - i need to accumulate data in a single table. Is it possible to consolidate/merging multiple Power pivot tables into one?

Laurent: It seems like making a local Access database is the easiest way to work around the limitations of Powerpivot, i think it is very sad that
PP is primarily limited to connect to databases and the possibilities for pasting data directly is so limited.