using the command - xp_cmdshell

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

using the command - xp_cmdshell

Author

Topic

theboyholty
Posting Yak Master

226 Posts

Posted - 2011-04-19 : 06:47:32

I've been trying to get a listing of the text files within a network directory. The files are called one.txt, two.txt etc up to six.txt.

I used the following code to get the file names, pop them in a table and removed the gunk I don't need.

CREATE TABLE #TryThis (FileNames VARCHAR(4000))
INSERT INTO #Trythis
EXEC  MASTER..xp_cmdshell 'dir "\\svm12\client data team\NickH\text files\*.txt" /D'
DELETE FROM #TryThis WHERE Filenames NOT LIKE '%.txt'
DELETE FROM #TryThis WHERE Filenames IS NULL
SELECT * FROM #TryThis

The output I get back is:
five.txt one.txt three.txt
four.txt six.txt two.txt

I was hoping for one row for each text file, but as you can see its put three in one row and three in another.

How do I tweak my code so its outputs into six separate rows?

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk The official unofficial website of Bury Football Club

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-19 : 07:22:31

CREATE TABLE #TryThis (FileNames VARCHAR(4000))
INSERT INTO #Trythis
EXEC MASTER..xp_cmdshell 'dir /B "\\svm12\client data team\NickH\text files\*.txt"'
DELETE FROM #TryThis WHERE Filenames NOT LIKE '%.txt'
DELETE FROM #TryThis WHERE Filenames IS NULL
SELECT * FROM #TryThis

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

theboyholty
Posting Yak Master

226 Posts

Posted - 2011-04-19 : 12:28:55

That was a cheeky little '/B' you sneaked into the code there. Seems to have done the trick. Cheers bud.

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk The official unofficial website of Bury Football Club

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources