locked
Importing multiple files with SQL UNION ALL works for only 2 files. No column detected error on 3rd file. RRS feed

  • Question

  • I'm trying to import multiple xlsx files (identical structure) according to this tutorial which is referenced in so many posts here: contextures.com/PowerPivot-Identical-Excel-Files.html

    It works beautifully for 2 files but the moment I add a 2nd UNION ALL statement I get a "No column detected" error.

    This works:

    SELECT ['mychoice-final-b9BwZvd@month da$'].* FROM ['mychoice-final-b9BwZvd@month da$']
    UNION ALL
    SELECT * FROM `D:\mark.xlsx`.[mark$]

    This works:

    SELECT ['mychoice-final-b9BwZvd@month da$'].* FROM ['mychoice-final-b9BwZvd@month da$']
    UNION ALL
    SELECT * FROM `D:\john.xlsx`.[john$]

    This doesn't:

    SELECT ['mychoice-final-b9BwZvd@month da$'].* FROM ['mychoice-final-b9BwZvd@month da$']
    UNION ALL
    SELECT * FROM `D:\mark.xlsx`.[mark$]
    UNION ALL
    SELECT * FROM `D:\john.xlsx`.[john$]

    It's driving me crazy! I just don't understand why it doesn't work. I've looked up UNION ALL syntax and read every single post I could find, I've triple checked the table names too, the structure is identical. The latter results in a "no column detected". Can anyone help? 

    Monday, November 12, 2012 5:30 PM

Answers

  • Hey Joanna,

          Can you limit some of the data to avoid the 2GB limit? (such that you don't import those big columns of unique data? Also you can try the Office 15 Beta. looks like it's at: http://www.microsoft.com/office/preview/en though the PP files you create in that won't be able to work with Office 14. If you have a full AS server you could also use that.
    Thanks,

    Bradley

    Tuesday, November 13, 2012 10:39 PM

All replies

  • After a lot of trial and error I think the problem is the number of columns in my Excel files (I have 124 columns). I have deleted them (different types, numerical and digits) and it seems that once my document has more than 85 columns I get the abive error. If the document has 85 columns or less I can add as many files using UNION ALL.

    Has anyone encountered this problem? Can anyone offer a workaround?

    Monday, November 12, 2012 6:09 PM
  • I haven't had this issue but can tell you that a 'triple' Union All will definitely work in Powerpivot.

    If I had to guess at the solution to your problem its that there is something 'wrong' with column 86 in 'John'! Have you tried it with all columns except that one?

    An alternative solution that may have other benefits in the long run is to import the xlsx files into a single Access table.
    • Edited by barnettjacob Tuesday, November 13, 2012 1:12 AM Typo
    Tuesday, November 13, 2012 1:04 AM
  • I had a similar probem while importing EXCEL files with many colums (about 450). With a 32 bits of EXCEL, I can only import about 200 columns and cut the others. I think it is due to the limitation of the "old" OLEDB driver used by Powerpivot for EXCEL files. Doing the same thing with SQL  SERVER 2012 64 bits SSIS with  a UNION clause on the same files works. Try on a 64 bits machine with OFFICE 2010 64 bits if you can. I resolved my problem by converting to csv and using the command COPY TABLE1.csv TABLE2.csv TABLE3.csv= TABLE.csv and then selecting less columns in the import wizard of powerpivot. 
    Tuesday, November 13, 2012 8:54 AM
  • Thank you both for your replies. I've tried this with many different column types. For example, I now have 1 row of data (all cells = 1) and 96 columns. 3rd UNION ALL returns an error. The moment I cut this down to 85 I can have as many as I want (tried 28 UNION ALL statements). I'm running Office 2010 on 64bit Windows 7.

    I did consider Access (I'm new to that as well but happy to learn!) but it truncates some cells to 255 characters. If I were able to overcome that then it would be a great workaround!

    Nguyen, is the COPY command a SQL one that you ran in the Query Editor in Power Pivot?

    Thank you!

    Tuesday, November 13, 2012 9:39 AM
  • Joanna, For this purpose you don't need to know much about Access! You can store more than 255 by changing the data type for that field to Memo and reimporting your data. Jacob
    Tuesday, November 13, 2012 10:32 AM
  • Ok I've found some code for a macro to import an entire folder of files into access. Using TransferSpreadsheet seems to overcome the 255 limit (I need to do this as a batch procedure as I have so many). Time to import the DB in PowerPivot. Fingers crossed! 
    Tuesday, November 13, 2012 11:27 AM
  • Well tried it and I hit the 2gb database file size pretty quickly (I have analytics data for 22 months, each month file ~110MB). All I want is to use PowerPivot to analyse all this data!

    Tuesday, November 13, 2012 3:14 PM
  • Hey Joanna,

          Can you limit some of the data to avoid the 2GB limit? (such that you don't import those big columns of unique data? Also you can try the Office 15 Beta. looks like it's at: http://www.microsoft.com/office/preview/en though the PP files you create in that won't be able to work with Office 14. If you have a full AS server you could also use that.
    Thanks,

    Bradley

    Tuesday, November 13, 2012 10:39 PM