Tuesday, August 20, 2013

Script To Migrate Data from Excel to SQL Server

Simply, here is a script to insert data from your xls or xlsx data to SQL Server.

-- This script Migrates data from Ms. Excel to Ms. SQL Server
-- GLOBAL CONFIGURATIONS For ALL Senarios
sp_configure 'show advanced option', 1
reconfigure
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

USE [master]
GO
EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
GO
EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
GO

-- Read from the Excel file and
-- Insert the data to 'business_unit_mapping' table

USE dbName

-- The following data reads one column from excel and inserts it to a table that has one column 
-- HDR=YES means the excel file has a header row
INSERT INTO dbo.tableNameOne (column1)
SELECT column1NameInExcel 
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0 Xml;HDR=YES;Database=C:\My Files\My Excel File.xlsx','SELECT * FROM [Sheet1Name$]') 
GO

-- The following inserts custom data to the first and second column and reads the third column's data from excel and inserts it to a table that has three columns 
-- Replaces the Header BOOK_NUM with blank.
INSERT INTO dbo.tableName (column1, column2, column3)
SELECT 0 as column 1, 5 as column2, replace(BOOK_NUM, '/', '') FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0 Xml;HDR=YES;Database=C:\My Files\My Excel File.xlsx','SELECT * FROM [Sheet1Name$]')
GO

If you such error, i.e.   "The 32-bit OLE DB provider "Microsoft.ACE.OLEDB.12.0" cannot be loaded in-process on a 64-bit SQL Server", then you need to download AccessDatabaseEngine_x64.exe.

For AccessDatabaseEngine_x64.exe to work, you might need to install Ms. Office 2007/20 x64 (64-bit) or just remove your Ms. Office 2007/20 x86 (32-bit) application and install it. [Finally, you might re-install Ms. Office 2007/20 x86 (32-bit)]

Enjoy!