Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
igates
Starting Member
6 Posts |
 Posted - 2009-08-19 : 09:19:59
|
| Can anybody please help. I am running a bcp query on SQL 2000 to export a csv file. The query works and creates the file no problem. But when I upload it to another application it doesn't like it. However if I go back to my csv file, open it, click save (not save as), and close it, it uploads fine.I have tried various combinations of switches, and still it has problems. Ideally I need to use a comma(,) as the fieldterminator (-t,) and a carriage return (0x0D) and a line feed (0x0A) as the row terminator (-r\r\n).I have tried -r\r -r\r\n -r\n and still it doesn't work until I open it and save it.This needs to work as part of a batch script that can run unattended, so I was wondering if anybody can tell me any other switches I can use, or is there a way to do the open / save combination from the command line?incidentally the script I am running is:bcp "SELECT ...<various fields>..... FROM TEST.Jobs" QUERYOUT "c:\Test\jobs.csv" -T -c -t, -r\n |
|
|
igates
Starting Member
6 Posts |
Posted - 2009-08-20 : 06:22:46
|
| I now know what the problem is, but not the solution yet. BCP, in it's infinite wisdom decides that all blank spaces (0x20) should be converted to null (0x00), which has caused havoc in the other application which doesn't know what to do with them.Strangely when opening/saving/closing the document windows then decides to change them all back again!! |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-08-20 : 07:15:57
|
Can you post some sample data that creates this behaviour?And where is the -S switch?
N 56°04'39.26"E 12°55'05.63" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-08-20 : 07:18:29
|
This behaviour is documented in Books Onlinequote:
When extracting data, note that the bcp utility represents an empty string as a null and a null string as an empty string.
There is a workaround for this.For the column possible containing empty spaces, use SELECT NULLIF(Col1, '') AS Col1Then BCP converts the NULL to empty space in the file.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
igates
Starting Member
6 Posts |
Posted - 2009-08-20 : 12:26:08
|
| cheers. I got round it in the end with ISNULL(field,' '),in all the necessary places.As for the -S, I found it wouldn't work until I took that out. I presumed it was because I was running the script locally to the database. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-08-20 : 13:06:41
|
If there is no "-S" option, BCP assumes there is a default instance on the machine where BCP is run.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-08-21 : 01:46:34
|
the "empty string to null, null to empty string" behavior is only for varchar. For char, you will get spaces in the output file
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
|
|
|
|
|
bcp export csv issue