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:22:55
|
| Hi Guys,Sorry, but I posted in the Admin forum, before I saw that this forum actually covers BCP as well, so I apologise if you read this twice.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 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-08-19 : 10:00:36
|
1. why the application does not like it ? which part of the file it does not like ?2. How do you open it ? Using what application ?3. Inspect your text file using an editor like notepad and check see any part of the text file is wrong ?4. Have you try using a format file ?
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
igates
Starting Member
6 Posts |
Posted - 2009-08-19 : 11:18:12
|
quote:
Originally posted by khtan
1. why the application does not like it ? which part of the file it does not like ?2. How do you open it ? Using what application ?3. Inspect your text file using an editor like notepad and check see any part of the text file is wrong ?4. Have you try using a format file ?
1. I'm assuming that SQl isn't creating a proper CSV format, which is why it is OK after it has been opened and saved. 2. The application is being imported into an in house application, but it has never had any problems with csv files created in other programs.3. If I open it in notepad it looks fine. when I click save and open it again it looks exactly the same (as you'd expect as I haven't made any changes)4. I tried using a format file, and get the same problem. |
|
|
|
igates
Starting Member
6 Posts |
Posted - 2009-08-20 : 06:23:03
|
| 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 |
|
|
|
|
|
|
|
bcp export problem with csv