开发者

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;Databa开发者_JS百科se=WorkBook.xls',[WorkSheet$])

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

How can I import and ignore the null rows?


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

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜