How to skip first lines of Excel file during SSIS import ? MS BI

How to import a table from Excel and skip the first lines with SSIS?

The initial way to import data and skip the first lines of an Excel file with SSIS is by using the default settings while connecting to one of the Excel spreadsheet’s sheets. Another method involves writing a SQL query with a special syntax for Excel. This query allows selecting specific cell ranges. With the default settings, the SSIS Excel Source component loads all the lines from the file, or at least from the Excel area containing data, like a table or cell range.

Here’s how to select or skip the first lines of an Excel spreadsheet imported with SSIS, such as headers or empty lines. Simply copy and modify the following queries to your specific needs. These SQL queries load all lines with content from the 3rd row of the Excel file to be loaded, starting from column A and up to column D.

Read a Range of Excel Cells with SSIS

The queries below allow skipping the first two lines of the Excel file. Simply provide a SQL query as below that uses the OPENROWSET method. This specifies the file path directly in the SQL code. This method, for instance, works with old files in XLS format, that is, Excel 1997-2003 format.

Indeed, this Excel format has many limitations, such as a maximum of 65,536 rows and 256 columns, so it hasn’t been recommended for years. The OPENROWSET method allows connecting and reading data from files or remote databases with OLE DB.

SELECT *
FROM OPENROWSET(
   'Microsoft.Jet.OLEDB.4.0',
   'Excel 8.0;Database=C:\Temp\Test2.xls;HDR=yes',
   'SELECT * FROM [Sheet1$A3:D]'
)

Example of SQL query to skip lines of Excel files with SSIS

Secondly, this simple query directly selects the Excel tab and cells. It’s necessary to set up the Excel source pointing to the Excel file to import into the database beforehand.

SELECT *
FROM [Sheet1$A3:D]

Another example explains how import the data starting at the 3rd line instead of the first line of the Excel sheet, it will stop at the line 13.

SELECT *
FROM [Sheet1$A3:C13]

Another one will select data from cell A3 to E31, like in the bigger area of the screenshot below.

SELECT *
FROM [Sheet1$A3:E31]

*Skip lines of Excel files with SSIS to import a specific data zone*

SSIS SQL query with up-to-date driver version

More updated versions of SSIS may use the ACE OLE DB provider instead of the Jet OLE DB provider to handle both XLS (Excel 1997-2003) and XLSX (Excel 2007 and later) files. This query works similarly to the previous ones but can manage newer Excel file formats with a newer driver. Other are available, like the ODBC driver.

SELECT *
FROM OPENROWSET(
   'Microsoft.ACE.OLEDB.12.0',
   'Excel 12.0;Database=C:\Temp\Test2.xlsx;HDR=yes',
   'SELECT * FROM [Sheet1$A3:D]'
)

This brief tutorial explains how to easily skip the first lines of an Excel file during an import with SSIS. Also, here’s how to perform the reverse operation, that is, export data from an SQL Server table to an Excel file with SSIS.