Post reply

Issue importing xlsx or xls; sheets greater than 255 columns in size.

  • February 22, 2012 at 9:40 pm

    #256217

    Hi All,

    I have encountered an issue when using the OPENROWSET to import xlsx files using the following provider:

    Microsoft.ACE.OLEDB.12.0

    The columns are imported F1, F2, .. F255, however, once 255 is reached the next column seems to be out of bounds for the provider.

    After doing some research on the provider I've managed to conclude that this is a limitation of the provider itself (something about the old excel sheet sizes being 255)

    Anyway, does anyone have any SQL solutions for this? (it's not feasible for me to learn C# in order to write a custom SSIS package unfortunately)

    From reading Wayne Sheffield pretty awesome Excel Automation article (http://qa.sqlservercentral.com/articles/Excel/64838/) it seems possible 🙂

    Thanks in advance

  • February 24, 2012 at 12:20 am

    #1451199

    In SSIS, use the datatype DT_NTEXT instead of the regular DT_WSTR.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • February 24, 2012 at 1:23 am

    #1451214

    Is there any chance that you could get the spreadsheet exported to .CSV format as step one of the process? Then you should find that the limitations are removed.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • February 26, 2012 at 3:29 pm

    #1451853

    Koen Verbeeck (2/24/2012)

    In SSIS, use the datatype DT_NTEXT instead of the regular DT_WSTR.

    Any instructions as to how this can be achieved? Can you set this with a SQL statement?

    Cheers

  • February 26, 2012 at 11:54 pm

    #1451894

    You can set this in the Advanced Editor of the Excel Source component.

    Right click on the source, choose Advanced Editor.

    Input and Output properties --> source output --> output columns.

    Choose the column you want to change and look for the datatype in the properties.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • February 27, 2012 at 2:04 am

    #1451926

    Unfortunately I need to do this from within a stored procedure dynamically for a set of excel files.

    Cheers for the information anyway 🙂

  • February 27, 2012 at 11:37 am

    #1452212

    Koen, I think the issue is the number of columns, not the length of a column.

    In that case, there is a solution but it is a third-party commercial solution. I would recommend you check CozyRoc Excel adapters, part of CozyRoc SSIS+ library. They don't exhibit the limitation with the number of columns.

    ---
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

  • February 27, 2012 at 11:41 am

    #1452215

    CozyRoc (2/27/2012)

    Koen, I think the issue is the number of columns, not the length of a column.

    In that case, there is a solution but it is a third-party commercial solution. I would recommend you check CozyRoc Excel adapters, part of CozyRoc SSIS+ library. They don't exhibit the limitation with the number of columns.

    D'oh, you're right. Seems like I read the question a bit too fast. :blush:

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • February 27, 2012 at 2:29 pm

    #1452307

    CozyRoc (2/27/2012)

    Koen, I think the issue is the number of columns, not the length of a column.

    In that case, there is a solution but it is a third-party commercial solution. I would recommend you check CozyRoc Excel adapters, part of CozyRoc SSIS+ library. They don't exhibit the limitation with the number of columns.

    Awesome! Thanks for all your help guys, I'll take a look at the product 🙂

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply