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.
精彩评论