Importing Multiple Excel Files using TSQL

Question

Post reply

Importing Multiple Excel Files using TSQL

kevaburg

SSCoach

Points: 18131
More actions
January 26, 2018 at 8:45 am

#335395

Hi Folks,
So, here is my goal this time: To import multiple Excel files into a database using TSQL. Whether it is a good idea or not and whether or not others can do it with SSIS (and I can already) is not relevant here. I am trying to put myself in a position where tasks I can perform with SIS I can also do with TSQL. An exercise if you like.
I have written this import routine that I think should work. It doesn't of course otherwise I wouldn't be here!
alter procedure dbo.usp_IMPORT_T1 as
declare @path nvarchar(255);
declare @files table (filename nvarchar(255), depth int, [file] int);
declare @filename nvarchar(50);
declare @opendatasource1 nvarchar(100);
declare @opendatasource2 nvarchar(100);
declare @execimport nvarchar(1000);
--Set the variables for the construction of the dynamic SQL
select @path = 'C:\_IMPORT\Templates\Standard\';
select @opendatasource1 = 'INSERT INTO [stage].[tbl_TimeTracking](
                   [EmployeeName]
                   ,[Entity]
                   ,[Matter]
                   ,[Workstream]
                   ,[EngagementCode]
                   ,[TaskDate]
                   ,[Hours]
                   ,[Location_of_hours_worked]
                   ,[Note]
                   ,[SytemMessage]
                   )
             SELECT * FROM OPENDATASOURCE(@Microsoft.ACE.OLEDB.12.0'',Data Source=''' + @path + @filename
select @opendatasource2 =';Extended Properties=Excel 12.0'')...[Time$]'

--Construct the import command
set @execimport = @opendatasource1 + @opendatasource2;
--Populate the table variable with the names of the files in the directory
insert into @files
exec xp_dirtree @path, 0,1;

declare T1_Import cursor local
for
select [filename]
from @files
where [file] = 1
  and depth = 1
open T1_Import
fetch next from T1_Import into @filename
while @@fetch_status = 0

  begin
   print @filename
   exec sp_executesql @execimport
  end;
close T1_Import;
deallocate T1_Import;
Unfortunately all it does is cycle through one file endlessly without importing it. Here is the output:
November-Infrastructure.xlsx
November-Infrastructure.xlsx
November-Infrastructure.xlsx
November-Infrastructure.xlsx
November-Infrastructure.xlsx
November-Infrastructure.xlsx
November-Infrastructure.xlsx
November-Infrastructure.xlsx
November-Infrastructure.xlsx
November-Infrastructure.xlsx
November-Infrastructure.xlsx
November-Infrastructure.xlsx
November-Infrastructure.xlsx
November-Infrastructure.xlsx
November-Infrastructure.xlsx
November-Infrastructure.xlsx
November-Infrastructure.xlsx
Upon checking the table I see that no data has been inserted.
There are no error messages and no clues (at least that I can find) as to why this isn't working.
Does anyone have any ideas please?
Thanks in advance!
Regards;
Kev

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246986 More actions · Answer 1

This looks wrong:

SELECT * FROM OPENDATASOURCE(@

is the @ sign in your actual code?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

kevaburg SSCoach Points: 18131 More actions · Answer 2

Phil Parkin - Friday, January 26, 2018 10:13 AM
This looks wrong:
SELECT * FROM OPENDATASOURCE(@
is the @ sign in your actual code?

Hi Phil,

thanks for the correction. It was indeed in the code and I have now removed it but it still doesn't work.

Now that I have run the procedure again I still don't receive an error but it still doen't insert any data. It doesn't even seem to cycle through the files and there are around 20 of them.

Regards,
Kev

Phil Parkin SSC Guru Points: 246986 More actions · Answer 3

kevaburg - Friday, January 26, 2018 10:26 AM
Phil Parkin - Friday, January 26, 2018 10:13 AM
This looks wrong:
SELECT * FROM OPENDATASOURCE(@
is the @ sign in your actual code?
Hi Phil,
thanks for the correction. It was indeed in the code and I have now removed it but it still doesn't work.
Now that I have run the procedure again I still don't receive an error but it still doen't insert any data.
Regards,
Kev

Does the SELECT work if you run it in isolation?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

kevaburg SSCoach Points: 18131 More actions · Answer 4

Phil Parkin - Friday, January 26, 2018 10:28 AM
kevaburg - Friday, January 26, 2018 10:26 AM
Phil Parkin - Friday, January 26, 2018 10:13 AM
This looks wrong:
SELECT * FROM OPENDATASOURCE(@
is the @ sign in your actual code?
Hi Phil,
thanks for the correction. It was indeed in the code and I have now removed it but it still doesn't work.
Now that I have run the procedure again I still don't receive an error but it still doen't insert any data.
Regards,
Kev
Does the SELECT work if you run it in isolation?

It does, perfectly. I have even printed the dynamic SQL to ensure the code is correct and checked the file name insert into the table variable to ensure it works. And unfortunately, it does.

I can only imagine I have done something peculiar in the Cursor.

Phil Parkin SSC Guru Points: 246986 More actions · Answer 5

I think that you need to be updating the value of @execimport within your While loop, otherwise the same dynamic SQL will be executed repeatedly.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

kevaburg SSCoach Points: 18131 More actions · Answer 6

Phil Parkin - Friday, January 26, 2018 11:43 AM
I think that you need to be updating the value of @execimport within your While loop, otherwise the same dynamic SQL will be executed repeatedly.

That would certainly make sense looking at it. I need some help implementing it though.

open T1_Import;

fetch next from T1_Import into @filename

while @@fetch_status = 0

begin

--print @filename

exec sp_executesql @execimport

end;

Do you mean I should put the while Loop @@fetch_status inside the block?

Phil Parkin SSC Guru Points: 246986 More actions · Answer 7

kevaburg - Friday, January 26, 2018 11:53 AM
Phil Parkin - Friday, January 26, 2018 11:43 AM
I think that you need to be updating the value of @execimport within your While loop, otherwise the same dynamic SQL will be executed repeatedly.
That would certainly make sense looking at it. I need some help implementing it though.
open T1_Import;
fetch next from T1_Import into @filename
while @@fetch_status = 0
begin
--print @filename
exec sp_executesql @execimport
end;
Do you mean I should put the while Loop @@fetch_status inside the block?

I mean that the value of @execimport is not changing for every iteration of the loop. I presume that you want it to change, otherwise the same SQL will be executed every time. So you need to modify the value of @execimport within the WHILE loop.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

kevaburg SSCoach Points: 18131 More actions · Answer 8

Phil Parkin - Friday, January 26, 2018 11:57 AM
kevaburg - Friday, January 26, 2018 11:53 AM
Phil Parkin - Friday, January 26, 2018 11:43 AM
I think that you need to be updating the value of @execimport within your While loop, otherwise the same dynamic SQL will be executed repeatedly.
That would certainly make sense looking at it. I need some help implementing it though.
open T1_Import;
fetch next from T1_Import into @filename
while @@fetch_status = 0
begin
--print @filename
exec sp_executesql @execimport
end;
Do you mean I should put the while Loop @@fetch_status inside the block?
I mean that the value of @execimport is not changing for every iteration of the loop. I presume that you want it to change, otherwise the same SQL will be executed every time. So you need to modify the value of @execimport within the WHILE loop.

Cool....I have an idea. When I have it sorted I will post it here.

Cheers Phil.....it was one of those situations where I couldn't see wood for trees!

subramaniam.chandrasekar SSCarpal Tunnel Points: 4223 More actions · Answer 9

kevaburg - Friday, January 26, 2018 8:45 AM
Hi Folks,
So, here is my goal this time: To import multiple Excel files into a database using TSQL. Whether it is a good idea or not and whether or not others can do it with SSIS (and I can already) is not relevant here. I am trying to put myself in a position where tasks I can perform with SIS I can also do with TSQL. An exercise if you like.
I have written this import routine that I think should work. It doesn't of course otherwise I wouldn't be here!
alter procedure dbo.usp_IMPORT_T1 as
declare @path nvarchar(255);
declare @files table (filename nvarchar(255), depth int, [file] int);
declare @filename nvarchar(50);
declare @opendatasource1 nvarchar(100);
declare @opendatasource2 nvarchar(100);
declare @execimport nvarchar(1000);
--Set the variables for the construction of the dynamic SQL
select @path = 'C:\_IMPORT\Templates\Standard\';
select @opendatasource1 = 'INSERT INTO [stage].[tbl_TimeTracking](
                   [EmployeeName]
                   ,[Entity]
                   ,[Matter]
                   ,[Workstream]
                   ,[EngagementCode]
                   ,[TaskDate]
                   ,[Hours]
                   ,[Location_of_hours_worked]
                   ,[Note]
                   ,[SytemMessage]
                   )
             SELECT * FROM OPENDATASOURCE(@Microsoft.ACE.OLEDB.12.0'',Data Source=''' + @path + @filename
select @opendatasource2 =';Extended Properties=Excel 12.0'')...[Time$]'

--Construct the import command
set @execimport = @opendatasource1 + @opendatasource2;
--Populate the table variable with the names of the files in the directory
insert into @files
exec xp_dirtree @path, 0,1;

declare T1_Import cursor local
for
select [filename]
from @files
where [file] = 1
  and depth = 1
open T1_Import
fetch next from T1_Import into @filename
while @@fetch_status = 0

  begin
   print @filename
   exec sp_executesql @execimport
  end;
close T1_Import;
deallocate T1_Import;
Unfortunately all it does is cycle through one file endlessly without importing it. Here is the output:
November-Infrastructure.xlsx
November-Infrastructure.xlsx
November-Infrastructure.xlsx
November-Infrastructure.xlsx
November-Infrastructure.xlsx
November-Infrastructure.xlsx
November-Infrastructure.xlsx
November-Infrastructure.xlsx
November-Infrastructure.xlsx
November-Infrastructure.xlsx
November-Infrastructure.xlsx
November-Infrastructure.xlsx
November-Infrastructure.xlsx
November-Infrastructure.xlsx
November-Infrastructure.xlsx
November-Infrastructure.xlsx
November-Infrastructure.xlsx
Upon checking the table I see that no data has been inserted.
There are no error messages and no clues (at least that I can find) as to why this isn't working.
Does anyone have any ideas please?
Thanks in advance!
Regards;
Kev

Thanks for your query, but if you could use SSIS in place of these large SQL scripts it will help you in performance & saves a lot of time.
Appreciate your try.

kevaburg SSCoach Points: 18131 More actions · Answer 10

subramaniam.chandrasekar - Sunday, January 28, 2018 9:55 PM
kevaburg - Friday, January 26, 2018 8:45 AM
Hi Folks,
So, here is my goal this time: To import multiple Excel files into a database using TSQL. Whether it is a good idea or not and whether or not others can do it with SSIS (and I can already) is not relevant here. I am trying to put myself in a position where tasks I can perform with SIS I can also do with TSQL. An exercise if you like.
I have written this import routine that I think should work. It doesn't of course otherwise I wouldn't be here!
alter procedure dbo.usp_IMPORT_T1 as
declare @path nvarchar(255);
declare @files table (filename nvarchar(255), depth int, [file] int);
declare @filename nvarchar(50);
declare @opendatasource1 nvarchar(100);
declare @opendatasource2 nvarchar(100);
declare @execimport nvarchar(1000);
--Set the variables for the construction of the dynamic SQL
select @path = 'C:\_IMPORT\Templates\Standard\';
select @opendatasource1 = 'INSERT INTO [stage].[tbl_TimeTracking](
                   [EmployeeName]
                   ,[Entity]
                   ,[Matter]
                   ,[Workstream]
                   ,[EngagementCode]
                   ,[TaskDate]
                   ,[Hours]
                   ,[Location_of_hours_worked]
                   ,[Note]
                   ,[SytemMessage]
                   )
             SELECT * FROM OPENDATASOURCE(@Microsoft.ACE.OLEDB.12.0'',Data Source=''' + @path + @filename
select @opendatasource2 =';Extended Properties=Excel 12.0'')...[Time$]'

--Construct the import command
set @execimport = @opendatasource1 + @opendatasource2;
--Populate the table variable with the names of the files in the directory
insert into @files
exec xp_dirtree @path, 0,1;

declare T1_Import cursor local
for
select [filename]
from @files
where [file] = 1
  and depth = 1
open T1_Import
fetch next from T1_Import into @filename
while @@fetch_status = 0

  begin
   print @filename
   exec sp_executesql @execimport
  end;
close T1_Import;
deallocate T1_Import;
Unfortunately all it does is cycle through one file endlessly without importing it. Here is the output:
November-Infrastructure.xlsx
November-Infrastructure.xlsx
November-Infrastructure.xlsx
November-Infrastructure.xlsx
November-Infrastructure.xlsx
November-Infrastructure.xlsx
November-Infrastructure.xlsx
November-Infrastructure.xlsx
November-Infrastructure.xlsx
November-Infrastructure.xlsx
November-Infrastructure.xlsx
November-Infrastructure.xlsx
November-Infrastructure.xlsx
November-Infrastructure.xlsx
November-Infrastructure.xlsx
November-Infrastructure.xlsx
November-Infrastructure.xlsx
Upon checking the table I see that no data has been inserted.
There are no error messages and no clues (at least that I can find) as to why this isn't working.
Does anyone have any ideas please?
Thanks in advance!
Regards;
Kev
Thanks for your query, but if you could use SSIS in place of these large SQL scripts it will help you in performance & saves a lot of time.
Appreciate your try.

If you read my post at the very beginning you would understand why I am writing the import in TSQL. Another point is that SSIS is not always performance-wise better and another is that not everybody understands the construction of a SSIS package or project. I am a firm believer in not relying on "click-and-drag" for every task because they still lack the flexibility of hand written TSQL most of the time.

The most important aspect about resolving this issue with the code is to better my understanding of the underlying processes regarding the import of an Excel workbook with several tabs.

Oh, and if you think that script is large....once this import is completed it will be mofified to accommodate the import of 15 other tabs in the workbook, plus the movement of a file based on import success or failure and the triggering of an Email to send the results to a mailing list.

Yes, SSIS would be faster in the development but is it the best solution? As I mentioned, the solution should be one that everybody understands and TSQL is the lowest common denominator that every DBA should understand.