SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 BCP Inconcistency while exporting CSV		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

emmanueloren
Starting Member

France
2 Posts
Posted - 06/22/2012 :  09:19:11  Show Profile  Reply with Quote
I have a problem using BCP. The request and the exported file doesn’t have the same number of lines.
This is the command I use :
Exec master..xp_cmdshell 'bcp "SELECT [email],[title],[firstname],[lastname],[dateofbirth],[zip],''RS'' as raison_soc   FROM […]" queryout "C:\Export\20120611003-0.csv" -c -T -CRAW '


The request executed out of BCP returns 6 695 695 lines.
BCP execution output is:



So the right number of lines is supposed to be returned.
The output file contains 6 911 814 lines, 216 119 added lines. None of them is doubled.
Versions:
SQL server 2008
Bcp version 10.0.5500.0

Does anybody got any clue because I haven't...

Thank you in advance for any input.

Emmanuel OREN
Edited by - emmanueloren on 06/22/2012 09:22:20

visakh16
Very Important crosS Applying yaK Herder

India
47023 Posts
Posted - 06/22/2012 :  23:05:50  Show Profile  Reply with Quote
may be one of involved column has text data which includes carriage return/line breaks

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

emmanueloren
Starting Member

France
2 Posts
Posted - 06/26/2012 :  08:50:07  Show Profile  Reply with Quote
Thank you for the idea, in fact there ara NULL caracters interpreted as line breaks !

Emmanuel ORE?
Go to Top of Page

Bill_C
Constraint Violating Yak Guru

United Kingdom
279 Posts
Posted - 06/28/2012 :  00:58:05  Show Profile  Reply with Quote
We regularly have to deal with csv files and have to 'clean' them, removing/replacing comma's from text fields, and many other characters to get them through the import.
Then when they are imported to the tables we do a reverse operation, puting them back in so the records look like their originals, it can be a bit of a pain.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000