SSIS Excel 导入(列名更改) [英] SSIS Excel Import (Column Names Changing)
问题描述
我有一个类似于以下格式的 Excel 文件:
我将首先解释 SSIS 元素的下一步,因为列名称并不重要",因为我正在取消数据流中的数据以开始使用它:
问题是,文件将被更新 - 年和季度将被删除(历史),添加新的以取代旧的.众所周知,这意味着数据流上的元数据已损坏.
单元格范围和位置等将始终保持不变.
有没有办法在列名 (2016q1) 具有流动性的数据流中处理它?</p>
谢谢
你会喜欢这个的,因为它也可以做枢轴:
使用 C# Script 组件源码:
添加命名空间:使用 System.Data.OleDb;
添加您的 4 个输出列并选择数据类型:
向新行部分添加代码.
public override void CreateNewOutputRows(){/*通过对名为
I have an Excel file that loosely resembles the following format:
I'll explain the next step of the SSIS element first as the column names are not "important" as I am un-pivoting the data in a data flow to start getting it usable:
The issue is, the file will be updated - years and quarters will be removed (historical), new ones added to replace the old ones. That means, as we all know, the metadata on a data flow is broken.
The cell range and position etc. will always remain the same.
Is there a way it can be handled in a data flow with the column names (2016q1) being fluid?
Thanks
You're going to like this as it also does the pivot:
Using C# Script component source:
Add namespace: Using System.Data.OleDb;
Add your 4 output columns and select data types:
Add code to new row section.
public override void CreateNewOutputRows()
{
/*
Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer".
For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput".
*/
string fileName = @"C:\test.xlsx";
string SheetName = "Sheet1";
string cstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";
OleDbConnection xlConn = new OleDbConnection(cstr);
xlConn.Open();
OleDbCommand xlCmd = xlConn.CreateCommand();
xlCmd.CommandText = "Select * from [" + SheetName + "$]";
xlCmd.CommandType = CommandType.Text;
OleDbDataReader rdr = xlCmd.ExecuteReader();
//int rowCt = 0; //Counter
while (rdr.Read())
{
for (int i = 2; i < rdr.FieldCount; i++) //loop from 3 column to last
{
Output0Buffer.AddRow();
Output0Buffer.ColA = rdr[0].ToString();
Output0Buffer.ColB = rdr[1].ToString();
Output0Buffer.FactName = rdr.GetName(i);
Output0Buffer.FactValue = rdr.GetDouble(i);
}
//rowCt++; //increment counter
}
xlConn.Close();
}
这篇关于SSIS Excel 导入(列名更改)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!