3

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.

Kobojunkie
  • 6,375
  • 31
  • 109
  • 164

2 Answers2

3

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:
excel data
when I set OpenRowset to Sheet1$a3:j third row is treated as header row:
data previev 1
but when I set OpenRowset to Sheet1$a3:j8 this row is treated as first data row:
data preview 2
Strange.

Piotr Sobiegraj
  • 1,775
  • 16
  • 26
  • When I set the OpenRowSet to Sheet1$a6:he, I get the error an error. I am not sure why but just so you know, I have about 4 sheets in my excel document that I would also like to tie to database – Kobojunkie Sep 17 '12 at 20:12
  • By setting OpenRowSet, I see i am unable to specify the Excel Sheet to map. How do I deal with that issue? – Kobojunkie Sep 17 '12 at 21:23
  • @Kobojunkie what error do you have? What's name of sheet to be read? I you've changed sheet names from default `Sheet1` to `Sheet3` you should use this name in expression, ie `SheetName$a6:he` – Piotr Sobiegraj Sep 17 '12 at 23:14
0

try this ,

Instead of using SSIS yuou can use OPENROWSET function in sql server, the below query execute step by step, because we need to set all the required options to be correct in sql server , before using OPENROWSET function, 

--execute step by step to process excel data into sqlserver using OPENROWSET/OPENDATASOURCE
sp_configure
sp_configure 'show advanced options',1
reconfigure
sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
SELECT * INTO XLImport8 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\StatusReport.xls', 'SELECT * FROM [Sheet1$]')
select * from XLImport8

Thanks,

Venkat.

Piotr Sobiegraj
  • 1,775
  • 16
  • 26
Venaikat
  • 197
  • 2
  • 5
  • 20
  • I am trying to build a windows client front end that will allow user select an excel file of a particular format and have the data from the file loaded and processed – Kobojunkie Sep 17 '12 at 20:09