BCP Export with Text Qualifiers

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

BCP Export with Text Qualifiers

Author

Topic

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2008-10-31 : 12:48:28

Is there any way to have BCP surround text fields with "s? im dumping some data for a client by state (separate file per state) and i notice if i test-import it, some data that has a comma for example in a bussiness name, will knock everything over a field, and mess up the whole record. here's what i'm using now:


BCP "select fname,lname from test.dbo.sample where phone<>' ' and state_abbrv='DC'" queryout DC.csv -c -t, -Sserver -T

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-31 : 12:52:05

tried using a format file?

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2008-10-31 : 12:54:56

no, i'm not really very well verse in format files, i've used them for fixed-width files, never csv...

any pointers? i'm not oppossed to it if it'll get the job done.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-31 : 13:02:19

http://www.nigelrivett.net/SQLTsql/BCP_quoted_CSV_Format_file.html

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2008-10-31 : 13:08:30

so i would use:

-- create the bcp file
"a",1,"b","c"
"d",2,"e","f"
"g",3,"h","i"

-- create the destination table
create table ##a (s varchar(5), i int, t varchar(5), u varchar(1000))

-- create the format file
8.0
5
1 SQLCHAR 0 0 "\"" 0 x Latin1_General_CI_AS
2 SQLCHAR 0 0 "\"," 1 s Latin1_General_CI_AS
3 SQLCHAR 0 0 ",\"" 2 i Latin1_General_CI_AS
4 SQLCHAR 0 0 "\",\"" 3 t Latin1_General_CI_AS
5 SQLCHAR 0 0 "\"\r\n" 4 u Latin1_General_CI_AS

master..xp_cmdshell 'bcp ##a in c:\bcp.txt -fc:\bcpfmt.txt'
bulk insert ##a from 'c:\bcp.txt' with (formatfile = 'c:\bcpfmt.txt')

part, right? and in that case, i simply change the in to a queryout and adjust all the fields to what i need right?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-31 : 13:13:36

try it out for yourself. that article explains it quite well.

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2008-10-31 : 14:09:10

Okay, so i created a format file:

8.0
2
1 SQLCHAR 0 20 "\"," 1 fname SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 20 "\"\r\n" 2 lname SQL_Latin1_General_CP1_CI_AS

and exported with
BCP test.dbo.sample out DC.csv -f format.txt -T -Sserver

and got: Incorrect host-column number found in BCP format-file

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-31 : 14:26:19

refer this

http://www.devnewsgroups.net/group/microsoft.public.sqlserver.tools/topic5505.aspx

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2008-10-31 : 14:45:39

that's basically what i did, unless i'm just missing something...

here is my actual bcp file, instead of the exampl i was using, this is with trying to add the "s as text qualifiers.


8.0
17
1       SQLCHAR       0       0          "\""     0  x                   ""
2       SQLCHAR       0       10      "\",\""     1  tl1                 ""
3       SQLCHAR       0       100     "\",\""     2  name                ""
4       SQLCHAR       0       150     "\",\""     3  location address    ""
5       SQLCHAR       0       150     "\",\""     4  location city       ""
6       SQLCHAR       0       150     "\",\""     5  location state      ""
7       SQLCHAR       0       150     "\",\""     6  location zip        ""
8       SQLCHAR       0       100     "\",\""     7  county              ""
9       SQLCHAR       0       150     "\",\""     8  owner               ""
10      SQLCHAR       0       30      "\",\""     9  fax                 ""
11      SQLCHAR       0       150     "\",\""     10 web address         ""
12      SQLCHAR       0       4       "\",\""     11 sic                 ""
13      SQLCHAR       0       6       "\",\""     12 sic                 ""
14      SQLCHAR       0       60      "\",\""     13 sic name1           ""
15      SQLCHAR       0       50      "\",\""     14 number of employees ""
16      SQLCHAR       0       80      "\",\""     15 sales volume        ""
17      SQLCHAR       0       70      "\"\r\n"    16 years in database1  ""

what do you think i might be missing, nothing's standing out to me.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources