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

Question

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

guyb 35072

Old Hand

Points: 388
More actions
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://www.sqlservercentral.com/articles/Excel/64838/) it seems possible 🙂
Thanks in advance

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

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

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

Phil Parkin SSC Guru Points: 246961 More actions · Answer 2

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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

guyb 35072 Old Hand Points: 388 More actions · Answer 3

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 4

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

guyb 35072 Old Hand Points: 388 More actions · Answer 5

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

Cheers for the information anyway 🙂

COZYROC One Orange Chip Points: 28499 More actions · Answer 6

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/

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 7

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

guyb 35072 Old Hand Points: 388 More actions · Answer 8

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 🙂