determining files with 0 bytes in transact SQL

Posted on 2009-04-16
Last Modified: 2012-05-06
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

Question by:mojoinc
    1 Comment
    LVL 39

    Accepted Solution

    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.

    dir /b


    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Suggested Solutions

    Title # Comments Views Activity
    get all customers 2 30
    SQl server restarts itself 6 26
    sql help 5 42
    Not selecting duplicate data 6 21
    Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA.…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now