Excel to Access to PowerPivot

[bolwahnn]

Hey Guys,

This might seem kind of strange, but I'll explain.....I currently have an Excel sheet with 100k+ rows of data (and growing) and 32 columns. I currently use Pivot tables to create reports and summarize the data (Reason I use Pivots is because users like to drill down into the data). Anyway, I ran into an issue today where I'm no longer able to create new calculated items due memory or maximum allowed has been reached (thats what warning prompt is telling me anyway).

So I came across PowerPivot and thought this may help me with my issues, However, it appears that the data streaming into PowerPivot needs to be from an external source such as a DB, Report, text file, etc. (Please correct me if I'm wrong on this assumption).....So what I'm attempting to do is create a link from my excel file to access database and then import data from that DB to PowerPivot. The problem is when I'm connecting to the PowerPivot to Access, it cant find a table in access with data.....

Any ideas on what I'm doing wrong? Or any better suggestions on what I can do for my dilemma (i.e. Cant create another calculated Item)?

Thanks,
Zeke

 

[scottsen]

Well, theoretically you could copy/paste a table from excel, directly into power pivot... but that's a pretty idea in your case

Options:

• From a new workbook, you can import directly into power pivot from your current excel file.
• You could export your current excel file to a CSV. From there... you could either populate a database, or import directly into power pivot.
• Was going to say "Linked Tables" but I think you still have trauma with "too many rows" in your excel table... so that is probably out.

I guess it kinda sorta depends on how you plan to maintain the data in the future? Like for monthly reporting or whatever, how will you append to the data (and then refresh the power pivot model) ?