Share via

Comparing File Names in a directory with the list in Excel File

  • Article

Here is the Script...

---- Get File Names rom you directory

Declare @vOSFiles Table (MyFileName nvarchar(1000),MyDepth int, IsFile bit)

Insert into @vOSFiles

       EXEC xp_dirtree 'G:\Atif\, 1, 1

--Select * from @vOSFiles where IsFile = 1

---- Get List of Files from Excel

----  Insert Excel File list to another Temp Table.

---- The structure o fthis table depends upon you excel file and requirement

Declare @vExcelFileList Table (MyFileName nvarchar(1000))

---- This is for xlsx files

Insert into @vExcelFileList

       SELECT [MyFileName] FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',

       'Excel 12.0 Xml;Database=G:\Budjet.xlsx;HDR=Yes;IMEX=1',

       'SELECT * FROM [Sheet2$]')

--Select *

--from @vOSFiles

---- Join the two tables on the File Names

Select a.*, case when b.MyFileName is not Null then 'Exists' Else 'Not Exists' end as FileExistance

from @vOSFiles a

Left Outer Join @vExcelFileList b on b.MyFileName = a.MyFileName

Where a.IsFile = 1

You might want to enable ad hoc query options;

---- Enable AdHoc Queries

--USE MSDB

--GO

--sp_configure 'show advanced options', 1;

--GO

--RECONFIGURE;

--GO

--sp_configure 'Ad Hoc Distributed Queries', 1;

--GO

--RECONFIGURE;

--GO

---- Add OLEDB reference for xlsx

--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

For XLS files (earlier 2007 versions of Excel), you can run the OpenRowset query as;

SELECT * FROM  OPENROWSET('Microsoft.Jet.OLEDB.4.0'

,'Excel 4.0;Database=G:\Budjet.xls','SELECT * FROM [sheet2$]')