CODEWITHSUNDEEP
sql-servert-sql
PKirby
PKirby

Reputation: 879

Select ONLY folders that contain files

I currently retrieve the folder structure and create a database with each folder contained in the Parent folder. The problem is, when there is a folder that does not contain any files, it falls over.

INSERT INTO DBName
EXECUTE XP_CMDSHELL 'dir \\FAS-RBGFS01\costec\FTP\ /b'

This brings back a list of ALL folders regardless if it has data in or not:

output
CAD
CEN
CRO
EXC
FRM
IND
LGL
RSK
ZZZ
NULL

Folder "CAD" does not contain any files.

How do I only select the folders which contain files/data in them?

Thank you in advance

Upvotes: 0

Views: 470

Answers (1)

Rahul
Rahul

Reputation: 77886

You can't achieve it using dir command. Rather you can use a batch script and call that batch script with XP_CMDSHELL like below.

Taken from how-to-list-all-the-empty-directories-using-windows-batch-file

You can have the below script saved in a file named test.bat

@echo off
dir /a /b %1 2>nul | findstr "^" >nul || echo %%~fA
for /f "eol=: delims=" %%A in ('dir /s /ad /b %1') do (
  dir /a /b "%%~fA" 2>nul | findstr "^" >nul || echo %%~fA
)

Then call it like

INSERT INTO DBName
EXECUTE XP_CMDSHELL 'test.bat \\FAS-RBGFS01\costec\FTP\'

Upvotes: 1

Related Questions