locked
A function to collect FileSize (Given File Path as param) RRS feed

  • Question

  • Experts,

    Can I have a function that tells the size of a file given the file path as a parameter?

    example,  File path = 'C:\testFolder\hello.txt'

    so passing this value, can I get the file size? of hello.txt?

    thanks,


    ebro

    Monday, April 14, 2014 7:33 PM

All replies

  • The easiest way is do with Powershell

    function filesize { param([String] $path) try { if(Test-Path $path) { $size=(Get-Item $path).length/1024 write-host "$path size is $size KB" } } catch [System.Exception] { } write-host "File not found" } filesize -path c:\second.jpg

    OR Use T-SQL

    declare @line varchar(255)
    declare @path varchar(255)
    declare @command varchar(255)
    
    create table #output (line varchar(255))
    
    set @path = 'C:\Appleton Papers - 23 SQL DM - dani 6.28.13.pdf'
    set @command = 'dir "' + @path +'"'
    
    insert into #output
    exec master.dbo.xp_cmdshell @command
    
    select @line = ltrim(replace(substring(line, charindex(')', line)+1,len(line)), ',', ''))
    from #output where line like '%File(s)%bytes'
    
    print cast(replace(@line,'bytes','') as float)/1024
    
    drop table #output

    --Prashanth


    • Edited by Prashanth Jayaram Monday, April 14, 2014 8:12 PM Added T-SQL method
    • Proposed as answer by Sofiya Li Tuesday, April 15, 2014 9:37 AM
    Monday, April 14, 2014 7:58 PM
  • You could try this as well
    -- Ole Automation Procedures
    
    sp_configure 'show advanced',1
    go
    reconfigure
    sp_configure 'Ole Automation Procedures',1
    go
    reconfigure
    go
    
    
    
    --put the following code in SP
    
    declare @return        int 
    declare @fso           int
    declare @fso_file      int
    declare @fso_file_size int
    declare @Filename varchar(1000)
    select @Filename = 'C:\Windows\notepad.exe'
    execute @return = sp_OACreate
    	'Scripting.FileSystemObject',
    	@fso    output
    execute @return = sp_OAMethod
    	@fso,
    	'GetFile',
    	@fso_file    output,
    	@Filename
    execute @return = sp_OAGetProperty
    	@fso_file,
    	'Size',
    	@fso_file_size    output
    
    
    select file_size = @fso_file_size


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker


    • Edited by SimpleSQL Monday, April 14, 2014 8:02 PM
    • Proposed as answer by Sofiya Li Tuesday, April 15, 2014 9:37 AM
    Monday, April 14, 2014 8:02 PM
  • Hi Prash,

    Can I use the power shell function you gave me above by cross applying it with a table?

    My table has over 5 million rows and a column in the table has all file paths. I want the function to run for each of the 5 million file paths. I want to pass the column name of my table to the power shell function above so that I get the result for all.

    You think that will work?

    Much thanks!!

     


    ebro

    Tuesday, April 15, 2014 2:58 PM