Reading 500 columns in Excel sheet using SSIS

Question

Hi,

I have to transform 500 columns from an excel sheet to Sql Server. In Excel 2k3 , I can read a max of 256 columns only.

If I use Excel 2k7, then SSIS 2k5 excel source does not support excel 2k7. If I use ole db source then again it can read a max of 256 columns.

Could anyone please suggest me how can we read 500 columns in excel sheet (Around 10000 rows) efficiently using SSIS 2k5.   

Raj 

Answer 1

Preconvert them to csv with a VB script, then try loading as csv files. That way you can also use the 64bit runtime.

---

My Blog "Karl Beran's BI Mumble"

Answer 2

Preconvert them to csv with a VB script, then try loading as csv files.

---

My Blog "Karl Beran's BI Mumble"

Agreed. Altho you don't even need to write any VB - any Excel file can be saved as a CSV (comma-seperated value) file.

Importing .CSV files using SSIS is much easier than .xls files.

---

http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

Answer 3

Thanks Karl and Jamie. I'll try this approach and let you know. Raj

Answer 4

Hi,

I have to transform 500 columns from an excel sheet to Sql Server. In Excel 2k3 , I can read a max of 256 columns only.

If I use Excel 2k7, then SSIS 2k5 excel source does not support excel 2k7. If I use ole db source then again it can read a max of 256 columns.

Could anyone please suggest me how can we read 500 columns in excel sheet (Around 10000 rows) efficiently using SSIS 2k5.   

Raj 

If you can use third-party solutions, check the commercial CozyRoc Excel Source Plus component. It supports Excel 97-2010 and doesn't have 256 columns limit.

---

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

Answer 5

Hi Raj,

We can install the Microsoft Access Database Engine 2010, and then use the Microsft Office Acess Database Engine OLE DB provider in SQL Server Integration Services(SSIS) to restrieve data from Excel 2007.
This don't have the 256 columns limited.

Microsoft Access Database Engine 2010 can be downloaded from:
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c06b8369-60dd-4b64-a44b-84b371ede16d

If you have any more questions, please feel free to ask.

Thanks,
Jin Chen 

---

Jin Chen - MSFT

Answer 6

Jin Chen:

I followed your instructions and I still cannnot import more than 256 columns from Excel 2010. I'm running Windows 2008 R2 with MS SQL Server 2008 R2.

What am I missing?

Thanks,

Matt

Answer 7

So once this is installed, how do you make the connections to perform the import?  I do not see it in the list of available connection types.   Are you referring to using a script task to facilitate loading this?

---

--You bet I ate it--

Answer 8

still am facing same 255 columns limited error

Answer 9

Hi,

Can you try  saving that  file  as Tab delimited  text   file and  try  importing?