使用SSIS取消合并栏的数据透视 [英] Unpivot data excel with merged column using SSIS

问题描述

我正在使用SSIS从Excel导出到文本，就我而言，我需要导出文件包含任何人都可以帮助或针对我的情况提出建议的合并列的地方?

I'm using SSIS to export from Excel to text and in my case, I need to export where the file contains merged columns anyone can help or have suggest for my case?

输入Excel

      A           B         C        D      E
+-------------+-----------------+-----------------+
|  Shop Name  |      Monday     |     Tuesday     |
|             +---------+-------+---------+-------+
|             | Jackson | Steve | Jackson | Steve |
+-------------+---------+-------+---------+-------+
| 7Eleven     | 11      | 30    | 23      | 21    |
+-------------+---------+-------+---------+-------+
| Delta Shop  | 43      | 12    | 33      | 2     |
+-------------+---------+-------+---------+-------+

预期输出

+-------------+---------+-------------+-------+
|  Shop_Name  |   Day   | Member_Name | Point |
+-------------+---------+-------------+-------+
| 7Eleven     | Monday  | Jackson     | 11    |
+-------------+---------+-------------+-------+
| 7Eleven     | Monday  | Steve       | 30    |
+-------------+---------+-------------+-------+
| Delta Shop  | Monday  | Jackson     | 43    |
+-------------+---------+-------------+-------+
| Delta Shop  | Monday  | Steve       | 12    |
+-------------+---------+-------------+-------+
| 7Eleven     | Tuesday | Jackson     | 23    |
+-------------+---------+-------------+-------+
| 7Eleven     | Tuesday | Steve       | 21    |
+-------------+---------+-------------+-------+
| Delta Shop  | Tuesday | Jackson     | 33    |
+-------------+---------+-------------+-------+
| Delta Shop  | Tuesday | Steve       | 2     |
+-------------+---------+-------------+-------+

推荐答案

使用oledb和ACE驱动程序(Microsoft Office)读取Excel文件

Using oledb and ACE driver (Microsoft Office) to read excel file

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace ConsoleApplication124
{
    class Program
    {
        static void Main(string[] args)
        {


            string connStr = "Provider=Microsoft.ACE.OLEDB.15.0;Data Source=c:\\temp\\test.xlsx;Extended Properties=\"Excel 12.0;HDR=NO;IMEX=1\"";
            string query = "Select * From [Sheet1$]";

            OleDbDataAdapter adapter = new OleDbDataAdapter(query, connStr);
            DataTable dt = new DataTable();
            adapter.Fill(dt);

            string[] days = dt.Rows[0].ItemArray.Skip(1).Select(x => (x == DBNull.Value) ? string.Empty : ((string)x).Trim()).ToArray();
            string[] people = dt.Rows[1].ItemArray.Skip(1).Select(x => (x == DBNull.Value) ? string.Empty : ((string)x).Trim()).ToArray();
            int numberDays = days.Where(x => x != string.Empty).Count();
            int numberPeople = people.Where(x => x != string.Empty).Distinct().Count();


            string[] columnNames = { "Shop_Name", "Day", "Member_Name", "Point" };
            Console.WriteLine(string.Join(",", columnNames));
            for (int row = 2; row < dt.Rows.Count; row++)
            {
                string[] columns = dt.Rows[row].ItemArray.Select(x => (x == DBNull.Value) ? string.Empty : ((string)x).Trim()).ToArray();

                string shop = columns[0];

                for (int col = 1; col < dt.Rows[row].ItemArray.Count(); col++)
                {
                    object point = dt.Rows[row].Field<string>(col);
                    if (point != null)
                    {
                        string pointStr = ((string)point).Trim();
                        int dayIndex = numberPeople * ((col - 1) / numberPeople);
                        string day = days[dayIndex];
                        string person = people[col - 1];

                        string[] outputData = { shop, day, person, pointStr };
                        Console.WriteLine(string.Join(",", outputData));
                    }
                }
            }
            Console.ReadLine();
        }

    }



}

这篇关于使用SSIS取消合并栏的数据透视的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！