quote:

---

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].

---

set nocount on
go

create table #test_file
(filee	int,
filed	int,
pd	int)
go

insert into #test_file
exec xp_fileexist 'R:\Contact centre ICM reports\t16062004peragt04.xls'

if not exists (select 1 from #test_file where filee = 1)
	begin
	print 'no file'
end
else
	begin
	print 'file found'
	SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
	  'Data Source="R:\Contact centre ICM reports\t16062004peragt04.xls";Extended properties=Excel 8.0')...[Agent_call_stats_14_June_2004$]
end

CREATE              Procedure usp_UploadExcelFile 
 @Folder_Name varchar(256)
 ,@File_Name varchar(256)
 ,@SessionId int
 ,@Worksheet varchar(20)
as
begin
 if exists (select * from sysobjects where name = 'Temp_Upload_table' and type = 'U' )
 begin
  drop table Temp_Upload_table
 end
 DECLARE 
  @Query varchar(800) 
  ,@Error_Msg varchar(200)
 select @query ='create procedure usp_excel_upload as select
    IDENTITY(INT,1,1) AS RowId 
    ,* 
   into 
   Temp_Upload_Table 
   from OpenDataSource( '+''''+'Microsoft.Jet.OLEDB.4.0'+''''+','+''''+'Data Source="'
   +@Folder_Name
   +@File_Name
   +'";User ID=;Password=;Extended properties="Excel 8.0;IMEX=1"'+'''' + ')...'+@Worksheet+'$'
 exec (@query)
 exec usp_excel_upload
  
end

ALTER               Procedure usp_UploadExcelFile 
 @Folder_Name varchar(256)
 ,@File_Name varchar(256)
 ,@SessionId int
 ,@Worksheet varchar(20)
as
begin
 if exists (select * from sysobjects where name = 'Temp_Upload_table' and type = 'U' )
 begin
  drop table Temp_Upload_table
 end
 DECLARE 
  @Query varchar(800) 
  ,@Error_Msg varchar(200)
 select @query ='create procedure usp_excel_upload as select
    IDENTITY(INT,1,1) AS RowId 
    ,* 
   into 
   Temp_Upload_Table 
   from OpenDataSource( '+''''+'Microsoft.Jet.OLEDB.4.0'+''''+','+''''+'Data Source="'
   +@Folder_Name
   +@File_Name
   +'";User ID=;Password=;Extended properties="Excel 8.0;IMEX=1"'+'''' + ')...'+@Worksheet+'$'
 exec (@query)
 exec usp_excel_upload
 
 if (@@error <> 0)
 Begin
  
  select 
   @Error_Msg =  'An unhandled error occured during upload of file "'
     +@File_Name
     +'". Kindly validate the file and try again.' 
  insert into 
   Error_log 
  select 
   @SessionId
   ,'2'
   ,@Error_Msg
   ,@File_Name
   ,getdate()
 End
 Drop Procedure usp_excel_upload
 
end

CREATE TABLE [Error_Log] (
	[Session_id] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Error_Code] [int] NULL ,
	[Error_Desc] [varchar] (396) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[File_Name] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Date] [datetime] NULL 
) ON [PRIMARY]
GO

quote:

---

Server: Msg 170, Level 15, State 1, Procedure usp_excel_upload, Line 6
Line 6: Incorrect syntax near '.17062004'.
Server: Msg 2812, Level 16, State 62, Line 25
Could not find stored procedure 'usp_excel_upload'.

---

exec usp_UploadExcelFile '\\reports\reports\test_e\','17062004pergat04.xls',1,'17062004pergat04'

set nocount on
go
declare @filename	varchar(250)
declare @sheetname	varchar(100)
--declare @dateformat	varchar(8)
declare @dir		varchar(1250)
--select @dateformat = cast((datepart(day,getdate()) -1) as char(2))
--	+substring(cast((datepart(month,getdate()) +100) as char(3)),2,2)
--	+cast(datepart(year,getdate()) as char(4))
set @filename = '\\reports\reports\test_e\a'+cast((datepart(day,getdate()) -1) as char(2))
	+substring(cast((datepart(month,getdate()) +100) as char(3)),2,2)
	+cast(datepart(year,getdate()) as char(4))+'peragt04.xls'
--set @filename = 'c:\2020 data\test16062004peragt04.xls'
--set @sheetname = 'sheet1$'
--select @filename
--select @sheetname
set @dir= 'dir \\reports\reports\test_e\a'+cast((datepart(day,getdate()) -1) as char(2))
	+substring(cast((datepart(month,getdate()) +100) as char(3)),2,2)
	+cast(datepart(year,getdate()) as char(4))+'peragt04.xls'
select @dir
select 'dir \\reports\reports\test_e\a17062004pergat04.xls'
exec master..xp_cmdshell 'dir \\reports\reports\test_e\a17062004pergat04.xls'
--this works
exec master..xp_cmdshell @dir
--this does not

dir \\reports\reports\test_e\a17062004peragt04.xls
                                                   
-------------------------------------------------- 
dir \\reports\reports\test_e\a17062004pergat04.xls

output                                                                          Volume in drive \\reports\reports has no label.
 Volume Serial Number is 0E2F-CEB4
NULL
 Directory of \\reports\reports\test_e
NULL
2004/06/18  12:44p              23,552 a17062004pergat04.xls
               1 File(s)         23,552 bytes
               0 Dir(s)   6,030,200,832 bytes free
NULL

output                                                                          Volume in drive \\reports\reports has no label.
 Volume Serial Number is 0E2F-CEB4
NULL
 Directory of \\reports\reports\test_e
NULL
File Not Found
NULL

if exists (select 1 from sysobjects where name = 'usp_Import_ExcelFile_TempTable' and xtype = 'p')
	drop procedure usp_Import_ExcelFile_TempTable

set nocount on

go

create procedure usp_Import_ExcelFile_TempTable
@filename varchar(250),
@sheetname varchar(100) = 'Sheet1$',
@succeed int OUTPUT
as
begin
set @succeed = -1 --failure
set nocount on
if exists (select 1 from sysobjects where name = 'excel_data_inserted' and xtype = 'u')
	drop table dbo.excel_data_inserted
declare @sql		varchar(500)
declare @filexists	int

set @sql = 'SELECT * INTO dbo.excel_data_inserted FROM OpenDataSource( ''Microsoft.Jet.OLEDB.4.0'',
	  ''Data Source="'+@filename+'";Extended properties=Excel 8.0'')...['+@sheetname+']'
exec master..xp_fileexist @filename, @filexists OUT
if (@filexists <> 1)
	print 'No file'
else
	begin
	set @succeed = 0 --success
--	print @succeed
--	print @sql
	exec (@sql)
	end
end

set nocount on
go
declare @filename	varchar(250)
declare @fullname	varchar(250)
declare @dateformat	char(8)
declare @dir		varchar(250)
declare @suc		int

select @dateformat = convert(char(2),(datepart(day,getdate()) -2))
	+substring(convert(char(3),(datepart(month,getdate()) +100) ),2,2)
	+convert(char(4),datepart(year,getdate()))
--CHANGE the SECTION before @dateformat to the relevant spreadsheet, eg PERAGT04 or PERAGT05 or PERAGT06
select @fullname = 'peragt06'+@dateformat
print @fullname
select @filename = '\\reports\reports\Contact centre ICM reports\'+@fullname+'.xls'
select @filename
select @fullname = ''+@fullname+'$'
select @fullname


--change the SHEET1 to the specific sheet name for the 
--per files. eg PERAGT0421062004.
--I am working on a dynamic version of that - shouldn't be hard
exec usp_Import_ExcelFile_TempTable @filename,@fullname,@suc output
--select @suc
if @suc = 0 
	select * from dbo.excel_data_inserted