SSIS Excel到SQL导入 - 文件的前6行包含标题相关信息 [英] SSIS Excel to SQL import -- First 6 rows of the file contains header-related information

问题描述

我正在从Excel文件导入数千行数据（120K）到SQL Server中。现在我试图使用SSIS完成这个，但是我立即遇到了一些问题，考虑到excel文件构建的excel模板似乎不包含Header在第一行（大约前6行包含头信息） 。如何使用BIDS中的SSIS数据流任务来解决此问题？或者直接阅读Excel文档中的每一行最好阅读这些行？

我认为这里有用的其他信息是

a）我试图找到一个替代方法从Windows客户端应用程序读取每个excel行，然后将数据逐行写入数据库

b）我的excel文档中有大约4个excel工作表

c）如果有一种方法从excel逐行阅读并有效地完成，而不是使用我不熟悉的Windows客户端的SSIS。我会感谢任何关于如何

的建议d）我的excel文档的特定标题位于第7行，我已经将其最小化，只是为了我的后端需要

解决方案

您可以设置Excel数据源（属性窗口）的 OpenRowset 属性，自定义属性部分中的OpenRowset）值类似于 Sheet1 $ a6：j ，其中 a 是您的第一列数据， j 是最后一列的数据， 6 通常是数据前的行头。数据应该从下一行开始。您还可以通过设置值类似于来设置最后一行。

请注意，首先给定的行有时被视为标题行和有时作为第一行与数据。例如使用excel：



当我设置 OpenRowset to Sheet1 $ a3：j 第三行被视为标题行：



但是当我设置 OpenRowset to Sheet1 $ a3：j8 此行被视为第一个数据行：



奇怪。

I am working on importing thousands of rows(120K) of data from an excel file into a SQL Server. Now I am trying to use SSIS to accomplish this but I immediately ran into some problem considering the excel template that the excel file is built with does not seem to contain the Header in just the first row(about the first 6 rows contain header information). How do I solve for this problem using the SSIS Data flow task in BIDS to handle the imports? Or would it be better to read the lines via direct read of each row from the Excel document?

Other information that I think will be helpful here is

a) I am trying to find an alternative to reading each excel row from a windows client application, and then writing the data to the database line by line

b) I have about 4 excel worksheets in my excel document

c) If there is a way to read line by line from excel and have it done efficiently, rather than use SSIS from Windows client which I am not that familiar with. I will appreciate any suggestions as to how

d) the particular header of my excel document resides on line 7, and I have it minimized sine the information is only to be for my backend need.

解决方案

You can set OpenRowset property of Excel Data Source (Properties window, OpenRowset in Custom Properties section) to value similar to Sheet1$a6:j, where a is first column with your data, j is last column with data and 6 is usually row with header just before data. Data should start in next row. You can also set last row to be read by setting value similar to Sheet1$a6:j20.
Note that first given row is sometimes treated as header row and sometimes as first row with data. For example with excel:

when I set OpenRowset to Sheet1$a3:j third row is treated as header row:

but when I set OpenRowset to Sheet1$a3:j8 this row is treated as first data row:

Strange.

这篇关于SSIS Excel到SQL导入 - 文件的前6行包含标题相关信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！