SSIS Excel 导入(列名更改) [英] SSIS Excel Import (Column Names Changing)

问题描述

我有一个类似于以下格式的 Excel 文件:

我将首先解释 SSIS 元素的下一步，因为列名称并不重要"，因为我正在取消数据流中的数据以开始使用它:

问题是，文件将被更新 - 年和季度将被删除(历史)，添加新的以取代旧的.众所周知，这意味着数据流上的元数据已损坏.

单元格范围和位置等将始终保持不变.

有没有办法在列名 (2016q1) 具有流动性的数据流中处理它?<​​/p>

谢谢

解决方案

你会喜欢这个的，因为它也可以做枢轴:

使用 C# Script 组件源码:

添加命名空间:使用 System.Data.OleDb;

添加您的 4 个输出列并选择数据类型:

向新行部分添加代码.

public override void CreateNewOutputRows(){/*通过对名为
Buffer"的成员变量调用 AddRow 方法来添加行.例如，如果您的输出名为MyOutput"，则调用 MyOutputBuffer.AddRow().*/string fileName = @"C:\test.xlsx";字符串 SheetName = "Sheet1";string cstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";OleDbConnection xlConn = 新 OleDbConnection(cstr);xlConn.Open();OleDbCommand xlCmd = xlConn.CreateCommand();xlCmd.CommandText = "Select * from [" + SheetName + "$]";xlCmd.CommandType = CommandType.Text;OleDbDataReader rdr = xlCmd.ExecuteReader();//int rowCt = 0;//柜台而 (rdr.Read()){for (int i = 2; i < rdr.FieldCount; i++)//从第 3 列循环到最后{Output0Buffer.AddRow();Output0Buffer.ColA = rdr[0].ToString();Output0Buffer.ColB = rdr[1].ToString();Output0Buffer.FactName = rdr.GetName(i);Output0Buffer.FactValue = rdr.GetDouble(i);}//rowCt++;//递增计数器}xlConn.Close();}

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