August 7, 2014 at 6:27 am
Hi everybody,
I have a large excel spreadsheet created by finance user that contains several decades worth of sales data.
Here is a small sample:
Guest Count
Unit ID1/2/2011 1/9/2011
3 0
7 0
8 0
90 0
151696 1202
222769 1914
232704 2110
250 0
282838 1882
331089 691
363581 3064
371469 1062
I need to get this data into an SQL table in the following form so I can use it to further manipulate the data and update several other tables. I am thinking that UNPIVOT or CROSS APPLY might be the way to go, but am not sure how to code it.
The desired output:
Unit IDDate Guest Count
31/2/2011 NULL
71/2/2011 NULL
81/2/2011 NULL
91/2/2011 0
151/2/2011 1696
221/2/2011 2769
231/2/2011 2704
251/2/2011 0
281/2/2011 2838
331/2/2011 1089
361/2/2011 3581
371/2/2011 1469
31/9/2011 0
71/9/2011 0
81/9/2011 0
91/9/2011 0
151/9/2011 1202
221/9/2011 1914
231/9/2011 2110
251/9/2011 0
281/9/2011 1882
331/9/2011 691
361/9/2011 3064
371/9/2011 1062
The spreadsheet has 2900 columns and 3500 rows so performance is definitely a consideration as well.
Any help/suggestions are greatly appreciated.
Thanks,
Petr
August 7, 2014 at 6:53 am
You really need to give your data in a consumable format. Please take a moment to look through the "how to post code and data for the best help" link in my signature.
As a start, I have interpreted your spreadsheet example to be
create table #temp
(
unitid int,
[1/2/2011] int,
[1/9/2011] int
)
insert into #temp
values
(3, null, 0),
(7, null, 0),
(8, null, 0),
(9, 0, 0),
(15, 1696, 1202),
(22, 2769, 1914),
(23, 2704, 2110),
(25, 0, 0),
(28, 2838, 1882),
(33, 1089, 691),
(36, 3581, 3064),
(37, 1469, 1062)
select * from #temp
drop table #temp
With easily consumable data like this provided, people can now start to work on your problem quickly and not have to figure out now the data looks.
I am sure that someone will get back to you shortly
August 7, 2014 at 7:49 am
With uploading nulls and wanting it to be sort of dynamic, the below will work
create table temp
(
unitid int,
[1/2/2011] int,
[1/9/2011] int
)
insert into temp
values
(3,null, 0),
(7,null, 0),
(8,null, 0),
(9,0, 0),
(15, 1696, 1202),
(22, 2769, 1914),
(23, 2704, 2110),
(25, 0, 0),
(28, 2838, 1882),
(33, 1089, 691),
(36, 3581, 3064),
(37, 1469, 1062)
DECLARE @tableName varchar(10)
SET @tableName = 'temp'
DECLARE @sql VARCHAR(MAX)
SET @sql = ''
SELECT @sql = @sql + 'UPDATE ' + @tableName + ' SET [' + c.name + '] = ''0'' WHERE [' + c.name + '] IS NULL ;'
FROM sys.columns c
INNER JOIN sys.tables t ON c.object_id = t.object_id
INNER JOIN sys.types y ON c.system_type_id = y.system_type_id
WHERE t.name = @tableName AND y.name IN ('int')
EXEC (@sql)
DECLARE
@table NVARCHAR(257) = N'temp',
@key_column SYSNAME = N'unitid';
DECLARE
@colNames NVARCHAR(MAX) = N'',
@colValues NVARCHAR(MAX) = N'',
@sql2 NVARCHAR(MAX) = N'';
SELECT
@colNames += ',
' + QUOTENAME(name),
@colValues += ',
' + QUOTENAME(name)
+ ' = CONVERT(VARCHAR(320), ' + QUOTENAME(name) + ')'
FROM sys.columns
WHERE [object_id] = OBJECT_ID(@table)
AND name <> @key_column;
SET @sql2 = N'SELECT unitid, property, value INTO #temp
FROM
(
SELECT ' + @key_column + @colValues + '
FROM ' + @table + '
) AS t
UNPIVOT
(
Value FOR Property IN (' + STUFF(@colNames, 1, 1, '') + ')
) AS up
select UnitID, property AS Date, value as GuestCount from #temp order by 2,1;
Drop table #temp';
--PRINT @sql2;
EXEC sp_executesql @sql2;
drop table temp
August 7, 2014 at 8:25 am
First of all, how do you get your data into SQL Server? What are you using?
August 7, 2014 at 8:49 am
This will be just a one time import from excel. I am not even going to set up a custom SSIS job. I will just use the regular SSMS feature to import the data into a staging table, clean it up and then go from there,
thanks,
Petr
August 7, 2014 at 9:13 am
When I execute the code, I get the following set of errors:
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 44
Must declare the scalar variable "@colNames".
Msg 137, Level 15, State 2, Line 55
Must declare the scalar variable "@key_column".
Msg 137, Level 15, State 2, Line 65
Must declare the scalar variable "@sql2".
August 7, 2014 at 9:15 am
Code works fine for me.
The code box only shows so much and is scrollable, so you need to ensure you select everything from within the code box, not just what you can see.
August 7, 2014 at 9:19 am
I know, I am definitely taking everything that is in the box. I even attempted to replace the ' marks.
not sure what is going on here,
Petr
August 7, 2014 at 10:47 am
the database is 2005 version that is why I was getting that error,
PEtr
August 7, 2014 at 10:53 am
How would I adjust the code so it works with SQL 2005 database?
thanks for all you help,
Petr
August 7, 2014 at 10:56 am
OK< I guess I can answer my own questions here :). I have to separate the declare and set into two statements.
Petr
August 8, 2014 at 1:40 am
Also try to post in the right version of SQL servers forums. As you posted in the 2012 forum I gave you a 2012 answer.
Alternatively, detail what version of SQL you are running in your post, that way we can try and replicate your environment as best as possible.
August 8, 2014 at 7:59 am
What I've done in the past is manipulate the data into tabular format in Excel using a Pivot Table and then it's just a straight import:
http://datapigtechnologies.com/blog/index.php/transposing-a-dataset-with-a-pivottable/
November 19, 2018 at 2:29 am
vecerda - Thursday, August 7, 2014 6:27 AMHi everybody,I have a large excel spreadsheet created by finance user that contains several decades worth of sales data.Here is a small sample:Guest CountUnit ID1/2/2011 1/9/20113 07 08 090 0151696 1202222769 1914232704 2110250 0282838 1882331089 691363581 3064371469 1062I need to get this data into an SQL table in the following form so I can use it to further manipulate the data and update several other tables. I am thinking that UNPIVOT or CROSS APPLY might be the way to go, but am not sure how to code it.The desired output:Unit IDDate Guest Count31/2/2011 NULL71/2/2011 NULL81/2/2011 NULL91/2/2011 0151/2/2011 1696221/2/2011 2769231/2/2011 2704251/2/2011 0281/2/2011 2838331/2/2011 1089361/2/2011 3581371/2/2011 146931/9/2011 071/9/2011 081/9/2011 091/9/2011 0151/9/2011 1202221/9/2011 1914231/9/2011 2110251/9/2011 0281/9/2011 1882331/9/2011 691361/9/2011 3064371/9/2011 1062The spreadsheet has 2900 columns and 3500 rows so performance is definitely a consideration as well.Any help/suggestions are greatly appreciated.Thanks,Petr
November 20, 2018 at 4:51 am
vecerda - Thursday, August 7, 2014 6:27 AMHi everybody,I have a large excel spreadsheet created by finance user that contains several decades worth of sales data.Here is a small sample:Guest CountUnit ID1/2/2011 1/9/20113 07 08 090 0151696 1202222769 1914232704 2110250 0282838 1882331089 691363581 3064371469 1062I need to get this data into an SQL table in the following form so I can use it to further manipulate the data and update several other tables. I am thinking that UNPIVOT or CROSS APPLY might be the way to go, but am not sure how to code it.The desired output:Unit IDDate Guest Count31/2/2011 NULL71/2/2011 NULL81/2/2011 NULL91/2/2011 0151/2/2011 1696221/2/2011 2769231/2/2011 2704251/2/2011 0281/2/2011 2838331/2/2011 1089361/2/2011 3581371/2/2011 146931/9/2011 071/9/2011 081/9/2011 091/9/2011 0151/9/2011 1202221/9/2011 1914231/9/2011 2110251/9/2011 0281/9/2011 1882331/9/2011 691361/9/2011 3064371/9/2011 1062The spreadsheet has 2900 columns and 3500 rows so performance is definitely a consideration as well.Any help/suggestions are greatly appreciated.Thanks,Petr
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply