import from excel to sql more than 255 columns

  • 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.

  • 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...

  • 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.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Viewing 3 posts - 1 through 2 (of 2 total)

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