使用 SSIS 2008 将 Excel 导入 SQL 时跳过行 [英] Skipping rows when importing Excel into SQL using SSIS 2008
问题描述
我需要导入如下所示的工作表:
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
列标题实际上是第3行.我可以使用Excel Sourch导入它们，但我不知道如何指定信息从第3行开始.
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.
推荐答案
看看:
链接有更多细节，但我已经包含了页面中的一些文字(以防链接失效)
问:
当我们通过 SSIS 将文本文件加载到 SQL Server 时，我们有规定从源跳过任意数量的前导行并加载数据到 SQL 服务器.是否有任何规定可以为Excel 文件.
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.
我的源 Excel 文件在前导 5 中有一些描述行，我想跳过它并从第 6 行开始加载数据.请提供您对此的看法.
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.
答:
最简单的方法是给每一行一个数字(有点像SQL Server)，然后使用条件拆分过滤掉所有内容其中数字 <=5
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
问:
例如在从 Excel 导入数据到 DB 表时是否可以跳过前 6 行?
Is it possible during import data from Excel to DB table skip first 6 rows for example?
还有 Excel 数据，由带有标题的部分划分.例如，是否可以跳过每 12 行?
Also Excel data divided by sections with headers. Is it possible for example to skip every 12th row?
答:
是的，你可以.实际上，如果您知道将从 Excel 文件导入的数字列，则可以非常轻松地执行此操作.在您的数据流任务，您需要设置OpenRowset"；风俗Excel 连接的属性(右键单击 Excel 连接 >特性;在属性"窗口中，在自定义"下查找 OpenRowset特性).忽略 Sheet1 中的前 5 行，并导入列A-M，您将为 OpenRowset 输入以下值:Sheet1$A6:M(注意，我没有为 M 列指定行号.您可以输入一个如果您愿意，行号，但在我的情况下，行数可能会有所不同从一次迭代到下一次)
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)
再说一次，是的，你可以.您可以使用条件拆分导入数据.您将配置条件拆分以在将其唯一标识为标题行的每一行；跳过行匹配这个标题逻辑".另一种选择是导入所有行，然后使用 SQL 脚本删除标题行数据库...就像一个每 12 行删除一次的游标.或者你可以添加一个种子/增量为 1/1 的标识字段，然后删除所有行号完全除以 12 的行.像那...
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...
问:
我有一个从 Excel 文件导入数据的 SSIS 包从第 7 行开始.
I have an SSIS package that imports from an Excel file with data beginning in the 7th row.
与 csv 文件的相同操作不同('Header Rows to Skip' in连接管理器编辑器)，我似乎无法找到一种方法来忽略Excel 文件连接的前 6 行.
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.
答:
问题 登录投票 1 登录投票 rbhro，实际上有前 5 行中的 2 个字段包含一些我认为阻止的数据导入器完全忽略这些行.
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.
在我的 Excel 源对象中，我使用SQL 命令"作为数据访问"模式"(双击 Excel 源对象时它会下拉).从那里我能够构建一个查询(构建查询"按钮)只抓取我需要的记录.像这样:SELECT F4，F5, F6 FROM [Spreadsheet$] WHERE (F4 不为空) AND (F4<>'TheHeaderFieldName')
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')
注意:我最初尝试使用 ISNUMERIC 而不是IS NOT NULL"，但是出于某种原因，它不受支持.
Note: I initially tried an ISNUMERIC instead of 'IS NOT NULL', but that wasn't supported for some reason.
在我的特殊情况下，我只对 F4 不感兴趣的行NULL(幸运的是 F4 在前 5 个中没有包含任何垃圾行).我可以用第二个 WHERE 跳过整个标题行(第 6 行)子句.
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.
这样就完美地清理了我的数据源.我现在需要做的一切在源和目标之间添加了一个数据转换对象(所有需要在电子表格中从 unicode 转换的)，它奏效了.
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屋！
