T-SQL - OPENROWSET Access to Excel Data

This is a sad story. Se developer came to me with the question: is there a DOS command to change an Excel file to a CSV file?

There was no context to the question, but I knew this was going to require a step back. The intention was to load different data sets on the same sheet into a database as part of a conversion away from EUC solutions to an enterprise solution. We discussed the merits of using third party libraries to access the Excel data, using an ETL tool with built in access engines (Informatica or SSIS), or using raw SQL. As he is stronger in SQL we discussed using OPENROWSET to access the data using the Excel/Access data access engine. We then planned to load into a staging table and parse out the subsets of data using the sub total columns as max row number delimiters.

One issue was that all examples we could find used the Jet 4.0 engine

SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Customers$]

SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])

SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')

This is only useful for 32 bit applications and servers. I found the 64 bit data access provider is the ACE provider. This needs to be downloaded and registered on the database server to allow access to EUC sources like Excel and Access in a 64 bit operating environment. the SQL would be reformatted as:

SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')

REFS:

http://www.microsoft.com/en-us/download/details.aspx?id=13255

http://support.microsoft.com/kb/321686

http://social.msdn.microsoft.com/Forums/en-US/b6274401-d8fe-49ed-bd79-e8cd431fd3ce/microsoftjetoledb40-has-not-been-registered-for-openrowset-query?forum=transactsql

Dynamic SQL - Drop Keys Truncate Tables and Add Keys

So I need to do the same routine when deploying to a test environment or a stage database when I add columns in the middle of a table or change the nullability of a column. Getting the column definition for a constraint can be a pain, so I wanted to record this here for reuse (thanks to Pinal Dave for keeping the old keys sys tables query online).


USE <DATABASE>


-------------------- BEGIN - Initialize System State Tables -------------------

DECLARE

@name VARCHAR(128),

@schema VARCHAR(128),

@tableSchema VARCHAR(128),

@tableName VARCHAR(128),

@sqlCommand NVARCHAR(1000),

@Rows INT,

@i INT ;

DECLARE @sysFKTable TABLE (RowID INT IDENTITY(1,1), ConstraintName sysname, SchemaName sysname, TableName sysname, ColumnName sysname, ReferenceSchemaName sysname, ReferenceTableName sysname, ReferenceColumnName sysname)

DECLARE @schemaTbl TABLE(RowID INT IDENTITY(1,1), SchemaName VARCHAR(128), TableName VARCHAR(128), IndexName VARCHAR(128));

DECLARE @t TABLE(RowID INT IDENTITY(1,1), SchemaName VARCHAR(128), ObjectName VARCHAR(128));

DECLARE @t1 TABLE(RowID INT IDENTITY(1,1), SchemaName VARCHAR(128), ObjectName VARCHAR(128));

PRINT 'Load @sysFKTable'

INSERT INTO @sysFKTable

SELECT f.name AS ConstraintName,

SCHEMA_NAME(f.SCHEMA_ID) SchemaName,

OBJECT_NAME(f.parent_object_id) AS TableName,

COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,

SCHEMA_NAME(o.SCHEMA_ID) ReferenceSchemaName,

OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,

COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName

FROM sys.foreign_keys AS f

JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id

JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id



-- ORDER BY tc.TABLE_SCHEMA, tc.TABLE_NAME, tc.CONSTRAINT_SCHEMA, tc.CONSTRAINT_NAME

PRINT 'Load @schemaTbl'

INSERT INTO @schemaTbl(SchemaName, TableName, IndexName)

select object_schema_name(object_id), object_name(object_id), name

from sys.indexes

where type > 0 AND object_schema_name(object_id) != 'sys'

PRINT 'Load @t for table trunc'

INSERT INTO @t(SchemaName, ObjectName)

SELECT

tc.TABLE_SCHEMA,

tc.TABLE_NAME

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc

WHERE (tc.CONSTRAINT_CATALOG = DB_NAME()) AND (tc.CONSTRAINT_TYPE = 'FOREIGN KEY')

ORDER BY SUBSTRING(tc.CONSTRAINT_NAME,PATINDEX('%o[_]%',tc.CONSTRAINT_NAME)+2,100) DESC

PRINT 'Load @t1 for table trunc'

INSERT INTO @t1

SELECT t.TABLE_SCHEMA, t.TABLE_NAME

FROM INFORMATION_SCHEMA.TABLES t

WHERE t.TABLE_TYPE = 'BASE TABLE'

ORDER BY t.TABLE_SCHEMA, t.TABLE_NAME DESC





PRINT 'This replaces merge statement as this targets sql server 2005'

INSERT INTO @t

SELECT

t1.SchemaName,

t1.ObjectName

FROM @t1 t1

WHERE

t1.ObjectName NOT IN (SELECT t.ObjectName FROM @t t)



-------------------- END - Initialize System State Tables -------------------

-------------------- BEGIN - Drop FKs ---------------------------------------

SET @Rows = (SELECT COUNT(RowID) FROM @sysFKTable)

SET @i = 1

WHILE (@i <= @Rows)



BEGIN

SELECT @sqlCommand = 'ALTER TABLE [' + t.SchemaName + '].[' + t.TableName + '] DROP CONSTRAINT [' + t.ConstraintName + '];',

@name = t.ConstraintName,

@tableSchema = t.SchemaName,

@tableName = t.TableName

FROM @SysFKTable t WHERE RowID = @i;

EXEC sp_executesql @sqlCommand;

PRINT 'DROP CONSTRAINT: ' + @name + ' on [' + @tableSchema + '].[' + @tableName + ']';

SET @i = @i + 1;



END



--------------------------- END - Drop FKs---------------------------------

--------------------------- BEGIN - TRUNCATE TABLES------------------------

SET @Rows = (SELECT COUNT(RowID) FROM @t)

SET @i = 1;

WHILE (@i <= @Rows)



BEGIN

SELECT @sqlCommand = 'TRUNCATE TABLE [' + t.SchemaName + '].[' + t.ObjectName + '];', @schema = t.SchemaName, @name = t.ObjectName FROM @t t WHERE RowID = @i;

EXEC sp_executesql @sqlCommand;

PRINT 'TRUNCATE TABLE: [' + @schema + '].[' + @name + ']';

SET @i = @i + 1;



END

------------------------- END - Truncate Tables ------------------------------

------------------------- BEGIN - ADD BACK CONSTRAINTS -----------------------

SET @Rows = (SELECT COUNT(RowID) FROM @sysFKTable)

SET @i = 1

WHILE (@i <= @Rows)



BEGIN

BEGIN TRY

SELECT @sqlCommand = 'ALTER TABLE [' + SchemaName + '].[' + TableName + '] WITH CHECK ADD CONSTRAINT [' + ConstraintName

+ '] FOREIGN KEY([' + ColumnName + ']) REFERENCES [' + ReferenceSchemaName + '].[' + ReferenceTableName

+ '] ([' + ReferenceColumnName + '])',

@name = t.ConstraintName,

@tableSchema = t.SchemaName,

@tableName = t.TableName

FROM @SysFKTable t WHERE RowID = @i;

EXEC sp_executesql @sqlCommand;

PRINT 'ADD CONSTRAINT: ' + @name + ' on [' + @tableSchema + '].[' + @tableName + ']';

SET @i = @i + 1;

END TRY

BEGIN CATCH

PRINT 'Error in creating constraints'

END CATCH



END

------------------------- END - Add FKs ----------------------------------------

------------------------- BEGIN - INDEX MGMT -----------------------------------

SET @Rows = (SELECT COUNT(RowID) FROM @schemaTbl)

SET @i = 1;

WHILE (@i <= @Rows)

BEGIN

 SELECT @sqlCommand = 'ALTER INDEX ' + t.IndexName + ' ON ' + t.SchemaName + '.' + t.TableName + ' REBUILD;',

@schema = t.SchemaName, @name = t.IndexName, @tableSchema = t.SchemaName, @tableName = t.TableName

FROM @schemaTbl t WHERE RowID = @i;

EXEC sp_executesql @sqlCommand;

PRINT 'REBUILD INDEX: ' + @name + ' on [' + @tableSchema + '].[' + @tableName + ']';

SET @i = @i + 1;

END

GO ------------------------- END ------------------------------------------------