Is PowerPivot Limited to look up 50 worksheets?

Question

I've been using UNION ALL queries in the "Table Properties>Query Editor" but have hit a solid brick wall.

I have 53 sheets in and Excel file to Union All but PowerPivot seems to be limiting me to 50.  Anybody have a solution to this problem?

 

Answer 1

There may be a limit of 50, but it is hard to believe that you would need to create a model from 53 "data sources" with 53 disparate tables. Perhaps you need to find a way to reorganize your data.

Answer 2

An alternate solution if you can't get powerpivot to work for you is to load the files into an access db.  I've done this with access 2003 (havn't tried it in 2010 yet).

The basic idea is to create a form with a list box connected to a table.   you load this table with all of the sheet names you need to bring in. then run some vb code that loops through each sheet name and does a transfer spreadsheet command to a temp table (with column named as F1, F2, F3...).  In each loop you also run another query which appends the data you want from the temp table to your real data table.  

Here is some sample code that I hacked together:

Private Sub cmdGetData_Click()
Dim strSheet As String
Dim strRange As String
Dim strFilename As String
Dim intX As Integer
Dim lngErrorCode As Long
On Error GoTo Err_Command0_Click:


strFilename = "c:\PROJECTS\myfilepath\myfilename.xls"

DoCmd.SetWarnings False

'need to add dynamic clear query

'lstWorksheetNames
For intX = 0 To lstWorksheetNames.ListCount - 1
    
    strSheet = lstWorksheetNames.ItemData(intX)
    'stWorksheetNames

    
    strRange = strSheet & "!a5:r145"
    DoCmd.OpenQuery "zClearDataImportTable"
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "DataImportTable", strFilename, False, strRange
    txtSheetName.SetFocus
    txtSheetName.Text = strSheet
    
     DoCmd.OpenQuery "AddSheetData"
    
    lstWorksheetNames.SetFocus
    
NEXTSHEET:
Next intX

DoCmd.SetWarnings True
MsgBox "Import Complete"



Exit_Command0_Click:
    Exit Sub

Err_Command0_Click:
    
    lngErrorCode = Err.Number
    If lngErrorCode = 3011 Then
        Resume NEXTSHEET:
    Else
    MsgBox Err.Description
    
    Resume Exit_Command0_Click
    End If
End Sub

 

This is not an "easy" solution but if you know a little vba and access it can be very helpful when all you are given is an excel file with 75 sheets.

once this is done you can connect to your access db with power pivot and you're all set.

 

cheers  :-)
 

Answer 3

Number of worksheets is excel is supposed to be limited by available memory. More details here http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP005199291.aspx

 

Where are you trying to create more than 50 worksheets? Inside Excel window or Inside PowerPivot window? What is the error you recieve? How much memory do you have available and remaining?

---

Tatyana Yakushev [PredixionSoftware.com]

Answer 4

I don't think there is a limit in no. of work sheets you can import into power pivot . But there is a limit on the size of the data you can process with power pivot. Since it is an in-memory processing engine your data should fit in memory. So please check if your data in 50 sheets exceeds your RAM memory. In that case it would give you an error.

Answer 5

Hi Tatyana

I was using PowerPivot to look up more than 50 worksheets from a single Excel file to collect certain columns

For some bizzare reason, the select all columns didn't work so I had to specify each column to import.

UNION ALL
SELECT [WorksheetName$].[ColumnName]  FROM [WorksheetName$]

The error I was receiving when I had more than 50 worksheets was

"The SQL statement is not valid. There are no columns detected in the statement."

It's only 2700 rows of data (20 columns)

Any thoughts?

Answer 6

Hi Sachin_100

The excel file is only 1.2Mb and the RAM is 5 Gb.  Would this be an issue?