none
Excel file load in ssis table RRS feed

  • Question

  • Hello Everybody,

    I have excel file with around 300 fields in it. I do want to load it in the database table using any easiest way

    I am using Office 2010 and SQL SERVER 2008 R2.

    Please suggest me.

    Thanks


    Friday, December 13, 2013 5:04 PM

All replies

  • Do you mean 300 columns or rows.

    If columns then probably ssis

    If rows and not so many columns then I would create insert statements in the spreadsheet using an expression and copy it to management studio.

    Friday, December 13, 2013 5:08 PM
  • I have 300 columns in the file. I have to load around 200 files (same format).

    Thanks

    Friday, December 13, 2013 5:12 PM
  • use a ForEachLoop container in SSIS which will point to your location containing Excel file. Inside loop you'll have a Data Flow Task with EXcel source and OLEDB Destination to move excel data to table. You 200 files should have same structure (metadata) for using inside SSIS ie number of column as well as corresponding columns should be same.

    see this as an example

    http://www.sqlis.com/sqlis/post/Looping-over-files-with-the-Foreach-Loop.aspx

    http://visakhm.blogspot.in/2012/05/package-to-implement-daily-processing.html


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Proposed as answer by Harry Bal Friday, December 13, 2013 7:00 PM
    Friday, December 13, 2013 5:26 PM
  • Yes all my files are having the same structure. I just tried to load  one sample file using SSIS but I see that max, I can load is 255 column but I have 340 columns in my file.

    How to resolve this ?

    Thanks

    Friday, December 13, 2013 5:31 PM
  • Split file into two based on your key column. ie have two excel sources with key columns + half of other columns ( say Col1,col2 key +col3...col170 for first source, then Col1,col2 key +col171...col340 for second). merge them using a merge join transform and then do your insert.

    Another way is to first insert first half to the table  you create at destination with 340 columns. Then as next data flow bring in next half columns and use update statement to add them to take by matching on key column


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Friday, December 13, 2013 5:36 PM
  • Thanks for the reply but I see only first 255 columns in excel connection. I am not even seeing all the columns.

    Thanks

    Friday, December 13, 2013 5:42 PM
  • Can you share a sample excel with atleast 1 row of data. I'll try to put together a sample package to you


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Friday, December 13, 2013 5:50 PM
  • My excel file is very simple. First column is ID and then col1 to col339 with some text data in it. Not more than 10 characters in each column.

    I am using Office 2010 and MS SQL SERVER 2008 R2.

    Thanks

    Friday, December 13, 2013 7:01 PM
  • Hi SSILeaner,

    The maximum number of columns is limited to 255 when we importing an .xls format Excel 2003 file. This limitation doesn’t exist when we deal with .xlsx format Exce file. For an .xls file, we usually save it as a .csv file or save it as a .xlsx file through Office 2010 and then do the ETL. Based on your description, the 200 files are all .xls files. In this condition, I don’t think there is an easy way to load the data to the database table. Maybe you can try some third party SSIS component/task.

    Here is a reference in which Pat Phelan did a good summary regarding this topic:
    http://www.dbforums.com/microsoft-sql-server/1658530-ssis-excel-more-400-columns.html

    Regards,
    Mike Yin

    If you have any feedback on our support, please click here


    Mike Yin
    TechNet Community Support

    Tuesday, December 17, 2013 11:48 AM
    Moderator
  • Thanks Mike.

    Actually I do have xlsx file extension as my files are in Office 2010. Also I am using SS 2008 R2.

    Is there any setting in ssis package which is preventing fields over 255 not appearing.

    Thanks

    Tuesday, December 17, 2013 6:05 PM