First published on MSDN on Jan 21, 2011
Guys, I was really excited to find out that Excel 2010 overcomes the limitation of having a maximum number of 255 columns in a worksheet. Personally I always wanted Excel to get around this column limitation while storing data. However, I wanted to share my experience about the little trick we need to do in order to import the data spilling over the 255
th
from the Excel file to Sql Server using any of the available tools like SSIS, Import/Export Wizard etc.
I discovered that whenever I try to import the Data into Sql Server, all the columns after the 255
th
one are totally ignored as they were never there and this in fact seemed to be still a limitation of the underlined provider that is used by tools like SSIS or the Import/Export Wizard. Yes, I made sure that I tried out both the JET as well as the ACE* provider (available for download at
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c06b8369-60dd-4b64-a44b-84b371ede16...
) with the exact same results. The fact that Excel now supports more than 255 columns does not make much sense if there isn’t a way to extract that data, so that obvious question now is: “What can I do to extract the data that spills over the 255
th
column”?
Well, after spending some time on further research, I actually found out a way. We can define Named Cell ranges in Excel and this feature came to my rescue. What we need to do in such a scenario is define a couple of named cell ranges, one range for Column 1 to 255 while another one from column 255 to the actual number of columns the spreadsheet has. Doing this the provider will detect both the cell ranges as two different Data Sets and show up in the Import/Export Wizard or SSIS. Still, there would be some work after that to merge these two datasets to a single destination or if feasible, re-direct them as it is in two different destinations.
I will conclude my post with a consolidated Step by step instructions on how to address this issue:
1. Open the Excel file
2. Select the Formulas Tab > Choose Define Name
3. In the New Name Dialog provide the following information:
Name: Columns1To255
Scope: Workbook
Refers to: =Sheet1!$A1:$IU1000
The 1000 in the range assumes you have 1000 rows of data, you will want to set this to the correct row number for your file.
4. Click Define Name again
5. In the New Name Dialog provide the following information:
Name: Columns256To500
Scope: Workbook
Refers to: =Sheet1!$IV1:$SF1000
The 1000 in the range assumes you have 1000 rows of data, you will want to set this to the correct row number for your file.
6. Save/Close the Excel File
7. Complete the SQL Server Import Wizard. When going through the wizard, the named ranges will show up as available tables and views. Select the named ranges and complete the import.
Note: Please make sure to Save and Close the Excel File after the changes and prior to running the Import/Export Wizard, unless the changes won’t take effect and you will not be able to do the import
* ACE provider may or may not be supported with SSIS, depending on the context.
Author : Debarchan(MSFT), SQL Developer Engineer, Microsoft
Reviewed by : Jason(MSFT), SQL Escalation Services, Microsoft
