excel - 使用 SSIS 2008 将 Excel 导入 SQL 时跳过行

Question

我需要导入如下所示的工作表:

    March Orders   
   ***Empty Row    
    Week Order # Date Cust #
    3.1 271356 3/3/10 010572
    3.1 280353 3/5/10 022114
    3.1 290822 3/5/10 010275
    3.1 291436 3/2/10 010155
    3.1 291627 3/5/10 011840

列标题实际上是第 3 行。我可以使用 Excel Sourch 导入它们，但我不知道如何指定信息从第 3 行开始。

我用谷歌搜索了这个问题，但一无所获。

Answer 1

看看:

链接有更多详细信息，但我包含了页面中的一些文本(以防万一链接失效)

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/97144bb2-9bb9-4cb8-b069-45c29690dfeb

Q:

While we are loading the text file to SQL Server via SSIS, we have theprovision to skip any number of leading rows from the source and loadthe data to SQL server. Is there any provision to do the same forExcel file.

The source Excel file for me has some description in the leading 5rows, I want to skip it and start the data load from the row 6. Pleaseprovide your thoughts on this.

A:

Easiest would be to give each row a number (a bit like an identity inSQL Server) and then use a conditional split to filter out everythingwhere the number <=5

http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/947fa27e-e31f-4108-a889-18acebce9217

Q:

1. Is it possible during import data from Excel to DB table skip first 6 rows for example?

2. Also Excel data divided by sections with headers. Is it possible for example to skip every 12th row?

A:

1. YES YOU CAN. Actually, you can do this very easily if you know the number columns that will be imported from your Excel file. Inyour Data Flow task, you will need to set the "OpenRowset" CustomProperty of your Excel Connection (right-click your Excel connection >Properties; in the Properties window, look for OpenRowset under CustomProperties). To ignore the first 5 rows in Sheet1, and import columnsA-M, you would enter the following value for OpenRowset: Sheet1$A6:M(notice, I did not specify a row number for column M. You can enter arow number if you like, but in my case the number of rows can varyfrom one iteration to the next)

2. AGAIN, YES YOU CAN. You can import the data using a conditional split. You'd configure the conditional split to look for something ineach row that uniquely identifies it as a header row; skip the rowsthat match this 'header logic'. Another option would be to import allthe rows and then remove the header rows using a SQL script in thedatabase...like a cursor that deletes every 12th row. Or you couldadd an identity field with seed/increment of 1/1 and then delete allrows with row numbers that divide perfectly by 12. Something likethat...

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/847c4b9e-b2d7-4cdf-a193-e4ce14986ee2

Q:

I have an SSIS package that imports from an Excel file with databeginning in the 7th row.

Unlike the same operation with a csv file ('Header Rows to Skip' inConnection Manager Editor), I can't seem to find a way to ignore thefirst 6 rows of an Excel file connection.

I'm guessing the answer might be in one of the Data FlowTransformation objects, but I'm not very familiar with them.

A:

Question Sign in to vote 1 Sign in to vote rbhro, actually there were2 fields in the upper 5 rows that had some data that I think preventedthe importer from ignoring those rows completely.

Anyway, I did find a solution to my problem.

In my Excel source object, I used 'SQL Command' as the 'Data AccessMode' (it's drop down when you double-click the Excel Source object).From there I was able to build a query ('Build Query' button) thatonly grabbed records I needed. Something like this: SELECT F4,F5, F6 FROM [Spreadsheet$] WHERE (F4 IS NOT NULL) AND (F4<> 'TheHeaderFieldName')

Note: I initially tried an ISNUMERIC instead of 'IS NOT NULL', butthat wasn't supported for some reason.

In my particular case, I was only interested in rows where F4 wasn'tNULL (and fortunately F4 didn't containing any junk in the first 5rows). I could skip the whole header row (row 6) with the 2nd WHEREclause.

So that cleaned up my data source perfectly. All I needed to do nowwas add a Data Conversion object in between the source and destination(everything needed to be converted from unicode in the spreadsheet),and it worked.