BCP to comma separated Quote surround text file.

I have a need to export (regularly) a large table. I need to export to a
comma delimited file with quote surrounds around text fields (or all fields
for all that matters).
I can export the data with no problem, I can't figure out how to get the
quote surrounds however.
Here is what my bcp statement looks like:
bcp SURVEY.dbo.vw_898002_results out e:\export\survey.dat -U ***** -P
***** -S VNET-SQL /c /t , > e:\export\out.txt
I used the bol and it looked like I should be able to do something with
the -t switch but that seems to be having no impact what-so-ever.
thanks.Quote surrounds can be made by specifying in a format file the delimiters
for each field, rather than using the -t operator. Read about format files,
they are not really that hard but many people choke on them too quickly. If
I remember correctly, you can define a column 0 that terminates with " if
you need a quote on the first column.
Think of it as a regular expression problem.
RLF
PS - Of course, it leaves me wondering what you are getting with /t. (FWIW,
I don't think it matters, but you are using /t and the doc is for -t.)
"Roger Twomey" <rogerdev@.vnet.on.ca> wrote in message
news:OJLZc7CQFHA.248@.TK2MSFTNGP15.phx.gbl...
>I have a need to export (regularly) a large table. I need to export to a
>comma delimited file with quote surrounds around text fields (or all fields
>for all that matters).
> I can export the data with no problem, I can't figure out how to get the
> quote surrounds however.
> Here is what my bcp statement looks like:
> bcp SURVEY.dbo.vw_898002_results out e:\export\survey.dat -U ***** -P
> ***** -S VNET-SQL /c /t , > e:\export\out.txt
> I used the bol and it looked like I should be able to do something with
> the -t switch but that seems to be having no impact what-so-ever.
> thanks.
>|||the /t is just one of the many versions I was trying out, I think it was
meant to be: -t \t (tab delimited)
in the end I managed to get the -t to work except for the very first record
on the very first row.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:ubZrw8DQFHA.2000@.TK2MSFTNGP15.phx.gbl...
> Quote surrounds can be made by specifying in a format file the delimiters
> for each field, rather than using the -t operator. Read about format
> files, they are not really that hard but many people choke on them too
> quickly. If I remember correctly, you can define a column 0 that
> terminates with " if you need a quote on the first column.
> Think of it as a regular expression problem.
> RLF
> PS - Of course, it leaves me wondering what you are getting with /t.
> (FWIW, I don't think it matters, but you are using /t and the doc is
> for -t.)
>
> "Roger Twomey" <rogerdev@.vnet.on.ca> wrote in message
> news:OJLZc7CQFHA.248@.TK2MSFTNGP15.phx.gbl...
>

bcp Question

Without using format file, is there a way for bcp to ignore single quote in
an import file? Thanks.Why are you not wanting to use a format file?
mason wrote:
> Without using format file, is there a way for bcp to ignore single quote i
n
> an import file? Thanks.
>|||Try -q option. More information in BOL.|||I have no problem using format files, but it would be simpler to do without
in terms of maintenance. -c option works well when there is no quote in
import files (about 36 of them). I just want to verify whether there is an
option somewhere to ignore quotes for bcp.
"Carl Imthurn" <nospam@.all.com> wrote in message
news:OvDmLQsQGHA.5768@.tk2msftngp13.phx.gbl...
> Why are you not wanting to use a format file?
> mason wrote:|||Just tried. It's not what I wanted. I think -q affects identifiers, not the
data elements in a data file.
For example, a data record looks like this. I would like bcp to ignore
single quotes when importing.
1,'John','2006-03-08 12:00:00.000'
"Green" <subhash.daga@.gmail.com> wrote in message
news:1141831907.411815.10740@.p10g2000cwp.googlegroups.com...
> Try -q option. More information in BOL.|||Would it be possible to create the data file without the apostrophes?
Looking at your example, it seems that you have a comma-delimited file
with apostrophes delimiting text fields but not numeric fields.
I needed to deal with that situation once - I was faced with quotes
rather than apostrophes, but the concept was the same. The only thing I
was able to figure out was to import the text file to a SQL Server table
via DTS because of the fact that some fields have a delimiting
character, some do not.
Good luck - hope this was helpful.
Carl
mason wrote:

> Just tried. It's not what I wanted. I think -q affects identifiers, not
> the data elements in a data file.
> For example, a data record looks like this. I would like bcp to ignore
> single quotes when importing.
> 1,'John','2006-03-08 12:00:00.000'
>
>
> "Green" <subhash.daga@.gmail.com> wrote in message
> news:1141831907.411815.10740@.p10g2000cwp.googlegroups.com...
>
>|||Of course. I will most likely do that at the export end. Since the files may
also feed Sybase and Oracle, it gets ugly. Thanks.
"Carl Imthurn" <nospam@.all.com> wrote in message
news:eJ2605sQGHA.5808@.TK2MSFTNGP12.phx.gbl...
> Would it be possible to create the data file without the apostrophes?
> Looking at your example, it seems that you have a comma-delimited file
> with apostrophes delimiting text fields but not numeric fields.
> I needed to deal with that situation once - I was faced with quotes rather
> than apostrophes, but the concept was the same. The only thing I was able
> to figure out was to import the text file to a SQL Server table via DTS
> because of the fact that some fields have a delimiting character, some do
> not.
> Good luck - hope this was helpful.
> Carl
> mason wrote:
>
>

BCP OUT with Double Quote(") text qualifier?

How do I tell BCP that I want it to output with Double Quotes around my char fields?

It's easy to do in DTS by setting the connection properties of the Text File destination. The reason I can't use DTS is because my table has 329 fields and when I try to modify the destination properties of the transform task, Enterprise Manager bombs. I'd rather use BCP and a format file anyway.

Thanks,

Ray Metz
Everett, WAI just tried using a format file to define the field seperators as "," but BCP barfed. when I switched to ',' it worked. Maybe you could use this.|||Yeah, I had a similar idea to create a view with select '"' + field + '"' for each of the char columns. But since my export has over 300 columns and many of them char or varchar, I wanted an easier solution than going through each column.

Luckily, the Oracle DBA on the other end was ok with me not sending the quotes. The field seperator was a bar (|), and so far we haven't found any bars in the data so I think we're in good shape.

Thanks for your reply.

Ray

BCP data import

Anyone knows what is the syntax for BCP import for a comma delimited and
double quote text qualifier text file ? I have tried almost all the options
but still can't figure it out. I keep getting different error messages.
T.I.A
Hi
You can use BCP with the format option to get a format file that you can
change. Alternatively look at using DTS/SSIS see
http://www.sqldts.com/default.aspx?246
John
"DXC" wrote:
[vbcol=seagreen]
> Format file for 221 text files !!!!!
> "John Bell" wrote:
|||Format file for 221 text files !!!!!
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> See Erlands post http://tinyurl.com/yf6z2u
> John
> "DXC" wrote:
|||Hi
See Erlands post http://tinyurl.com/yf6z2u
John
"DXC" wrote:

> Anyone knows what is the syntax for BCP import for a comma delimited and
> double quote text qualifier text file ? I have tried almost all the options
> but still can't figure it out. I keep getting different error messages.
> T.I.A

BCP data import

Anyone knows what is the syntax for BCP import for a comma delimited and
double quote text qualifier text file ? I have tried almost all the options
but still can't figure it out. I keep getting different error messages.
T.I.AHi
You can use BCP with the format option to get a format file that you can
change. Alternatively look at using DTS/SSIS see
http://www.sqldts.com/default.aspx?246
John
"DXC" wrote:
[vbcol=seagreen]
> Format file for 221 text files !!!!!
> "John Bell" wrote:
>|||Format file for 221 text files !!!!!
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> See Erlands post http://tinyurl.com/yf6z2u
> John
> "DXC" wrote:
>|||Hi
See Erlands post http://tinyurl.com/yf6z2u
John
"DXC" wrote:

> Anyone knows what is the syntax for BCP import for a comma delimited and
> double quote text qualifier text file ? I have tried almost all the option
s
> but still can't figure it out. I keep getting different error messages.
> T.I.A

BCM with accounting 2007

I have bcm with outlook 2007 and accounting express. when i create an opportunity in bcm, everything is fine. when i click the convert to quote button, it dumps all of the item information (quantity, name, description, price, tax....) into the description field in accounting. Then I have to copy each piece out of the description field into it's appropriate field in accounting 2007 express. Where is the mapping for these fields, so that I can map each one correctly? Is there something else i should be doing?This does not appear related to Setup & Upgrade