C＃中的Excel表到SQL表 - 在Excel中将Excel中的多个列映射到单个列

I am trying to upload individual ListObject tables in excel to SQL tables in a database.

我试图将excel中的单个ListObject表上传到数据库中的SQL表。

I have already wrote code that simply generates an SQL query string and executes - this works but it's not easily maintainable.

我已经编写了只生成SQL查询字符串并执行的代码 - 这可以工作,但不容易维护。

So I found this technique: https://*.com/a/11268550/1610402

所以我找到了这个技术:https://*.com/a/11268550/1610402

This allows me to fill a data table and bulk insert that into SQL; with column mapping capabilities if they are required.

这允许我填充数据表并将其批量插入到SQL中;具有列映射功能(如果需要)。

However my table structure in Excel differs more than simply column 'names' to avoid redundant data.

但是,我在Excel中的表结构与简单的“名称”列不同,以避免冗余数据。

Therefore I need to map columns from Excel (example):

因此,我需要从Excel映射列(示例):

Column1    Column2   '2015'  '2016'  '2017'  '2018'  '2019'  '2020'
1          1          0.1     0.2     0.3     0.35    0.375   0.4
1          2          0.1     0.2     0.3     0.35    0.375   0.4

To columns in SQL, like the following:

对于SQL中的列,如下所示:

Column1    Column2    Year    Value
1          1          2015    0.1
1          1          2016    0.2
1          1          2017    0.3
1          1          2018    0.35
1          1          2019    0.375
1          1          2020    0.4
1          2          2015    0.1
1          2          2016    0.2
1          2          2017    0.3
1          2          2018    0.35
1          2          2019    0.375
1          2          2020    0.4

Is there a better way of doing this in C# .Net or should I stick with my nested for loop and string builder?

有没有更好的方法在C#.Net中执行此操作,还是应该坚持使用嵌套的for循环和字符串生成器?

1 个解决方案

#1

The concept of what you want is called 'unpivoting'. Whether you do this in C# or is not of importance, since you can simply load the first data in SQL-Server and unpivot this data by

你想要的概念被称为'unpivoting'。无论您是在C#中执行此操作还是不重要,因为您只需在SQL Server中加载第一个数据并通过以下方式取消数据:

SELECT IDENTITY(int,1,1) as RowId, Column1, Column2, [Year], [Value]
INTO newTable
FROM (
   SELECT Column1,Column2,[2015],[2016],[2017],[2018],[2019],[2020] FROM oldTable
) as source
UNPIVOT
(
   [Value] for [Year] in [2015],[2016],[2017],[2018],[2019],[2020]
) as target

The square brackets are required to make these strings appear as column names in stead of numbers or reserved words.

方括号必须使这些字符串显示为列名而不是数字或保留字。

#1