CODEWITHSUNDEEP
sql-serverexcelopenrowset
Johnno Nolan
Johnno Nolan

Reputation: 29659

import of excel in SQL imports 'NULL' lines

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?

Upvotes: 1

Views: 4145

Answers (1)

Damir Sudarevic
Damir Sudarevic

Reputation: 22187

  • 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.

Upvotes: 3

Related Questions