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

问题描述

我正在将数千行 (120K) 数据从 excel 文件导入 SQL Server.现在我正在尝试使用 SSIS 来完成此操作，但考虑到构建 excel 文件的 excel 模板似乎并未仅在第一行中包含标题(大约前 6 行包含标题信息)，因此我立即遇到了一些问题.如何使用 BIDS 中的 SSIS 数据流任务处理导入来解决此问题?或者通过直接读取 Excel 文档中的每一行来读取行会更好吗?

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) 我试图找到一种替代方法来从 Windows 客户端应用程序读取每个 excel 行，然后将数据逐行写入数据库

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) 我的 excel 文档中有大约 4 个 excel 工作表

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

c) 如果有一种方法可以从 excel 中逐行读取并有效地完成它，而不是使用我不太熟悉的 Windows 客户端的 SSIS.我将不胜感激关于如何

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) 我的 excel 文档的特定标题位于第 7 行，我将其最小化，因为该信息仅用于我的后端需要.

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.

推荐答案

您可以将 Excel 数据源(属性窗口，自定义属性部分中的 OpenRowset)的 OpenRowset 属性设置为类似于 的值Sheet1$a6:j，其中 a 是数据的第一列，j 是数据的最后一列，6 通常是在数据之前带有标题的行.数据应从下一行开始.您还可以通过设置类似于 Sheet1$a6:j20 的值来设置要读取的最后一行.
请注意，第一个给定的行有时被视为标题行，有时被视为包含数据的第一行.以excel为例:

当我将 OpenRowset 设置为 Sheet1$a3:j 时，第三行被视为标题行:

但是当我将 OpenRowset 设置为 Sheet1$a3:j8 时，该行被视为第一个数据行:

奇怪.

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屋！