Search This Blog

Tuesday, December 10, 2013

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

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 referance 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$]')


5% OFF All Lenovo ThinkPad's. Coupon Code:CJDEC5UK Lenovo UK Homepage www.1and1.co.uk
5% OFF All Lenovo ThinkPad's.