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!