SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Import from Excel
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

digitalmcgrath
Starting Member

USA
5 Posts

Posted - 03/12/2013 :  21:28:15  Show Profile  Reply with Quote
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
52317 Posts

Posted - 03/13/2013 :  01:06:03  Show Profile  Reply with Quote
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/

Go to Top of Page

digitalmcgrath
Starting Member

USA
5 Posts

Posted - 03/13/2013 :  08:35:09  Show Profile  Reply with Quote
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/



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/13/2013 :  10:14:27  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

digitalmcgrath
Starting Member

USA
5 Posts

Posted - 03/13/2013 :  21:32:05  Show Profile  Reply with Quote
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?

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000