SSIS Excel to SQL import -- First 6 rows of the file contains header-related information
I am working on importing thousands of rows(120K) of data from an excel file into a SQL Server. Now I am trying to use SSIS to accomplish this but I immediately ran into some problem considering the excel template that the excel file is built with does not seem to contain the Header in just the first row(about the first 6 rows contain header information). How do I solve for this problem using the SSIS Data flow task in BIDS to handle the imports? Or would it be better to read the lines via direct read of each row from the Excel document?
Other information that I think will be helpful here is
a) I am trying to find an alternative to reading each excel row from a windows client application, and then writing the data to the database line by line
b) I have about 4 excel worksheets in my excel document
c) If there is a way to read line by line from excel and have it done efficiently, rather than use SSIS from Windows client which I am not that familiar with. I will appreciate any suggestions as to how
d) the particular header of my excel document resides on line 7, and I have it minimized sine the information is only to be for my backend need.
Answer
You can set OpenRowset property of Excel Data Source (Properties window, OpenRowset in Custom Properties section) to value similar to Sheet1$a6:j, where a is first column with your data, j is last column with data and 6 is usually row with header just before data. Data should start in next row. You can also set last row to be read by setting value similar to Sheet1$a6:j20.
Note that first given row is sometimes treated as header row and sometimes as first row with data. For example with excel:
when I set OpenRowset to Sheet1$a3:j third row is treated as header row:
but when I set OpenRowset to Sheet1$a3:j8 this row is treated as first data row:
Strange.