CODEWITHSUNDEEP
ssis
Raymondo
Raymondo

Reputation: 587

SSIS Excel Import (Column Names Changing)

I have an Excel file that loosely resembles the following format:

enter image description here

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:

enter image description here

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

Upvotes: 1

Views: 2087

Answers (2)

KeithL
KeithL

Reputation: 5594

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();
    }

Upvotes: 2

KeithL
KeithL

Reputation: 5594

If the columns remain in order, then you can skip header rows and select 1st row does not contain headers.

Upvotes: 1

Related Questions