none
How to read more than 500 columns from xlsx file and load them into SQL table (dynamically created at time of loading ) through ssis script task?

    General discussion

  • Hi,

    I have xlsx file with multiple tab and each tab has more than 500 columns. I have to store these data into SQL table and table will be created for each tab with tab name.

    i tried to this using C# code and directly openrowset but it always returning 256 columns.

    Divrer: ACE:14  2010

    Please help me to work out this problem..

    Wednesday, July 24, 2013 3:16 PM

All replies

  • The ACE OLE DB driver has an issue when the Excel file exceeds 255 columns. So I would try another angle. Maybe try converting the Excel file to a CSV file?


    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.

    Wednesday, July 24, 2013 7:11 PM
  • CSV is much easier then Excel! If that's an option... go for it.

    It's a bit devious, but if 255 is the limit then perhaps you could read it in two steps? First Column A to IU and then IV to SP...

    SELECT * FROM [Sheet1$A:IU]
    


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Wednesday, July 24, 2013 7:33 PM
    Moderator
  • Okay, converted xlsx file to csv file, then loaded to sql table. 

    PCS

    Friday, August 16, 2013 4:41 PM