Spreadsheet Sizer

A customer was constantly importing a variety of XLS files, and atleast once a week having to scroll through raw data to find the cause of a generic “data would be truncated” type error.

I pieced together this SP (to be called by the already inplace EMAIL_SORTER procedure) to indicate the column with the problem …

USE [DBA]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/***************************************************************************************************
Procedure Name	:	Spreadsheet_check
Date Created	:	12/02/2013

Description	:	Reports the Size of spreadsheet columns
***************************************************************************************************/

alter procedure [dbo].[Spreadsheet_check]
@requester varchar(200)
as

declare @recipients varchar(200), @query varchar(200), @subject varchar(100), @body varchar(200)
declare @file_attachments varchar(200), @xlsname varchar(50), @doscmd varchar(50), @sqlcmd varchar(max)

-- get the name of the oldest spreadsheet to be checked
set @doscmd = 'dir /b E:DBAsizer*.xls /OD'
truncate table tmp_inputs
insert into tmp_inputs
exec master.dbo.xp_cmdshell @doscmd
set @xlsname = (select top 1 * from tmp_inputs)

-- import the spreadsheet
begin try drop table tmp_import end try begin catch end catch -- ignore errors

begin try
set @sqlcmd = 'select * into tmp_import
				from openrowset(''microsoft.ace.oledb.12.0'',
				''excel 12.0;database=e:dbasizer' + @xlsname + ''',
				''select * from [Sheet1$]'')'
exec (@sqlcmd)
end try

	--ERROR: Wrong Sheet Name
	begin catch
	select	@recipients = @requester,
		@query = '',
		@subject = 'Excel Size checker: Wrong worksheet',
		@body = 'Sorry the SPREADSHEET SIZING TOOL can only work with default WorkSheet names IE: sheet1',
		@file_attachments = '\' + dbo.RB_GetServerName() + 'DBAsizer' + @xlsname
	exec dbo.RB_Emailsender @recipients, @query, @subject, @body, @file_attachments
	goto tidyup
	end catch

-- look up metadata
select @sqlcmd = stuff((select
'union all
select ' + quotename(column_name,'''') + ',
max(len(' + quotename(column_name) + '))
from ' + quotename(table_schema) + '.' + quotename(table_name)
from information_schema.columns
where table_name = 'tmp_import'
and table_schema = 'dbo'
order by column_name
for xml path(''),type).value('.','varchar(max)'),1,11,'')

-- email report
truncate table tmp_sizer
insert into tmp_sizer exec(@sqlcmd)

select	@recipients = @requester,
		@query = 'SELECT * FROM  [' + dbo.RB_GetServerName() + '].DBA.dbo.tmp_sizer',
		@subject = 'Excel Size Checker',
		@body = 'To use this SPREADSHEET SIZING TOOL please email an xls file to SQLSRV with the subject of SIZER. ' + Space(5) + ' Please find below the results for the attached spreadsheet. ' + Space(5),
		@file_attachments = '\' + dbo.RB_GetServerName() + 'DBAsizer' + @xlsname

exec dbo.RB_Emailsender @recipients, @query, @subject, @body, @file_attachments

tidyup:
set @doscmd = 'del \' + dbo.RB_GetServerName() + 'dbasizer' + @xlsname
exec master.dbo.xp_cmdshell @doscmd