[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


tsql BCP csv has extra spaces

Posted on 2011-10-18
Medium Priority
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
  • 2

Author Comment

ID: 36986249
The char value is 20, if that helps anyone

Expert Comment

ID: 36986329
SELECT FristName+',,'+Sirname from tnlNames

Author Comment

ID: 36986350
isnt that just creating one field? - its not practical for me.
LVL 60

Accepted Solution

Kevin Cross earned 2000 total points
ID: 36987306
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. ;)

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
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 video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

873 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