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

  • Does anyone know how to read files from spesific folder/path using T-SQL?

    Regards,

    Eko Indriyawan

  • 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

  • Wow, excellent..

    This is the query that I need.

    Thank you for your answer.

    King Regards,

    Eko Indriyawan

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • 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

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • 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

  • 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

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • 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.

  • 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;

  • Thank you. I thought it would be that way 🙂

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

  • 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

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

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

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