• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 152
  • Last Modified:

determining files with 0 bytes in transact SQL

I have a need to determine the length of flat files before I import them into SQL Server.  I currently determine which files to load by a DIR command thru xp_cmdshell.  I have run into a problem where some files have 0 bytes and I want to exclude those.  How can determine the file size in transact SQL?  
xp_cmdshell 'dir c:\directory\ /B'  this is what I currently use but it only returns the file names.  Haven't figured out how to determine the 0 length files

Open in new window

0
mojoinc
Asked:
mojoinc
1 Solution
 
BrandonGalderisiCommented:
If you remove /b, it will print the full file details.  From there, you can parse the string for each record to ensure that you aren't looking at 0 byte files.

example:
dir /b

c1.txt
c2.txt
c3.txt

dir
04/10/2009  12:28 PM             6,079 c1.txt
04/10/2009  12:28 PM             6,077 c2.txt
04/10/2009  12:47 PM             5,958 c3.txt

You just need to find the file bytes and make sure it's not 0.  

On a side note, adding "/a-d" to your dir cmd will exclude directories.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now