import of excel in SQL imports 'NULL' lines

Question

I have a stored procedure that imports differently formatted workbooks into a database table. does work on them then drops the table.

Here is the populating query.

SELECT IDENTITY(INT,1,1) AS ID
INTO #test101 
FROM OPENROWSET
 ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=WorkBook.xls',[WorkSheet$])

Some spreadsheets seem to create an null row in the bottom.

How can I import and ignore the null rows?

Answer 1

• Open the spreadsheet and delete several rows below the data table.
  OR
• Use DELETE FROM #test101 WHERE myCol_1 IS NULL AND myCol_2 IS NULL
  OR
• If you want to preserve numbering (ID), load into #test_x, delete null rows and then load into your #test101.
  OR
• Use SSIS to load and get rid of the null rows during the ETL process within SSIS.