How to list directory contents?

[OLAPer]

Hello

What I want to do is retrieve the contents of a directory, from which I will import data. The reason I want to do a listing is that the number of files may vary, although all of them will be CSV format.

I want to then loop round and import each one.

I know that I can use:

master..xp_cmdshell

However, two things:

a). How do I get the output of the above command into table or working array?

b). I was wondering whether there was a different more direct way of doing this.

I look forward to hearing your ideas,

kindest regards


OLAPer
:

 

[tlbroadbent]

You can create a temp table and insert the results of xp_cmdshell into it.

set nocount on
Create table #dir(cmdoutput varchar(240))
Insert #dir
exec master..xp_cmdshell 'dir e:\mssql7\import\*.csv'

select filename=substring(cmdoutput,40,80)
from #dir
where charindex('.csv',cmdoutput)>0

drop table #dir

You can also use OLE automation objects to access the windows file system. See sp_OACreate and related topics in SQL BOL. I don't have a ready example to post for you. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.