Using EXEC master..xp_cmdshell 1

[TonyFoo]

Hi Everyone,

I am using the cmdshell tool to try to pull in the filenames of all excel files in a directory. My code is as follows:

Declare @Files Table ([File] varchar(250) null)
INSERT @Files ([File])
EXEC master..xp_cmdshell 'DIR C:\current\*.xls'

For some reason it also brings in information such as drive volume, drive volume serial number, and other statistics. How do i just pull the excel file names?

Thank you in advance!

 

[TonyFoo]

Ok, I am now using:

EXEC master..xp_cmdshell 'DIR C:\current\*.xls /b'

but now it is returning one null row.

Also, this code is going to be in an SQL stored procedure where i want to capture all of the file names in that folder.

Thanks again.

 

[mrdenny]

You should take a look at the procedure xp_dirtree. This way you don't have to use xp_cmdshell which is a security risk to use.

More info can be found here:

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1270919,00.html

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog