Create SQL procedure to Import Excel file in SQL12

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Create SQL procedure to Import Excel file in SQL12

Author

Topic

SQL_Support_2015
Starting Member

7 Posts

Posted - 2015-01-16 : 05:11:39

I am not a SQL developer but having basic understanding and looking for your help. I need to import regularly several excel files in SQL 2012 manually and it take most of my time.

I am looking for a SQL script that will help me to import excel file automatically as following:

1. Create a procedure in SQL that will to import a designated excel file into a designated SQL table in SQL server 2012.

2. The procedure must allow the user to specify the following options:
- Source file name and location;
- SQL table to be affected;
- Type of importation to be done (e.g. Append, Create or Reset);

If the user specifies “Append”, the script must insert the data coming from the excel file to the designated SQL table.

If the user specifies “Create”, the script must create a SQL table where the data will be inserted.

If the user specifies “Reset” the script must delete all the data within the SQL existing table and put the new data coming from the file.

In SQL the procedure must accept input parameters and will look something like:

CREATE PROCEDURE ImportfilesTextFile
(
@TABLENAME VARCHAR(1000),
@filepath varchar(1000),
@TypeOfUpdate varchar(50),
@FIELDTERMINATOR varchar(1),
@ROWTERMINATOR varchar(2)
)

The user will then write a command like

EXEC ImportfilesTextFile 'SQL_Table_Name','C:\FilePath\filename.csv', 'Append', '|', '/n'

Looking forward for your support.
Rajinder

***************************************

--Below is the example for approch

Create procedure ImportExcelFile
(@Source varchar(1000)
, @SourceSheet varchar (100)
, @DestinationTable varchar (100))
as

declare @retval int
EXEC master..xp_fileexist @Source, @retval output -- check if file exists

if @retval = 0
begin
print 'file does not exist.'
return
end

if @SourceSheet is null or @SourceSheet = ''
set @SourceSheet = '[Sheet1$]' -- assume that the Sheet name on excel file is the default name
else
set @SourceSheet = '[' + ltrim(rtrim(@SourceSheet)) + '$]'

if @DestinationTable is null or @DestinationTable = ''
set @DestinationTable = substring(@SourceSheet, 2, len(@SourceSheet) - 3) + convert(varchar, getdate(), 126)

-- TO CHECK THE DYNAMIC QUERY
--select ('select * into [' + @DestinationTable + '] from openrowset(''Microsoft.ACE.OLEDB.12.0'', ''Excel 12.0;HDR=YES;Database=' + @Source + ''', ' + ''' select * from '+@SourceSheet + ''')')

exec('select * into [' + @DestinationTable + '] from openrowset(''Microsoft.ACE.OLEDB.12.0'', ''Excel 12.0;HDR=NO;Database=' + @Source + ''', ' + ''' select * from '+@SourceSheet + ''')')

/*
exec('
INSERT INTO ' + @Tablename + ' SELECT * FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',
''Excel 8.0;Database=C:\..\..\..\packages\@Tablename.xls'', ''select * from myTable'')
')

*/

SQL_Support_2015

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-16 : 06:59:48

what happens when you run your proc? does it work, does it give an error message, does it just sometimes not work? Note that it's much easier to do this sort of things using SSIS.

SQL_Support_2015
Starting Member

7 Posts

Posted - 2015-01-16 : 07:09:36

Its not working as require and I need in SQL script.

SQL_Support_2015

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-16 : 09:02:53

Ok, it's not working. At what point does it fail? Do you get any error messages (please post them)? Does it seem to work but give incorrect results? If so, what results do you get and how do they differ from what you want?

btw, why do you need a sql script? SSIS will do the job better. For that matter, you can invoke a SSIS package with parameters from a sql script.

see: http://msdn.microsoft.com/en-ca/library/ff878160.aspx

SQL_Support_2015
Starting Member

7 Posts

Posted - 2015-01-16 : 09:40:07

Hello, thanks for you reply, but we need a sql script solution only.

When I run query second time then I got an error

"Msg 2714, Level 16, State 3, Procedure ImportExcelFile, Line 28
There is already an object named 'ImportExcelFile' in the database."

Therefore, I have added on the top

DROP PROCEDURE dbo.ImportExcelFile;
GO

then no error, and results are "Command(s) completed successfully."

However, I could not find stored procedure 'dbo.ImportExcelFile'. In addition I created the file to import header of excel.

************

In addition I created the file to import header of excel.

CREATE PROCEDURE [dbo].[ImportExcelFile_HEADERS]
(@Source varchar(1000)
, @SourceSheet varchar (100)
, @DestinationTable varchar (100))
as

declare @retval int
EXEC master..xp_fileexist @Source, @retval output -- check if file exists

if @retval = 0
begin
print 'file does not exist.'
return
end

if @SourceSheet is null or @SourceSheet = ''
set @SourceSheet = '[Sheet1$]' -- assume that the Sheet name on excel file is the default name
else
set @SourceSheet = '[' + ltrim(rtrim(@SourceSheet)) + '$]'

if @DestinationTable is null or @DestinationTable = ''
set @DestinationTable = substring(@SourceSheet, 2, len(@SourceSheet) - 3) + convert(varchar, getdate(), 126)

--select @DestinationTable
--select @Source
--select @SourceSheet
--select ('select * into [' + @DestinationTable + '] from openrowset(''Microsoft.ACE.OLEDB.12.0'', ''Excel 12.0;HDR=YES;Database=' + @Source + ''', ' + ''' select * from '+@SourceSheet + ''')')

exec('select * into [' + @DestinationTable + '] from openrowset(''Microsoft.ACE.OLEDB.12.0'', ''Excel 12.0;HDR=YES;Database=' + @Source + ''', ' + ''' select * from '+@SourceSheet + ''')')

--Select * into SQLServerTable FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:testing.xlsx;HDR=YES', 'SELECT * FROM [Sheet1$]')

please reply how we can import header and data in sql server from excel file.

SQL_Support_2015

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-16 : 10:30:26

You've specified HDR=YES

"HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.

https://www.connectionstrings.com/excel/

If you cannot find the stored procedure when you want to run it, be sure that:

1. you created the procedure in the correct database,
2. you execute a USE <dbname> for the database containing the stored procedure before you execute it.

BTW you haven't explained why you won't use SSIS. It is the simple way to do what you want.

Nonetheless, here's a good article on doing what you want:

https://www.simple-talk.com/sql/t-sql-programming/questions-about-using-tsql-to-import-excel-data-you-were-too-shy-to-ask/?utm_source=ssc&utm_medium=publink&utm_content=tsqlimportexcelquestions#first

Note that one of the main problems with your approach is that SQL needs to be able to access the file. If someone points to C:\My Documents\My.xls and tries to run this, it will probably fail unless the server happens to have that path and SQL can access it. Even then, the results would likely be incorrect. If the user points to a network share, then SQL Server needs to be able to access that location, which usually means running SQL server with a Domain account with access to the share.

SQL_Support_2015
Starting Member

7 Posts

Posted - 2015-01-19 : 04:28:50

Hello,

Thanks for the updated link and I have tried with below code to import excel file:
*-------------------
EXEC sp_configure 'Show Advanced Options', 1;
RECONFIGURE;
GO

EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO

EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO

SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xlm; HDR=YES;
Database=C:\Temp\TestFiles\OPENASNs.xlsx',
'Select * from [Sheet1$]');

*------------------

Facing error:

Msg 7308, Level 16, State 1, Line 17
OLE DB provider 'Microsoft.ACE.OLEDB.12.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

any feedback to resolve this error

SQL_Support_2015

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-19 : 08:53:47

FOund this link on the first Google search:

http://blog.codefluententities.com/2011/01/20/microsoft-access-database-engine-2010-redistributable/

FWIW, had you used SSIS, you would have been done many days ago.

SQL_Support_2015
Starting Member

7 Posts

Posted - 2015-01-20 : 03:52:28

The above problem of import is SOLVED.

Solution is in my SQL server "Microsoft Access Database Engine 2010 Redistributable" were not installed and after installation on server I was able to import the excel.

However, I want to make it dynamic as below exec command

"EXEC [ImportExcelFile] 'C:\temp\TestFiles\OPENASNs.xlsx','Sheet1', 'test', 'Create';"

please also confirm is my EXEC is correct, further condition.

1. If "Create" then Create new table

2. If the user specifies “Append”, the script must insert the data coming from the excel file to the designated SQL table.

3. If the user specifies “Reset” the script must delete or Drop table in SQL and insert the new data coming from the excel file.

When i execute below procedure for below code then it should create table

EXEC [ImportExcelFile] 'C:\temp\TestFiles\OPENASNs.xlsx','Sheet1', 'test', 'Create';

When i execute below procedure for below code then it should drop table and create new.

EXEC [ImportExcelFile] 'C:\temp\TestFiles\OPENASNs.xlsx','Sheet1', 'test', 'Reset';

When i execute below procedure for below code then it should insert into same table

EXEC [ImportExcelFile] 'C:\temp\TestFiles\OPENASNs.xlsx','Sheet1', 'test', 'Apend';

-----------------------------------------------

Below if my code

Drop PROCEDURE [dbo].[ImportExcelFile]
go

CREATE PROCEDURE [dbo].[ImportExcelFile]
(@TABLENAME varchar(1000)
,@SourceSheet varchar (100)
,@TypeOfUpdate varchar (100)
,@DestinationTable varchar (100))

as

declare @retval int --Remaing Value
EXEC master..xp_fileexist @TABLENAME, @retval output -- check if file exists

if @retval = 0
begin
print 'file does not exist.'
return
end

if @SourceSheet is null or @SourceSheet = ''
set @SourceSheet = '[Sheet1$]' -- assume that the Sheet name on excel file is the default name
else
set @SourceSheet = '[' + ltrim(rtrim(@SourceSheet)) + '$]'

if @DestinationTable is null or @DestinationTable = ''
set @DestinationTable = substring(@SourceSheet, 2, len(@SourceSheet) - 3) + convert(varchar, getdate(), 126)

--select @DestinationTable
--select @Source
--select @SourceSheet
--select ('select * into [' + @DestinationTable + '] from openrowset(''Microsoft.ACE.OLEDB.12.0'', ''Excel 12.0;HDR=YES;Database=' + @Source + ''', ' + ''' select * from '+@SourceSheet + ''')')

if @TypeOfUpdate='Create'

Begin

exec('select * into [' + @DestinationTable + '] from openrowset(''Microsoft.ACE.OLEDB.12.0'', ''Excel 12.0;HDR=YES;Database=' + @TABLENAME + ''', ' + ''' select * from '+@SourceSheet + ''')')

END

if @TypeOfUpdate='Reset'

Begin

exec('drop @DestinationTable''select * into [' + @DestinationTable + '] from openrowset(''Microsoft.ACE.OLEDB.12.0'', ''Excel 12.0;HDR=YES;Database=' + @TABLENAME + ''', ' + ''' select * from '+@SourceSheet + ''')')

END

/*
if @TypeOfUpdate='Append'
then

*/

------------------------------

please correct my code for the condition in order to work.

thanks for your support.

SQL_Support_2015

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources