read file names from folder

Question

Post reply

read file names from folder

APIJENA

SSC Veteran

Points: 296
More actions
January 28, 2010 at 5:51 pm

#216100

Hi,
I want to get the list of file names in a folder then pass name as a parameter in a loop.
something like but i m not getting the exact names ! thanks
DECLARE @tabfileNames TABLE (fName varchar(100) )
DECLARE @i int
SET @i=1
INSERT INTO @tabfileNames
exec master.dbo.xp_cmdshell 'dir c:\CIC\.jpg'
while @i <10
begin
select *
from @tabfileNames
set @i=@i+1

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

i think it's just your DIR command:

exec master.dbo.xp_cmdshell 'dir c:\CIC\*.jpg /b'

*.jpg for each jpg file

/b for the brief directory style...just the file name

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

APIJENA SSC Veteran Points: 296 More actions · Answer 2

Merci beaucoup Lowell !

do you have any suggestion how can I loop to pass names one by name? should I use a cursor or is there any better way?

thanks again .

Lowell SSC Guru Points: 323518 More actions · Answer 3

it really depends on what you are doing with the filename; they came into your temp table in a set based operation...could be thousands of file names potentially;

your example did not say what you are doing, it was just selecting the file name one at a time;

what are you trying to do with them? load them as binaries into the database? all via tsql, i think your stuck with looping;

sort them or something/ that'd be set based

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

APIJENA SSC Veteran Points: 296 More actions · Answer 4

Thanks again Lowell

actually, I need only XML files name to pass them to a script which shred them into sql server.

I already wrote a dynamic script and it works fine. so I just need to loop in the table names and pass them to the dynamic script. Thanks for your help

DECLARE @xmlDoc XML

SET @xmlDoc = (

SELECT * FROM OPENROWSET (

BULK 'C:\bidon\xml_test.xml', SINGLE_CLOB

) AS xmlData

)

SELECT @xmlDoc

Lowell SSC Guru Points: 323518 More actions · Answer 5

yep, since openrowset doesn't accept variables,(?right?) you'll be stuck with a cursor to go thru the table of file names, and dynamic SQL to do the openrowset and shredding of the xml; do you need a cursor example, or just a confirmation of your plan?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

APIJENA SSC Veteran Points: 296 More actions · Answer 6

Lowell, Yes if you have an example will help for sure. Thanks

Mitesh Oswal SSCrazy Points: 2798 More actions · Answer 7

DECLARE @tbl TABLE

(

File_Names VARCHAR(100)

)

INSERT INTO @tbl

exec master.dbo.xp_cmdshell 'dir c:\*.xml /b'

SELECT * FROM @tbl WHERE File_NamesLIKE'%xml%'

Regards,
Mitesh OSwal
+918698619998

APIJENA SSC Veteran Points: 296 More actions · Answer 8

APIJENA

SSC Veteran

Points: 296

January 29, 2010 at 7:15 am

#1110705

Thank you very much, I really appreciate it