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

x
?
Solved

tsql BCP csv has extra spaces

Posted on 2011-10-18
4
Medium Priority
?
515 Views
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.
i.e
DAVID, ,THORN

but im after
DAVID,,THORN

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

Author Comment

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

Expert Comment

by:luckeydog
ID: 36986329
SELECT FristName+',,'+Sirname from tnlNames
0
 

Author Comment

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

Accepted Solution

by:
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. ;)
0

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