Search code examples
sql-serverexcelt-sqlstored-proceduresopenrowset

When using openrowset how can I avoid inserting blank lines at the end of an excel sheet

I am using openrowset to extract values from an excel sheet. The openrowset works fine but the only problem is that it also extracts blank lines at the end of the excel sheet which then results in null values being extracted and displayed . How can tweak my openrowset query to avoid null values being extracted because I will have to adjust this query to insert into a table and I dont want these null values entered into the tables.

I read a post where adding imex=1 to the query helps to avoid blank lines but unfortunately this has not worked for me.

My current Openrowset query

SELECT *

     FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
          'Excel 12.0; Database=C:\Users\admin\Desktop\failed files\Order1.xls;Imex=1', [Sheet1$]);
Solution

  • Use a where clause in "openrowset".
    'SELECT Col1 FROM [Sheet1$] where Col1 is not null order by Col1 desc'