SSIS package to query an Excel data source, ignore first 10 rows.

Posted on 2010-04-07
Medium Priority
Last Modified: 2013-11-10
I have this requirement to pull data from an excel spreadsheet and perform some transformationms and  convert it to a csv file. Now the problem is The excel file has the useful data starting from some 7th row and all the initial rows have some information and metadata.
I need to query the excel spread sheet.
I have tried this query,
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                'Excel 8.0;Database=C:\Documents and Settings\ \Myfile0282010.xls;IMEX=1',
                'SELECT * FROM [Sheet2$]')

This query doesnt work. I get an error which says " sysntax error in the FROM clause"
It gives me an error even when I try it on a sheet with data starting from the second row, and column names on the first row.  
Please help. I use a data flow task and use an excel connection manager and use the option, "Sql command" on the data access mode
Appreciate your response
Question by:sriscan
LVL 30

Expert Comment

by:Reza Rad
ID: 30053365
could you upload your excel file here?

Author Comment

ID: 30056023
I guess I dont need to use "openrowset" at all when I use Excel source connection manager
I tried this query and its working great
SELECT    F3,F18
FROM    [FAC_Per Risk$B8:S50]

but now the problem is I must automate the package and each month I have different nmber of records. I would know the starting position i.e. here B8 which is fixed every month but the location S50 is not constant i.e. next month I could have something like 80 rows. and then at the end I have one row empty completely and then I have subtotals and some comments which I should not be including in the ETL. please help how I could grab the data only until the empty row at the end.

Assisted Solution

da-zero earned 1000 total points
ID: 30099342
I would try to read everything in the sheet as text, and then filter out unwanted rows with the conditional split.
LVL 16

Accepted Solution

carsRST earned 1000 total points
ID: 31384575
1.  Create  a new  table that matches the columns in your Excel sheet (take note on the data types)
2.  In your SSIS package, iImport the Excel workbook in to this new table
3.  And then query from the table as opposed to the worksheet.  

In your SSSIS package, before you import, clear out the table, so as to not have data from the previous run.

While it is possible to query a worksheet, it's not really a good idea.


Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

599 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question