BCP export to csv

Question

BCP export to csv

balde

SSCommitted

Points: 1594
More actions
November 2, 2012 at 7:56 am

#269659

Not sure of the right forum for this one; Integration services is near enough!
Using BCP (v 9.00.4035.00) with SQL2005 to export data to a set of csv files; hey, it works, or at least it used too....
Looping through the different files for a day, BCP will (seemingly) randomly export the data in Chinese?
The loop executes the same SP to produce similar data-sets, but then BCP exports to csv files and they randomly end-up containing the data translated to Chinese!
Has anyone seen this behaviour before?
BCP syntax used for the export:
SET @bcpCommand = 'bcp "EXEC <dbName>.dbo.<spName> ''' + @batchID + ''', '''+ @thisType +'''" queryout "' + @fileName + '" -t"," -T -c'
EXEC @bcpResult = master..xp_cmdshell @bcpCommand
the SP results for each loop are pretty standard fare, excerpt:
CUSTOMER_IDDATE_TIME DOB
2617461 21/09/2012 17/07/1973
I'll re-trawl through the extensive bcp syntax to see if there's anything I've missed.
In the meantime, any help greatly appreciated!
thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

balde SSCommitted Points: 1594 More actions · Answer 1

...addendum; the Chinese characters only appear when i view the csv files on the SQL Server machine (Windows Server 2008 R2 Enterprise)...

If i copy off to a network share and view from other OS, hey-presto, back to English....

I'll get onto our infrastructure people!

cheers

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

balde (11/2/2012)
...addendum; the Chinese characters only appear when i view the csv files on the SQL Server machine (Windows Server 2008 R2 Enterprise)...
If i copy off to a network share and view from other OS, hey-presto, back to English....
I'll get onto our infrastructure people!
cheers

Sounds like an improper "code page" setup on the machine with the fault. I believe you can overcome that using a RAW setting in BCP.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

balde SSCommitted Points: 1594 More actions · Answer 3

thanks Jeff; tried all the code page settings available to BCP, all to no avail...

we don't need to use the csv dumps on that server itself, they're being sftp'ed away, as long as the target server doesn't interpret them as Chinese then I can move on!

cheers

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 4

balde (11/3/2012)
thanks Jeff; tried all the code page settings available to BCP, all to no avail...
we don't need to use the csv dumps on that server itself, they're being sftp'ed away, as long as the target server doesn't interpret them as Chinese then I can move on!
cheers

Thanks for the feedback. I guess your infrastructure folks (if you have some) would have to look into that and, yeah, I'd still look into it because it makes it tougher to troubleshoot when needed.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

cdutre SSC Enthusiast Points: 148 More actions · Answer 5

I hate replying to an old thread but I ran into the same problem and was able to resolve it.

Once I eliminated any field values that could be NULL with an ISNULL() function it no longer displayed in Chinese when opening in Notepad. Also, when I opened it in Notepad++ it displayed normally in English/Latin characters before or after the changes.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

cdutre (1/16/2014)
I hate replying to an old thread but I ran into the same problem and was able to resolve it.
Once I eliminated any field values that could be NULL with an ISNULL() function it no longer displayed in Chinese when opening in Notepad. Also, when I opened it in Notepad++ it displayed normally in English/Latin characters before or after the changes.

On the other hand, I love it when someone posts a useful update to an old thread. Thanks for the update.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)