Friday, January 27, 2012 3:11 PM
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?
Friday, January 27, 2012 5:10 PMThere 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.
Friday, January 27, 2012 10:20 PM
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.
- Proposed As Answer by Challen FuModerator Tuesday, January 31, 2012 7:10 AM
Tuesday, January 31, 2012 10:28 PM
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]
Wednesday, February 01, 2012 6:50 AMI 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.
Wednesday, February 08, 2012 4:45 PM
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.
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)
Wednesday, February 08, 2012 4:56 PM
The excel file is only 1.2Mb and the RAM is 5 Gb. Would this be an issue?