tsql BCP csv has extra spaces

Posted on 2011-10-18
Last Modified: 2012-05-12
Hi Experts

I have a sql query  = Select FirstName, '' as MiddleName, '' as Sirname from tnlNames

I dont want to ouput the Middlename, I use the bcp to create a csv from the above query.
The file creates no problem but when I open the file with note pad I get an extra space in middle name.

but im after

I cant actually see the ASCII value of the space, Im still looking for some software to assist me in that one.
Question by:MrDavidThorn

    Author Comment

    The char value is 20, if that helps anyone

    Expert Comment

    SELECT FristName+',,'+Sirname from tnlNames

    Author Comment

    isnt that just creating one field? - its not practical for me.
    LVL 59

    Accepted Solution

    If you are sending this to BCP, does it actually matter if you send multiple fields or one field concatenated by comma delimiter already? I think that was the suggestion, but anyway try it with NULL.

    bcp "SELECT FirstName, NULL as MiddleName, Sirname FROM tnlNames;" ...

    FYI, in your question above, you also have a literal '' for the Sirname, so removed that in example given you are showing that in the final result. ;)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
    In this article I will describe the Copy Database Wizard 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.
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    737 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

    22 Experts available now in Live!

    Get 1:1 Help Now