SSIS中的脚本任务导入excel电子表格 [英] script task in SSIS to import excel spreadsheet

问题描述

我已经回顾了可能有我的答案的问题，不幸的是他们似乎不适用。这是我的情况。我必须从我的客户端导入工作表。在列A，C，D和AA中，客户端具有我需要的信息。列的平衡对我来说是无价值的信息。列标题在我需要的四列中是一致的，但在不重要的列中是非常不一致的。例如，单元格A1包含Division。所有这些电子表格都是这样的。单元格B1可以包含任何从套筒长度到整体长度以适应。我需要做的只是导入我需要的列，并将它们映射到SQL 2008 R2表。我已经在当前调用SSIS函数的存储过程中定义了表。

问题是，当我尝试导入具有不同列名称的电子表格时，SSIS失败，我必须手动返回运行，以获取字段设置向右。

我无法想象，我以前没有做过什么。只有这样的大小不会丢失，我有170个用户拥有超过120个不同的电子表格模板。

我绝望的一个可行的解决方案。在SQL中将文件放入我的表后，我可以做所有事情。我甚至写了代码将文件移回FTP服务器。

解决方案

我把一篇文章描述为我使用脚本任务来解析Excel 。它允许我将非表格数据导入数据流。

核心概念是您将使用JET或ACE提供程序，只需从Excel Worksheet /命名范围查询数据。一旦你有了这个，你可以有一个数据集，你可以逐行走过，执行你需要的任何逻辑。在您的情况下，您可以跳过标题的第1行，然后仅导入列A，C，D和AA。

该逻辑将在ExcelParser类中。所以，第71行的Foreach循环可能会被简化为（代码近似）。

  //这样获取值的列A 
 current = dr [0] .ToString（）; 
 //将current的值分配到第0列的输出行
 newRow [0] = current; 
 
 //获取列C 
 current = dr [2] .ToString（）; 
 //将电流值分配到第1列的输出行
 newRow [1] = current; 
 
 //获取列D 
 current = dr [3] .ToString（）; 
 //这将current的值分配到第2列的输出行
 newRow [2] = current; 
 
 //获取列AA的值
 current = dr [26] .ToString（）; 
 //将current的值分配到第3列的输出行
 newRow [3] = current; 
  

你显然可能需要进行类型转换，这在这里是解析逻辑的核心。 p>

I have reviewed the questions that may have had my answer and unfortunately they don't seem to apply. Here is my situation. I have to import worksheets from my client. In columns A, C, D, and AA the client has the information I need. The balance of the columns have what to me is worthless information. The column headers are consistent in the four columns I need, but are very inconsistent in the columns that don't matter. For example cell A1 contains Division. This is true across all of the spreadsheets. Cell B1 can contain anything from sleeve length to overall length to fit. What I need to do is to import only the columns I need and map them to an SQL 2008 R2 table. I have defined the table in a stored procedure which is currently calling an SSIS function.

The problem is that when I try to import a spreadsheet that has different column names the SSIS fails and I have to go back in an run it manually to get the fields set up right.

I cannot imagine that what I am trying to do has not been done before. Just so the magnitude is not lost, I have 170 users who have over 120 different spreadsheet templates.

I am desperate for a workable solution. I can do everything after getting the file into my table in SQL. I have even written the code to move the files back to the FTP server.

解决方案

I put together a post describing how I've used a Script task to parse Excel. It's allowe me to import decidedly non-tabular data into a data flow.

The core concept is that you will use a the JET or ACE provider and simply query the data out of an Excel Worksheet/named range. Once you have that, you have a dataset you can walk through row-by-row and perform whatever logic you need. In your case, you can skip row 1 for the header and then only import columns A, C, D and AA.

That logic would go in the ExcelParser class. So, the Foreach loop on line 71 would probably be distilled down to something like (code approximate)

// This gets the value of column A
current = dr[0].ToString();
// this assigns the value of current into our output row at column 0
newRow[0] = current;

// This gets the value of column C
current = dr[2].ToString();
// this assigns the value of current into our output row at column 1
newRow[1] = current;

// This gets the value of column D
current = dr[3].ToString();
// this assigns the value of current into our output row at column 2
newRow[2] = current;

// This gets the value of column AA
current = dr[26].ToString();
// this assigns the value of current into our output row at column 3
newRow[3] = current;

You obviously might need to do type conversions and such here but that's core of the parsing logic.

这篇关于SSIS中的脚本任务导入excel电子表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！