Import from Excel - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Import from Excel

Author

Topic

digitalmcgrath
Starting Member

5 Posts

Posted - 2013-03-12 : 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

52326 Posts

Posted - 2013-03-13 : 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

5 Posts

Posted - 2013-03-13 : 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

52326 Posts

Posted - 2013-03-13 : 10:14:27

welcome

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

digitalmcgrath
Starting Member

5 Posts

Posted - 2013-03-13 : 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?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources