| Author |
 Topic  |
|
|
digitalmcgrath
Starting Member
USA
5 Posts |
 Posted - 03/12/2013 : 21:28:15
|
I have an excel file that is formatted like:
00 24 31 36
30 125.06 156.35 181.93
36 136.44 170.55 204.66
42 155.12 192.08 226.19
48 172.17 210.35 246.08
I need to insert this into a table containing productcode, productcost. An example of the productcode would be 2430 and the product cost would be 125.06. So I am looking for a solution that will combine the header row with column A and get the corresponding price where the rows and columns intersect.
Can someone point me in the right direction? |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
52326 Posts |
Posted - 03/13/2013 : 01:06:03
|
dump the results as it is to a sql server table using export import wizard. Choose first row as column header option in export import properties. Then once done you can use query like below (Assuming you named table as ExcelDest)
--this part is just for illustration. you dont require this as you already create table in export import
declare @ExcelDest table
(
[00] int,
[24] decimal(7,2),
[31] decimal(7,2),
[36] decimal(7,2)
)
--this is just for data population. you dont need this either as this is done by export import package
insert @ExcelDest
values(30, 125.06, 156.35, 181.93),
(36, 136.44, 170.55, 204.66),
(42, 155.12, 192.08, 226.19),
(48, 172.17, 210.35, 246.08)
--this is your ACTUAL SOLUTION. Replace @ExcelDest with name of table you created inside export import wizard
select CAST(header AS varchar(2)) + CAST([00] AS varchar(2)) AS PrdtCode,
val
from @ExcelDest
unpivot(val for header in ([24],[31],[36]))u
output
-------------------------------------
PrdtCode val
-------------------------------------
2430 125.06
3130 156.35
3630 181.93
2436 136.44
3136 170.55
3636 204.66
2442 155.12
3142 192.08
3642 226.19
2448 172.17
3148 210.35
3648 246.08
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
digitalmcgrath
Starting Member
USA
5 Posts |
Posted - 03/13/2013 : 08:35:09
|
Thanks for the response. This has been very helpful!
quote:
Originally posted by visakh16
dump the results as it is to a sql server table using export import wizard. Choose first row as column header option in export import properties. Then once done you can use query like below (Assuming you named table as ExcelDest)
--this part is just for illustration. you dont require this as you already create table in export import
declare @ExcelDest table
(
[00] int,
[24] decimal(7,2),
[31] decimal(7,2),
[36] decimal(7,2)
)
--this is just for data population. you dont need this either as this is done by export import package
insert @ExcelDest
values(30, 125.06, 156.35, 181.93),
(36, 136.44, 170.55, 204.66),
(42, 155.12, 192.08, 226.19),
(48, 172.17, 210.35, 246.08)
--this is your ACTUAL SOLUTION. Replace @ExcelDest with name of table you created inside export import wizard
select CAST(header AS varchar(2)) + CAST([00] AS varchar(2)) AS PrdtCode,
val
from @ExcelDest
unpivot(val for header in ([24],[31],[36]))u
output
-------------------------------------
PrdtCode val
-------------------------------------
2430 125.06
3130 156.35
3630 181.93
2436 136.44
3136 170.55
3636 204.66
2442 155.12
3142 192.08
3642 226.19
2448 172.17
3148 210.35
3648 246.08
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
52326 Posts |
Posted - 03/13/2013 : 10:14:27
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
digitalmcgrath
Starting Member
USA
5 Posts |
Posted - 03/13/2013 : 21:32:05
|
I finally had a chance to sit down and try this solution and it almost works for what I need.
I have two questions...
1. Is there a way to dynamically generate the header field in the unpivot? I tried using a select statement to query information_schema.columns, but that threw an error.
2. If I have an excel file that has some empty cells in the grid, they are imported as "null" and then when I run your query, it stops at the first null it comes to. Is there an easy way around this?
|
|
|
| |
Topic |
|
|
|
Import from Excel