Load data from Excel and CSV to SQL Server

How to load data from excel / csv to SQL Server

Most repetitive task in DBA daily activities.

This task can be accomplished in many ways.

By using SSIS we can use IMPORT and EXPORT utility to load the data into SQL Server.

The other way around is using "OPENROWSET" function.

Syntax for Openrowset:


sp_configure 'show advanced options',1GO
reconfigure
GO
sp_configure 'Ad Hoc Distributed Queries',1
GO
reconfigure
GO
select * from openrowset('Microsoft.ACE.OLEDB.12.0','EXCEL 12.0;database=C:\Users\Documents\Office\SIP.xlsx;','select * from [Sheet1$]')


We have to use the OLEDB provider to load the data from excel to SQL Server.

Above OLEDB used is : Microsoft.ACE.OLEDB.12.0

Use the abvoe OLEDB for Excel version greater than 2003.

For lesser version of Excel use " Microsoft.Jet.OLEDB.4.0" Provider.

===================================================

For loading data from CSV to SQL Server.

use below syntax:


SELECT * FROM OPENROWSET('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)}','SELECT * FROM \\share\file.csv')


===================================================

For accessing other servers without creating linked server use the below syntax:


select * FROM OPENROWSET('SQLNCLI', 'Server=100.10.1.1;Trusted_Connection=yes;','SELECT 1 FROM master.dbo.sysprocesses WHERE program_name = N''SQLAgent -  Generic Refresher''')


=====================================================

Hope the above syntax will help in the day to day activities of a DBA

Thanks