使用SSIS 2008将Excel导入SQL时跳过行 [英] Skipping rows when importing Excel into SQL using SSIS 2008
问题描述
我需要导入如下所示的工作表：
3月订单
***空行
周订单＃日期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行开始。
我Googled这个问题，但是空出来了。
看看：
更多细节，但我已经从页面中添加了一些文本（以防万一链接死了）
问：
当我们通过SSIS将文本文件加载到SQL Server时，我们有
设置可以从源中跳过任何数量的前导行，并加载
数据到SQL服务器。对于
Excel文件是否有任何规定。
我的源Excel文件在前导的5
行中有一些描述，我想跳过它并从第6行开始数据加载。请
提供你的想法。
A：
最简单的是给每行一个数字（有点像
SQL Server中的一个身份），然后使用条件拆分来过滤掉所有的
其中数字< = 5
问：
在从Excel到DB表的导入数据期间可能跳过前6行吗？
Excel数据除以标题的部分。例如，可以跳过第12行吗？
A：
是的，你可以其实，如果你知道从Excel文件导入的数字列，你可以很容易地做到这一点。在
您的数据流任务中，您将需要设置您的Excel连接的OpenRowset自定义
属性（右键单击您的Excel连接
属性;在属性窗口中查找在Custom
属性下的OpenRowset）。要忽略Sheet1中的前5行，并导入列
AM，您将为OpenRowset输入以下值：Sheet1 $ A6：M
（注意，我没有为列M指定行号。如果你喜欢，你可以输入
行号，但是在我的情况下，行数可以从一个迭代到下一个不同的
）
- 再次，是的，你可以您可以使用条件拆分导入数据。您将配置条件拆分，以在
中的每个行中唯一标识它作为标题行的东西;跳过匹配这个标题逻辑的行
。另一个选项是导入所有
行，然后使用
数据库中的SQL脚本删除标题行，如删除每第12行的游标。或者你可以
添加一个标识字段，种子/增量为1/1，然后删除所有
行，行号完全除以12.像
那样...
Q：
我有一个SSIS包，从Excel文件导入，数据
从第七行开始。
与同一操作csv文件（$ Header Rows to Skip'in
Connection Manager Editor），我似乎找不到一种忽略
前6行Excel文件连接的方法。
我猜想答案可能在其中一个数据流
转换对象，但我不太熟悉他们。
A：
问题登录进行投票1登录投票rbhro，实际上在上面的5行中有
2个字段，其中有一些数据，我认为阻止
进口商完全忽略这些行。
无论如何，我找到了解决问题的方法。
在我的Excel源对象中，我使用SQL命令作为数据访问
模式（当您双击Excel源对象时，它将被下拉）。
从那里我能够构建一个查询（'构建查询'按钮），
只能抓取我需要的记录。这样做：SELECT F4，
F5，F6 FROM [Spreadsheet $] WHERE（F4 IS NOT NULL）AND（F4
<> TheHeaderFieldName'）
注意：我最初尝试使用ISNUMERIC而不是IS NOT NULL，但
由于某种原因不被支持。
我特定的情况下，我只对F4不是
NULL（幸运的是，F4在前5
行中没有任何垃圾）感兴趣。我可以用第二个WHERE
子句跳过整个标题行（第6行）。
这样清理了我的数据源。所有我现在需要做的
在源和目标
之间添加一个数据转换对象（所有需要从电子表格中的unicode转换），
和它的工作。 >
I need to import sheets which look like the following:
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
The column headers are actually row 3. I can use an Excel Sourch to import them, but I don't know how to specify that the information starts at row 3.
I Googled the problem, but came up empty.
have a look:
the links have more details, but I've included some text from the pages (just in case the links go dead)
Q:
While we are loading the text file to SQL Server via SSIS, we have the provision to skip any number of leading rows from the source and load the data to SQL server. Is there any provision to do the same for Excel file.
The source Excel file for me has some description in the leading 5 rows, I want to skip it and start the data load from the row 6. Please provide your thoughts on this.
A:
Easiest would be to give each row a number (a bit like an identity in SQL Server) and then use a conditional split to filter out everything where the number <=5
Q:
Is it possible during import data from Excel to DB table skip first 6 rows for example?
Also Excel data divided by sections with headers. Is it possible for example to skip every 12th row?
A:
YES YOU CAN. Actually, you can do this very easily if you know the number columns that will be imported from your Excel file. In your Data Flow task, you will need to set the "OpenRowset" Custom Property of your Excel Connection (right-click your Excel connection > Properties; in the Properties window, look for OpenRowset under Custom Properties). To ignore the first 5 rows in Sheet1, and import columns A-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 a row number if you like, but in my case the number of rows can vary from one iteration to the next)
AGAIN, YES YOU CAN. You can import the data using a conditional split. You'd configure the conditional split to look for something in each row that uniquely identifies it as a header row; skip the rows that match this 'header logic'. Another option would be to import all the rows and then remove the header rows using a SQL script in the database...like a cursor that deletes every 12th row. Or you could add an identity field with seed/increment of 1/1 and then delete all rows with row numbers that divide perfectly by 12. Something like that...
Q:
I have an SSIS package that imports from an Excel file with data beginning in the 7th row.
Unlike the same operation with a csv file ('Header Rows to Skip' in Connection Manager Editor), I can't seem to find a way to ignore the first 6 rows of an Excel file connection.
I'm guessing the answer might be in one of the Data Flow Transformation objects, but I'm not very familiar with them.
A:
Question Sign in to vote 1 Sign in to vote rbhro, actually there were 2 fields in the upper 5 rows that had some data that I think prevented the 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 Access Mode' (it's drop down when you double-click the Excel Source object). From there I was able to build a query ('Build Query' button) that only 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', but that wasn't supported for some reason.
In my particular case, I was only interested in rows where F4 wasn't NULL (and fortunately F4 didn't containing any junk in the first 5 rows). I could skip the whole header row (row 6) with the 2nd WHERE clause.
So that cleaned up my data source perfectly. All I needed to do now was add a Data Conversion object in between the source and destination (everything needed to be converted from unicode in the spreadsheet), and it worked.
这篇关于使用SSIS 2008将Excel导入SQL时跳过行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
