none
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?

     

    Friday, January 27, 2012 3:11 PM

Answers

  • 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  :-)
     

    Friday, January 27, 2012 10:20 PM

All replies

  • 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.
    Friday, January 27, 2012 5:10 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.

     

    cheers  :-)
     

    Friday, January 27, 2012 10:20 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]
    Tuesday, January 31, 2012 10:28 PM
  • 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.
    Wednesday, February 01, 2012 6:50 AM
  • 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?

    Wednesday, February 08, 2012 4:45 PM
  • Hi Sachin_100

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

    Wednesday, February 08, 2012 4:56 PM
  • Is this still an issue? 

    Thanks!


    Ed Price, SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Wednesday, September 18, 2013 9:11 PM