How to read list of files in folder using T-SQL?

Question

Post reply

How to read list of files in folder using T-SQL?

eko indriyawan

Hall of Fame

Points: 3316
More actions
January 21, 2010 at 6:55 pm

#218408

Does anyone know how to read files from spesific folder/path using T-SQL?
Regards,
Eko Indriyawan

Viewing 15 posts - 1 through 15 (of 27 total)

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

WayneS SSC Guru Points: 95409 More actions · Answer 1

Try this:

declare @files table (ID int IDENTITY, FileName varchar(100))

insert into @files execute xp_cmdshell 'dir c:\ /b'

select * from @files

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

eko indriyawan Hall of Fame Points: 3316 More actions · Answer 2

Wow, excellent..

This is the query that I need.

Thank you for your answer.

King Regards,

Eko Indriyawan

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 3

Without xp_CmdShell...

EXEC Master.dbo.xp_DirTree 'yourpathhere',1,1

You can use INSERT/EXEC to capture the info in a table if you prebuild the table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

eko indriyawan Hall of Fame Points: 3316 More actions · Answer 4

Jeff Moden (1/21/2010)
Without xp_CmdShell...
EXEC Master.dbo.xp_DirTree 'yourpathhere',1,1
You can use INSERT/EXEC to capture the info in a table if you prebuild the table.

Hello Jeff Moden,

Thank you very much. Your query is so great.

Can you explain to what mean for the parameter 1,1?

King Regards,

Eko Indriyawan

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 5

Yes...

The first "1" controls how many "levels" to go down from the directory listed in quotes with the directory listed in the quotes being "Level 1". In other words, a "1" says "give me the info in the listed directory only".

The second "1" can be anything other than 0. Any non-zero value here says "Tell me if each row is a file or not using the ISFILE column."

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

eko indriyawan Hall of Fame Points: 3316 More actions · Answer 6

Jeff Moden (1/21/2010)
Yes...
The first "1" controls how many "levels" to go down from the directory listed in quotes with the directory listed in the quotes being "Level 1". In other words, a "1" says "give me the info in the listed directory only".
The second "1" can be anything other than 0. Any non-zero value here says "Tell me if each row is a file or not using the ISFILE column."

Hello Jeff Moden,

Thank you veru much for your explanation.

I've understand now. Your answer more simple and I do not need to active the xp command.

King Regards,

Eko Indriyawan

BOR15K SSCertifiable Points: 5876 More actions · Answer 7

Hello,

Can one advise how to obtain file's details, please? Both xp_dirtree and xp_cmdshell return expected list of files, but I also need their properties: size and date.

Is it possible, please? An only unpleasant idea I have is to run xp_cmdshell without parameters, to insert the result into a temporary table and then to process record by record (removing /B will return date and time first and then somewhere further down the line - the size and the name).

Kind Regards

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 8

BOR15K (9/20/2016)
Hello,
Can one advise how to obtain file's details, please? Both xp_dirtree and xp_cmdshell return expected list of files, but I also need their properties: size and date.
Is it possible, please? An only unpleasant idea I have is to run xp_cmdshell without parameters, to insert the result into a temporary table and then to process record by record (removing /B will return date and time first and then somewhere further down the line - the size and the name).
Kind Regards

I suppose you could make a call to PowerShell (I don't have the details how to do that from a proc of job without using xp_CmdShell but could be a job that causes PoSh to write the directory out to a file that you could import using BULK INSERT) but I'd just use output of xp_CmdShell as you say. If you do it right, the output table would have persisted computed columns that would do the "split" of information as soon as you load the table. Another option would be to use SP_OA* procs so that you can avoid everyone getting unnecessarily nasty about xp_CmdShell but SP_OA* will be slower and still requires deity privs (I do have a script for that somewhere. If you need it, I'll try to find it when I get home tonight). There is also a WMIC command that you can call with xp_CmdShell to give you all the information including the full path but I don't have it on the tip of my tongue. It's something that I was looking into and haven't written any code around yet.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110206 More actions · Answer 9

BOR15K (9/20/2016)
Hello,
Can one advise how to obtain file's details, please? Both xp_dirtree and xp_cmdshell return expected list of files, but I also need their properties: size and date.
Is it possible, please? An only unpleasant idea I have is to run xp_cmdshell without parameters, to insert the result into a temporary table and then to process record by record (removing /B will return date and time first and then somewhere further down the line - the size and the name).
Kind Regards

Well, you do not need to parse strings in temp table record by record.

You can do it in one go.

And even record by record will still be much faster than requesting file properties file by file, one OS call at the time.

_____________
Code for TallyGenerator

DesNorton SSC-Insane Points: 24276 More actions · Answer 10

BOR15K (9/20/2016)
Hello,
Can one advise how to obtain file's details, please? Both xp_dirtree and xp_cmdshell return expected list of files, but I also need their properties: size and date.
Is it possible, please? An only unpleasant idea I have is to run xp_cmdshell without parameters, to insert the result into a temporary table and then to process record by record (removing /B will return date and time first and then somewhere further down the line - the size and the name).
Kind Regards

This is a bit dirty, but it will do the trick.

DECLARE @FolderName VARCHAR(256) = 'C:\';

DECLARE @DirCmd VARCHAR(300);

-- Create a table to hold a list of file names

CREATE TABLE #FileNames (

[FileName] VARCHAR(256)

);

-- Populate the list of file names

SET @DirCmd = 'dir ' + @FolderName + ' /b';

INSERT INTO #FileNames([FileName])

execute xp_cmdshell @DirCmd;

-- Clean up NULLS

DELETE #FileNames WHERE [FileName] IS NULL;

-- Create a table to hold a list of folders and file info

CREATE TABLE #FileData (

ID INT IDENTITY(1,1)

, RawData VARCHAR(512)

, [Type] VARCHAR(4)

, Name VARCHAR(256)

, LastUpdated SMALLDATETIME

, FileSize BIGINT

);

-- Populate the list of folders and file info

SET @DirCmd = 'dir ' + @FolderName;

INSERT INTO #FileData(RawData)

execute xp_cmdshell @DirCmd;

-- Clean up NULLS and Header|Footer data

DELETE #FileData WHERE RawData IS NULL;

DELETE #FileData WHERE RawData LIKE ' Directory of %';

DELETE #FileData WHERE RawData LIKE ' Volume %';

DELETE #FileData WHERE RawData LIKE '%File(s)%';

DELETE #FileData WHERE RawData LIKE '%Dir(s)%';

-- Shred the data into usable info

-- NOTE the CHAR(160) is due to the "space" between the digits not being an actual SPACE (CHAR(32))

UPDATE #FileData

SET [Type] = CASE WHEN PATINDEX('%<DIR>%', RawData) > 0 THEN 'DIR' ELSE 'FILE' END

, LastUpdated = LEFT(RawData, 21)

, Name = CASE WHEN PATINDEX('%<DIR>%', fd.RawData) > 0

THEN LTRIM(SUBSTRING(fd.RawData, PATINDEX('%<DIR>%', fd.RawData)+5, 512))

ELSE fn.[FileName]

END

, FileSize = CAST(REPLACE(REPLACE(CASE WHEN PATINDEX('%<DIR>%', fd.RawData) > 0

THEN '0'

ELSE SUBSTRING(fd.RawData, 21, LEN(fd.RawData) - LEN(fn.[FileName]) - 21)

END, ' ', ''), CHAR(160), '') AS BIGINT)

FROM #FileData AS fd

LEFT JOIN #FileNames AS fn

ON ' ' + fn.[FileName] = RIGHT(fd.RawData, LEN(' ' + fn.[FileName]));

SELECT * FROM #FileData AS fd;

DROP TABLE #FileNames;

DROP TABLE #FileData;

How to post data/code on a forum to get the best help.

BOR15K SSCertifiable Points: 5876 More actions · Answer 11

BOR15K

SSCertifiable

Points: 5876

September 21, 2016 at 8:41 am

#1902196

Thank you. I thought it would be that way 🙂

Charles Bilodeau-369032 SSC-Addicted Points: 468 More actions · Answer 12

That is all good but is it possible to do it without using CMDSHELL?

BOR15K SSCertifiable Points: 5876 More actions · Answer 13

Charles Bilodeau-369032 - Monday, June 25, 2018 6:09 AM
That is all good but is it possible to do it without using CMDSHELL?

Purely speculating, but I presume you could write something in .Net and only call it from SQL

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 14

Charles Bilodeau-369032 - Monday, June 25, 2018 6:09 AM
That is all good but is it possible to do it without using CMDSHELL?

Other than people's severe misperception of xp_CmdShell being a security issue, what's the problem with using xp_CmdShell? It can be used very safely and without making the mistake of granting the ability to users to execute it directly.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)