import from excel to sql more than 255 columns

• dallas13

  SSCertifiable

  Points: 7491

  More actions

  September 26, 2016 at 10:13 am

  #313461

  Hello,

  I have so many excel files coming that I need to load to sql and create automated process.

  I dont want to convert them to csv or txt file format and then load to sql. I also dont want to use macros in excel.

  I found this post,

  https://waheedrous.wordpress.com/2014/01/14/ssis-importing-an-excel-file-with-over-255-columns/

  But I m not more into .NET\VB side so instead of script component, can I use some T-sql or sql component to add identity column?

  Please let me know if thats possible.

  Thanks.

• pietlinden

  SSC Guru

  Points: 63503

  More actions

  September 26, 2016 at 10:25 am

  #1903132

  Why not use a For Each file loop and then use an Excel connector and insert that way?

  You probably need some serious normalization of the data you're importing. 255 columns sounds seriously in need of some restructuring.

  FWIW, I have done this kind of restructuring by connecting to the spreadsheets from Access and then using DAO to unpivot the data, and then append to a table in SQL Server.... but I guess you could use an unpivot transformation inside SSSIS...

• Phil Parkin

  SSC Guru

  Points: 246961

  More actions

  September 26, 2016 at 10:30 am

  #1903138

  dallas13 (9/26/2016)

  ---

  Hello,

  I have so many excel files coming that I need to load to sql and create automated process.

  I dont want to convert them to csv or txt file format and then load to sql. I also dont want to use macros in excel.

  I found this post,

  https://waheedrous.wordpress.com/2014/01/14/ssis-importing-an-excel-file-with-over-255-columns/

  But I m not more into .NET\VB side so instead of script component, can I use some T-sql or sql component to add identity column?

  Please let me know if thats possible.

  Thanks.

  Two rows of C# scripting should not be beyond even a novice coder, especially when the code is there for you to copy directly!

  I have not had to import an Excel worksheet with more than 255 columns (thank the heavens!), so I cannot comment on that part.

  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