BCP format file

[Johnny]

Hi All,
If someone can help to figure out how to use the option -f<format file>
using BCP command. I want to create a txt file with comma for field
separator and double quotes.
"asd","asdas","adsda"
"data1","data2"."data3"
Anith help me to fix the comma with:
DECLARE @sql varchar(255)
SET @sql = 'bcp database.dbo.tblWork out
c:\temp\file.txt -c -Sserver -Uuser -Ppassw -t","'
exec master.dbo.xp_cmdshell @sql
I try this:
SET @sql = 'bcp database.dbo.tblWork out
c:\temp\file.txt -c -Sserver -Uuser -Ppassw -t""",""" -r"""\n"'
exec master.dbo.xp_cmdshell @sql
But the file looks like:
asd","asdas","adsda
data1","data2"."data3

Thanks in advance.
Johnny...

[lindawie]

Johnny,

> If someone can help to figure out how to use the option -f<format
> file> using BCP command. I want to create a txt file with comma for
> field separator and double quotes.
> "asd","asdas","adsda"
> "data1","data2"."data3"

> I try this:
> SET @sql = 'bcp database.dbo.tblWork out
> c:\temp\file.txt -c -Sserver -Uuser -Ppassw -t""",""" -r"""\n"'
> exec master.dbo.xp_cmdshell @sql
> But the file looks like:
> asd","asdas","adsda
> data1","data2"."data3

You are correct, you need to use a format file for this.
Using the pubs..authors table as an example, we'll bcp out of a
view that looks like this:

use pubs
go
create view authors_csv as
select null first_quote, * from authors

Note that we are including a dummy column called first_quote
that returns NULL. It's just a little trick to get the leading
quote on the first column.

The format file looks like this:

8.0
10
1 SQLCHAR 0 0 "\"" 1 first_quote ""
2 SQLCHAR 0 11 "\",\"" 2 au_id ""
3 SQLCHAR 0 40 "\",\"" 3 au_lname ""
4 SQLCHAR 0 20 "\",\"" 4 au_fname ""
5 SQLCHAR 0 12 "\",\"" 5 phone ""
6 SQLCHAR 0 40 "\",\"" 6 address ""
7 SQLCHAR 0 20 "\",\"" 7 city ""
8 SQLCHAR 0 2 "\",\"" 8 state ""
9 SQLCHAR 0 5 "\",\"" 9 zip ""
10 SQLCHAR 0 1 "\"\r\n" 10 contract ""

That dummy column is also in the format file to get the leading
quote on au_id. Each field terminator includes the closing quote
of the current field, a comma, and the opening quote of the next field.
The row terminator includes the closing quote od the last field.

Here's the command line:

bcp pubs..authors_csv out
authors_csv.dat -fauthors_csv.bcp -Slindaw\ml_tg -T

Linda

[Johnny]

Thanks Linda,
But How can I open or see the format file?
Johnny

"lindawie" <lind...@my-deja.com> wrote in message
news:#KaFNAGSCHA.1724@tkmsftngp13...

[lindawie]

Johnny,

> Thanks Linda,
> But How can I open or see the format file?
> Johnny
>

This is the format file.
Just copy all the lines below, paste into Notepad and save.

8.0
10
1 SQLCHAR 0 0 "\"" 1 first_quote ""
2 SQLCHAR 0 11 "\",\"" 2 au_id ""
3 SQLCHAR 0 40 "\",\"" 3 au_lname ""
4 SQLCHAR 0 20 "\",\"" 4 au_fname ""
5 SQLCHAR 0 12 "\",\"" 5 phone ""
6 SQLCHAR 0 40 "\",\"" 6 address ""
7 SQLCHAR 0 20 "\",\"" 7 city ""
8 SQLCHAR 0 2 "\",\"" 8 state ""
9 SQLCHAR 0 5 "\",\"" 9 zip ""
10 SQLCHAR 0 1 "\"\r\n" 10 contract ""

Linda

[Johnny]

Sorry to be slow ;-)
Thank you very much.
Johnny...

"lindawie" <lind...@my-deja.com> wrote in message

news:OX9SCPGSCHA.1976@tkmsftngp11...

[Johnny]

Linda I create this format file to use bcp....

8.0
7

1 SQLCHAR 0 0 "\"" 1 first_quote ""

2 SQLCHAR 0 255 "\",\"" 2 col001 ""
3 SQLCHAR 0 255 "\",\"" 3 col002 ""
4 SQLCHAR 0 255 "\",\"" 4 col003 ""
5 SQLCHAR 0 255 "\",\"" 5 col004 ""
6 SQLCHAR 0 8 "\",\"" 6 col005 ""
7 SQLCHAR 0 255 "\"\r\n" 7 col006 ""

I'm getting this error:
Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading BCP
format file
Do you know how to create this file using my table.
Thanks
Johnny...

"lindawie" <lind...@my-deja.com> wrote in message

news:OX9SCPGSCHA.1976@tkmsftngp11...

[lindawie]

Johnny,

<jball...@integram.com> wrote in message
news:OuV0epGSCHA.1996@tkmsftngp12...

> Linda I create this format file to use bcp....
>
> 8.0
> 7
> 1 SQLCHAR 0 0 "\"" 1 first_quote ""
> 2 SQLCHAR 0 255 "\",\"" 2 col001 ""
> 3 SQLCHAR 0 255 "\",\"" 3 col002 ""
> 4 SQLCHAR 0 255 "\",\"" 4 col003 ""
> 5 SQLCHAR 0 255 "\",\"" 5 col004 ""
> 6 SQLCHAR 0 8 "\",\"" 6 col005 ""
> 7 SQLCHAR 0 255 "\"\r\n" 7 col006 ""
>
> I'm getting this error:
> Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading BCP
> format file
> Do you know how to create this file using my table.

-- I am assuming your table looks like this. If it does not,
-- please post DDL and sample data in the form of insert statements.

create table tblWork (
col001 varchar(255),
col002 varchar(255),
col003 varchar(255),
col004 varchar(255),
col005 varchar(8),
col006 varchar(255)
)
go
-- Create a view with the dummy column for the first quote.
create view Work as
select null first_quote, * from tblWork
go
-- Create some test data.
insert tblWork
select au_id, au_fname, au_lname, city, right(phone, 8),
isnull((select top 1 title
from pubs..titles titles
join pubs..titleauthor titleauthor
on titleauthor.title_id = titles.title_id
and titleauthor.au_id = authors.au_id), 'Unpublished')
from pubs..authors authors
select * from Work
go
-- drop table tblWork
-- drop view Work
-- truncate table tblWork

/***
Your format file works fine for me.
I just copied and pasted into Query Analyser and saved it.
Make sure you have a blank line at the end of the file.
If you are using xp_cmdshell, you must either move to the
directory where the format file is located or use fully
qualified path names.
***/

declare @sql varchar(255)
set @sql = 'pushd C:\Staging\Zap & bcp Zap..Work out
Work.dat -fWork.bcp -S%computername%\pellew -T'
exec master..xp_cmdshell @sql

/***
output
-----------------------------------------
NULL
Starting copy...
NULL
23 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 1
NULL

***/

-- Linda

[Johnny]

Sorry to reply late.
Thanks for you help.... it works succefully.
Johnny

"lindawie" <lind...@my-deja.com> wrote in message

news:eTiEMmHSCHA.3648@tkmsftngp11...