SQL Server : import Excel and place column name and the value in table-CodePudding

I have an Excel file that needs to be imported into SQL Server.

Excel sheet:

Product	202147	202148	202149
Prod-A	15	10	11
Prod-B	7	18	16

This needs to be read into a SQL Server table MyData:

YearWeek	Product	Value
202147	Prod-A	15
202147	Prod-B	7
202148	Prod-A	10
202148	Prod-B	18
202149	Prod-A	11
202149	Prod-B	16

How can I do this in an SSIS import?

CodePudding user response：

You can't do that directly on SSIS import. On the other hand, you can import your excel on a table and then run the following sql query to achieve your goal:

select yearWeek, product, value
from myData
unpivot (value for yearWeek in ([202147], [202148], [202149])) up
order by 1,2

-- Result
/*
yearWeek    product value
202147      Prod-A  15
202147      Prod-B  7
202148      Prod-A  10
202148      Prod-B  18
202149      Prod-A  11
202149      Prod-B  16
*/