SQL Puzzle | Get File Size using T-SQL

This is a really cool puzzle. Here you to accept a file location as a parameter and provide the size of the file.

For details please check out the sample input and the expected output below-

Sample Inputs

DECLARE @FileLocation AS VARCHAR(120) = ‘C\a.CSV’

Expected Output

Note – XX will be size of the your csv file

Rules/Restrictions

• Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script | use below script to create table & insert some sample data

--

DECLARE @FileLocation VARCHAR(120) = 'E:\pawan.csv'

--

Solution 1

--

DECLARE @FileLocation VARCHAR(120) = 'E:\a.csv'

SET @FileLocation = 'dir "' + @FileLocation +'"'

IF OBJECT_ID('tempdb..#tempFilePaths') IS NOT NULL
DROP TABLE #tempFilePaths

CREATE TABLE #tempFilePaths (Files VARCHAR(500))
 
INSERT INTO #tempFilePaths
EXEC XP_CMDSHELL @FileLocation

SELECT CAST(LTRIM(RTRIM(SUBSTRING(Files,CHARINDEX('File(s)',(Files)) + LEN('File(s)'),  CHARINDEX('bytes',(Files))-1-(CHARINDEX('File(s)',(Files)) 
	  + LEN('File(s)'))))) AS DECIMAL(13,2)) SizeInBytes
FROM #tempFilePaths
WHERE Files LIKE '%File%Bytes%'

--

Add a comment if you have a solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com