BCP Utility with null values

Hi all,
I am using BCP to output from a table that has some empty values into a
flat file. I noticed that when I try to use BCP to upload the flat
file into an exact replica of the table in another server I get an
error due to restrictions on that table that does not allow null
values. I think what is happening is that BCP convert empty values
into null during the output process and when trying to upload the value
I get the error.
Is there a way to go around the fact that BCP is converting empty
values to NULL or should I be using a different process to upload the
data from one server into another. Also, I don't have the option to
connect to the remote server from any of them hence the reason why I am
using BCP.
Thank you very much.
Ron.Hi Ron
What options are you specifying on the BCP command? This should be ok and
you should not get conversions.
John
"Ron" wrote:
> Hi all,
> I am using BCP to output from a table that has some empty values into a
> flat file. I noticed that when I try to use BCP to upload the flat
> file into an exact replica of the table in another server I get an
> error due to restrictions on that table that does not allow null
> values. I think what is happening is that BCP convert empty values
> into null during the output process and when trying to upload the value
> I get the error.
> Is there a way to go around the fact that BCP is converting empty
> values to NULL or should I be using a different process to upload the
> data from one server into another. Also, I don't have the option to
> connect to the remote server from any of them hence the reason why I am
> using BCP.
> Thank you very much.
> Ron.
>sql

BCP Utility with null values

Hi all,
I am using BCP to output from a table that has some empty values into a
flat file. I noticed that when I try to use BCP to upload the flat
file into an exact replica of the table in another server I get an
error due to restrictions on that table that does not allow null
values. I think what is happening is that BCP convert empty values
into null during the output process and when trying to upload the value
I get the error.
Is there a way to go around the fact that BCP is converting empty
values to NULL or should I be using a different process to upload the
data from one server into another. Also, I don't have the option to
connect to the remote server from any of them hence the reason why I am
using BCP.
Thank you very much.
Ron.Hi Ron
What options are you specifying on the BCP command? This should be ok and
you should not get conversions.
John
"Ron" wrote:

> Hi all,
> I am using BCP to output from a table that has some empty values into a
> flat file. I noticed that when I try to use BCP to upload the flat
> file into an exact replica of the table in another server I get an
> error due to restrictions on that table that does not allow null
> values. I think what is happening is that BCP convert empty values
> into null during the output process and when trying to upload the value
> I get the error.
> Is there a way to go around the fact that BCP is converting empty
> values to NULL or should I be using a different process to upload the
> data from one server into another. Also, I don't have the option to
> connect to the remote server from any of them hence the reason why I am
> using BCP.
> Thank you very much.
> Ron.
>

BCP Utility with null values

Hi all,
I am using BCP to output from a table that has some empty values into a
flat file. I noticed that when I try to use BCP to upload the flat
file into an exact replica of the table in another server I get an
error due to restrictions on that table that does not allow null
values. I think what is happening is that BCP convert empty values
into null during the output process and when trying to upload the value
I get the error.
Is there a way to go around the fact that BCP is converting empty
values to NULL or should I be using a different process to upload the
data from one server into another. Also, I don't have the option to
connect to the remote server from any of them hence the reason why I am
using BCP.
Thank you very much.
Ron.
Hi Ron
What options are you specifying on the BCP command? This should be ok and
you should not get conversions.
John
"Ron" wrote:

> Hi all,
> I am using BCP to output from a table that has some empty values into a
> flat file. I noticed that when I try to use BCP to upload the flat
> file into an exact replica of the table in another server I get an
> error due to restrictions on that table that does not allow null
> values. I think what is happening is that BCP convert empty values
> into null during the output process and when trying to upload the value
> I get the error.
> Is there a way to go around the fact that BCP is converting empty
> values to NULL or should I be using a different process to upload the
> data from one server into another. Also, I don't have the option to
> connect to the remote server from any of them hence the reason why I am
> using BCP.
> Thank you very much.
> Ron.
>

bcp utility

I'm using the bcp utility to export data to a network file. Is there a way to
output the field names in addition to the data? According to Books Online,
there isn't a switch for this.Hi Mike
Look at using the import/export wizard, DTS or SSIS to do this, you can do
it with BCP using something like:
BCP "SELECT au_id, au_lname, au_fname, phone, address, city, state, zip,
contract FROM ( SELECT CAST(au_id as varchar(30)) as au_id, au_lname,
au_fname, phone, address, city, state, zip, CAST(contract as varchar(30)) AS
contract, 1 AS orderby FROM PUBS..Authors UNION ALL SELECT 'au_id',
'au_lname', 'au_fname', 'phone', 'address', 'city', 'state', 'zip',
'contract', 0 ) A ORDER BY orderby" QUERYOUT authors.txt -c -T -S (local)
but it's a bit of a cludge!
John
"mike" wrote:
> I'm using the bcp utility to export data to a network file. Is there a way to
> output the field names in addition to the data? According to Books Online,
> there isn't a switch for this.sql

bcp utilities

Hi
When I used the bcp utilities to output a query to a text file, the date
field become a "10/2/2003 00:00:00" instead of mm/dd/yyyy format. How can I
correct this?
Thanks!
ChrisYou could use :-
bcp with the queryout option and specifiy a select statement that
converts the date to your format
or
create a view that returns the data as you require and then bcp out throught
the view
or
specifiy a format file for the bcp file to use
--
HTH
Ryan Waight, MCDBA, MCSE
"ChrisM" <cma1@.mail.com> wrote in message
news:ezveEJ6pDHA.2740@.TK2MSFTNGP09.phx.gbl...
> Hi
> When I used the bcp utilities to output a query to a text file, the date
> field become a "10/2/2003 00:00:00" instead of mm/dd/yyyy format. How can
I
> correct this?
> Thanks!
> Chris
>|||Ryan
Thanks for your replied. Could you give me some example on how the queryout
option converts the date to the format I need? Below is the bcp command I
use.
bcp pubs..titles out "C:\bcp_test
Output.txt" -c -q -S"sqlserver" -U"sa" -P"xxx"
Thanks!
Chris
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:uqZNap6pDHA.392@.TK2MSFTNGP11.phx.gbl...
> You could use :-
> bcp with the queryout option and specifiy a select statement that
> converts the date to your format
> or
> create a view that returns the data as you require and then bcp out
throught
> the view
> or
> specifiy a format file for the bcp file to use
> --
> HTH
> Ryan Waight, MCDBA, MCSE
> "ChrisM" <cma1@.mail.com> wrote in message
> news:ezveEJ6pDHA.2740@.TK2MSFTNGP09.phx.gbl...
> > Hi
> >
> > When I used the bcp utilities to output a query to a text file, the date
> > field become a "10/2/2003 00:00:00" instead of mm/dd/yyyy format. How
can
> I
> > correct this?
> >
> > Thanks!
> >
> > Chris
> >
> >
>|||You would have to use the CONVERT statement which converts the dates into a
string, and with which you can define a format for the date (the 3:rd
parameter to the function).
bcp "SELECT CONVERT(char(10), ord_date, 101) + ' ' + CONVERT(char(8),
ord_date, 108) FROM pubs..sales" out
"C:\bcp_test\Output.txt" -c -q -S"sqlserver" -U"sa" -P"xxx"
Alternatively, you can create a view with the query and CONVERTS and then
export from the view.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"ChrisM" <cma1@.mail.com> wrote in message
news:esqf0M%23pDHA.2000@.TK2MSFTNGP12.phx.gbl...
> Ryan
> Thanks for your replied. Could you give me some example on how the
queryout
> option converts the date to the format I need? Below is the bcp command I
> use.
> bcp pubs..titles out "C:\bcp_test
> Output.txt" -c -q -S"sqlserver" -U"sa" -P"xxx"
> Thanks!
> Chris
>
> "Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:uqZNap6pDHA.392@.TK2MSFTNGP11.phx.gbl...
> > You could use :-
> > bcp with the queryout option and specifiy a select statement that
> > converts the date to your format
> >
> > or
> >
> > create a view that returns the data as you require and then bcp out
> throught
> > the view
> >
> > or
> >
> > specifiy a format file for the bcp file to use
> >
> > --
> > HTH
> > Ryan Waight, MCDBA, MCSE
> >
> > "ChrisM" <cma1@.mail.com> wrote in message
> > news:ezveEJ6pDHA.2740@.TK2MSFTNGP09.phx.gbl...
> > > Hi
> > >
> > > When I used the bcp utilities to output a query to a text file, the
date
> > > field become a "10/2/2003 00:00:00" instead of mm/dd/yyyy format. How
> can
> > I
> > > correct this?
> > >
> > > Thanks!
> > >
> > > Chris
> > >
> > >
> >
> >
>

bcp utf8 file

I'm outputting a file from a mySQL server that contains 1 column that
has input it many languages. The base definition for the column is
UTF8.

I output the data into a flat file and bcp it to a sql2000 server, and
the multi-byte data does not survive the translation.

Any ideas on how to input a UTF8 flat file into the server using BCP?

I've tried the -n -N -w options -- no luck.

thanks,

edAssuming that your destination column is nvarchar or nchar, then -w
should be the correct option; -n or -N are specific to MSSQL, so
they're unlikely to work. I've never used bcp to import a file like
that myself, so I can't really say for sure what the solution is, but
under "Copying Data Between Different Collations" in Books Online, it
suggests using a bcp format file with specific collations. You might
also try using DTS to import the data, as it's generally a lot smarter
than bcp, however again I've never tried importing a UTF8 file with it.

Simon|||(geekboyed@.gmail.com) writes:
> I'm outputting a file from a mySQL server that contains 1 column that
> has input it many languages. The base definition for the column is
> UTF8.
> I output the data into a flat file and bcp it to a sql2000 server, and
> the multi-byte data does not survive the translation.
> Any ideas on how to input a UTF8 flat file into the server using BCP?

Since you cannot store UTF-8 data in SQL 2000 in any civilized way,
you need to first convert the file to UCS-2. (Or UTF_16 if you prefer.
I hope you are not using surrogates.)

A simple way to do this is to open the file in Notepad, and then change
encoding when you do Save As.

I'm a little uncertain how any byte-order mark would affect BCP.
Notepad is likely to add one, but I don't know if BCP is smart
enough to ignore it.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

BCP Temporary Tables

Hi,
I am trying to bcp data from a txt file into a temp table:
CREATE TABLE #output
(FIRSTNAME varchar NOT NULL,
lastname VARCHAR(32) NOT NULL,
state VARCHAR(14) NULL )
master.dbo.xp_cmdshell 'bcp #output in "c:\test.txt" -STRAVELLER -T -c'
I am running this within the dbtemp database. I am getting the error:
SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object
name '#output'.
NULL
When I run it against a normal table the query runs fine. Can anybody
tell me what I am doing wrong? Is it possible to run this into a
temporary table?
Thanks
Steffan
Temporary tables are session specific, so the new session used by osql
connecting back into SQL Server can't see the temp table created in the
original session. You can use a global temporary table (CREATE TABLE
##output) or a permanent staging table
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Bob Badger" <sjdavies47@.hotmail.com> wrote in message
news:1130707432.996880.194530@.g47g2000cwa.googlegr oups.com...
> Hi,
> I am trying to bcp data from a txt file into a temp table:
> CREATE TABLE #output
> (FIRSTNAME varchar NOT NULL,
> lastname VARCHAR(32) NOT NULL,
> state VARCHAR(14) NULL )
> master.dbo.xp_cmdshell 'bcp #output in "c:\test.txt" -STRAVELLER -T -c'
> I am running this within the dbtemp database. I am getting the error:
> SQLState = S0002, NativeError = 208
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object
> name '#output'.
> NULL
> When I run it against a normal table the query runs fine. Can anybody
> tell me what I am doing wrong? Is it possible to run this into a
> temporary table?
> Thanks
> Steffan
>

BCP Temporary Tables

Hi,
I am trying to bcp data from a txt file into a temp table:
CREATE TABLE #output
(FIRSTNAME varchar NOT NULL,
lastname VARCHAR(32) NOT NULL,
state VARCHAR(14) NULL )
master.dbo.xp_cmdshell 'bcp #output in "c:\test.txt" -STRAVELLER -T -c'
I am running this within the dbtemp database. I am getting the error:
SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid o
bject
name '#output'.
NULL
When I run it against a normal table the query runs fine. Can anybody
tell me what I am doing wrong? Is it possible to run this into a
temporary table'
Thanks
SteffanTemporary tables are session specific, so the new session used by osql
connecting back into SQL Server can't see the temp table created in the
original session. You can use a global temporary table (CREATE TABLE
##output) or a permanent staging table
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Bob Badger" <sjdavies47@.hotmail.com> wrote in message
news:1130707432.996880.194530@.g47g2000cwa.googlegroups.com...
> Hi,
> I am trying to bcp data from a txt file into a temp table:
> CREATE TABLE #output
> (FIRSTNAME varchar NOT NULL,
> lastname VARCHAR(32) NOT NULL,
> state VARCHAR(14) NULL )
> master.dbo.xp_cmdshell 'bcp #output in "c:\test.txt" -STRAVELLER -T -c'
> I am running this within the dbtemp database. I am getting the error:
> SQLState = S0002, NativeError = 208
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid
object
> name '#output'.
> NULL
> When I run it against a normal table the query runs fine. Can anybody
> tell me what I am doing wrong? Is it possible to run this into a
> temporary table'
> Thanks
> Steffan
>

BCP Temporary Tables

Hi,
I am trying to bcp data from a txt file into a temp table:
CREATE TABLE #output
(FIRSTNAME varchar NOT NULL,
lastname VARCHAR(32) NOT NULL,
state VARCHAR(14) NULL )
master.dbo.xp_cmdshell 'bcp #output in "c:\test.txt" -STRAVELLER -T -c'
I am running this within the dbtemp database. I am getting the error:
SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object
name '#output'.
NULL
When I run it against a normal table the query runs fine. Can anybody
tell me what I am doing wrong? Is it possible to run this into a
temporary table'
Thanks
SteffanTemporary tables are session specific, so the new session used by osql
connecting back into SQL Server can't see the temp table created in the
original session. You can use a global temporary table (CREATE TABLE
##output) or a permanent staging table
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Bob Badger" <sjdavies47@.hotmail.com> wrote in message
news:1130707432.996880.194530@.g47g2000cwa.googlegroups.com...
> Hi,
> I am trying to bcp data from a txt file into a temp table:
> CREATE TABLE #output
> (FIRSTNAME varchar NOT NULL,
> lastname VARCHAR(32) NOT NULL,
> state VARCHAR(14) NULL )
> master.dbo.xp_cmdshell 'bcp #output in "c:\test.txt" -STRAVELLER -T -c'
> I am running this within the dbtemp database. I am getting the error:
> SQLState = S0002, NativeError = 208
> Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object
> name '#output'.
> NULL
> When I run it against a normal table the query runs fine. Can anybody
> tell me what I am doing wrong? Is it possible to run this into a
> temporary table'
> Thanks
> Steffan
>

bcp still more errors

I managed to coorect the earlier error but now it looks like this
master..xp_cmdshell 'bcp ##ttable
OUTPUT "res.xls" -C -S "server" -U "sa" -P "password"'
the new error is
NULL
Enter the file storage type of field state_id [int-null]:
(2 row(s) affected)
I have 2 fields in the temp tableHi,
Instead of -C can use -c (small case). The -C is for code page.
-c does not prompt for each field; it uses char as the storage type
--
Thanks
Hari
MCDBA
"Anup" <anup_pokhrel@.hotmail.com> wrote in message
news:u8U6iVWaEHA.3016@.tk2msftngp13.phx.gbl...
> I managed to coorect the earlier error but now it looks like this
> master..xp_cmdshell 'bcp ##ttable
> OUTPUT "res.xls" -C -S "server" -U "sa" -P "password"'
> the new error is
> NULL
> Enter the file storage type of field state_id [int-null]:
> (2 row(s) affected)
> I have 2 fields in the temp table
>

BCP Quoted CSV

Can anyone clue me in on how to get bcp to output quoted, comma-separated
values
"Bob",50,"Kansas"
"Leslie",44,"California"
etc
thanks ...
Hi
Just using the -r and -t options to produce quotes will miss the first set
of quotes on each line.
You can try something like:
bcp "SELECT
'\"'+au_id,au_lname,au_fname,phone,address,city,st ate,zip,contract FROM
Pubs.dbo.authors" queryout authors.txt -T -t"\",\"" -r"\"" -c
or use the Bulk Export task and DTS:
http://www.sqldts.com/default.aspx?272
John
"Liz" <liz@.tiredofspam.com> wrote in message
news:OvIT7vYKEHA.2244@.tk2msftngp13.phx.gbl...
> Can anyone clue me in on how to get bcp to output quoted, comma-separated
> values
> "Bob",50,"Kansas"
> "Leslie",44,"California"
> etc
> thanks ...
>
>
|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:seiic.495$mG1.3946694@.news-text.cableinet.net...

> You can try something like:
> bcp "SELECT
> '\"'+au_id,au_lname,au_fname,phone,address,city,st ate,zip,contract FROM
> Pubs.dbo.authors" queryout authors.txt -T -t"\",\"" -r"\"" -c
thank you; it's so convoluted it almost seems they don't want you to do this
... BUT it works

> or use the Bulk Export task and DTS:
> http://www.sqldts.com/default.aspx?272
interesting ...
[vbcol=seagreen]
> John
> "Liz" <liz@.tiredofspam.com> wrote in message
> news:OvIT7vYKEHA.2244@.tk2msftngp13.phx.gbl...
comma-separated
>
|||Hi
If you wish for a feature to be added to SQL Server you may want to suggest
it by emailing:
sqlwish@.microsoft.com
John
"Liz" <liz@.tiredofspam.com> wrote in message
news:O5ckPlZKEHA.808@.tk2msftngp13.phx.gbl...
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:seiic.495$mG1.3946694@.news-text.cableinet.net...
>
> thank you; it's so convoluted it almost seems they don't want you to do
this
> .. BUT it works
>
> interesting ...
>
>
> comma-separated
>

BCP Quoted CSV

Can anyone clue me in on how to get bcp to output quoted, comma-separated
values
"Bob",50,"Kansas"
"Leslie",44,"California"
etc
thanks ...Hi
Just using the -r and -t options to produce quotes will miss the first set
of quotes on each line.
You can try something like:
bcp "SELECT
'\" '+au_id,au_lname,au_fname,phone,address,
city,state,zip,contract FROM
Pubs.dbo.authors" queryout authors.txt -T -t"\",\"" -r"\"" -c
or use the Bulk Export task and DTS:
http://www.sqldts.com/default.aspx?272
John
"Liz" <liz@.tiredofspam.com> wrote in message
news:OvIT7vYKEHA.2244@.tk2msftngp13.phx.gbl...
> Can anyone clue me in on how to get bcp to output quoted, comma-separated
> values
> "Bob",50,"Kansas"
> "Leslie",44,"California"
> etc
> thanks ...
>
>|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:seiic.495$mG1.3946694@.news-text.cableinet.net...

> You can try something like:
> bcp "SELECT
> '\" '+au_id,au_lname,au_fname,phone,address,
city,state,zip,contract FROM
> Pubs.dbo.authors" queryout authors.txt -T -t"\",\"" -r"\"" -c
thank you; it's so convoluted it almost seems they don't want you to do this
.. BUT it works

> or use the Bulk Export task and DTS:
> http://www.sqldts.com/default.aspx?272
interesting ...

> John
> "Liz" <liz@.tiredofspam.com> wrote in message
> news:OvIT7vYKEHA.2244@.tk2msftngp13.phx.gbl...
comma-separated[vbcol=seagreen]
>|||Hi
If you wish for a feature to be added to SQL Server you may want to suggest
it by emailing:
sqlwish@.microsoft.com
John
"Liz" <liz@.tiredofspam.com> wrote in message
news:O5ckPlZKEHA.808@.tk2msftngp13.phx.gbl...
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:seiic.495$mG1.3946694@.news-text.cableinet.net...
>
> thank you; it's so convoluted it almost seems they don't want you to do
this
> .. BUT it works
>
> interesting ...
>
>
> comma-separated
>

BCP Quoted CSV

Can anyone clue me in on how to get bcp to output quoted, comma-separated
values
"Bob",50,"Kansas"
"Leslie",44,"California"
etc
thanks ...Hi
Just using the -r and -t options to produce quotes will miss the first set
of quotes on each line.
You can try something like:
bcp "SELECT
'\"'+au_id,au_lname,au_fname,phone,address,city,state,zip,contract FROM
Pubs.dbo.authors" queryout authors.txt -T -t"\",\"" -r"\"" -c
or use the Bulk Export task and DTS:
http://www.sqldts.com/default.aspx?272
John
"Liz" <liz@.tiredofspam.com> wrote in message
news:OvIT7vYKEHA.2244@.tk2msftngp13.phx.gbl...
> Can anyone clue me in on how to get bcp to output quoted, comma-separated
> values
> "Bob",50,"Kansas"
> "Leslie",44,"California"
> etc
> thanks ...
>
>|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:seiic.495$mG1.3946694@.news-text.cableinet.net...
> You can try something like:
> bcp "SELECT
> '\"'+au_id,au_lname,au_fname,phone,address,city,state,zip,contract FROM
> Pubs.dbo.authors" queryout authors.txt -T -t"\",\"" -r"\"" -c
thank you; it's so convoluted it almost seems they don't want you to do this
.. BUT it works
> or use the Bulk Export task and DTS:
> http://www.sqldts.com/default.aspx?272
interesting ...
> John
> "Liz" <liz@.tiredofspam.com> wrote in message
> news:OvIT7vYKEHA.2244@.tk2msftngp13.phx.gbl...
> > Can anyone clue me in on how to get bcp to output quoted,
comma-separated
> > values
> >
> > "Bob",50,"Kansas"
> > "Leslie",44,"California"
> >
> > etc
> >
> > thanks ...
> >
> >
> >
> >
>|||Hi
If you wish for a feature to be added to SQL Server you may want to suggest
it by emailing:
sqlwish@.microsoft.com
John
"Liz" <liz@.tiredofspam.com> wrote in message
news:O5ckPlZKEHA.808@.tk2msftngp13.phx.gbl...
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:seiic.495$mG1.3946694@.news-text.cableinet.net...
> > You can try something like:
> >
> > bcp "SELECT
> > '\"'+au_id,au_lname,au_fname,phone,address,city,state,zip,contract FROM
> > Pubs.dbo.authors" queryout authors.txt -T -t"\",\"" -r"\"" -c
> thank you; it's so convoluted it almost seems they don't want you to do
this
> .. BUT it works
>
> > or use the Bulk Export task and DTS:
> > http://www.sqldts.com/default.aspx?272
> interesting ...
>
>
> > John
> >
> > "Liz" <liz@.tiredofspam.com> wrote in message
> > news:OvIT7vYKEHA.2244@.tk2msftngp13.phx.gbl...
> > > Can anyone clue me in on how to get bcp to output quoted,
> comma-separated
> > > values
> > >
> > > "Bob",50,"Kansas"
> > > "Leslie",44,"California"
> > >
> > > etc
> > >
> > > thanks ...
> > >
> > >
> > >
> > >
> >
> >
>

BCP question

I am outputting table data to a file using bcp. The table consists of all CHAR datatypes, so that the output will be fixed length. The problem is that occationally a line will wrap on me. it isn't a line-lenght issue as far as I can tell, and the wrapping is occuring in the middle of the line.

My other fils come out without a hitch.

Anyone have any ideas?

TIAIt's most likely an embedded carriage return/line feed (char(13)+char(10) or either one of the above.) In order to avoid this situation you need to either fix the data with an update by replacing char(13)+char(10) with a space (' ') or create a view that selects fields individually and passes the suspected field to a function that returns the corrected value by replacing those characters with a space.|||Funny, I was just playing with that, and it turned out to be the case. No idea how the CR/LF got in there, as I actually typed the data into the VARCHAR field myself, using Enterprise Manager.

Oh, well. Without these little mysteries, how would I ever support my ulcers?|||In my case I ended up going with the view solution. What are your plans?|||The table I am reading from simply holds parameters for SAP files. It is a small table that will be maintained manually, so I will just control the input.|||I am running bcp from within a stored procedure, so I suppose I could use REPLACE on the parameter values to exclude and CR/LF values (just to be safe).

BCP puts nulls in output text file

Hi,

I have been trying to output varchar fields from a table to a text file using BCP. When the field in the table is an empty string (where I have specifically set it to '') BCP places an ascii null in the output text file.

Is there a way I can tell it to just not place anything in the output for this field.

Other inportant information: I am trying to use comma as the field terminator (-t,). If I use the default tab terminator, then things seem to be ok.

Thanks for any help.
ScottWhat does your bcp command look like?

with -c it should be OK.

from
create table bcp (s varchar(10), t varchar(10) null, u varchar(10) not null, v varchar(10))
insert bcp select 'asd', 'asd', 'asd', 'asd'
insert bcp select 'asd', '', '', 'asd'
insert bcp select 'asd', 'asd', 'asd', 'asd'
exec master..xp_cmdshell 'bcp test..bcp out c:\bcpfile.txt -S(local) -T -t, -c'

I get
asd,asd,asd,asd
asd, , ,asd
asd,asd,asd,asd|||The command I used was very similar to yours and when I ran yours, I got the same results you did. However, notice that where the fields in the table are empty, bcp puts a space between the commas (, ,). What I would like to see is just (,,) with no space between.

If I allow nulls in the table, then the output file contains a null (ASCII 0) in that spot between the commas.

Now, if I use DTS to output the file, then everything comes out as I want it to. I just wanted to avoid the use of DTS for something so simple.

Thanks for your help.
Scott|||However, notice that where the fields in the table are empty, bcp puts a space between the commas (, ,). What I would like to see is just (,,) with no space between.

Actually the fields are NOT empty, they contained a zero length string. This is not the same as empty. The ASCII 0 or (, ,) is a zero length string, if you insert a NULL then your file will only contain (,,).sql

BCP Problems

Hello, I am wanting to take the output of sp_who2 to and
output file and then send that file in an email message
all within a PROC. I was thinking that I should
use "xp_cmdShell" and execute the BCP command. After
trying a number of times to get it to work, someone
provided me the following and indicated that it work find
on their machine. So all I did was change the server
address and run, but it still generates an error. Here
is the command:
master..xp_cmdshell 'bcp "exec master..sp_who" queryout
"C:\SQLTestOutput.txt" -c -T -S "Fred\NetSDK" '
Here is the error that I get.
usage: bcp {dbtable | query} {in | out | queryout |
format} datafile
[-m maxerrors] [-f formatfile] [-e
errfile]
[-F firstrow] [-L lastrow] [-b
batchsize]
[-n native type] [-c character type] [-w
wide character type]
[-N keep non-text native] [-V file format version] [-q
quoted identifier]
[-C code page specifier] [-t field terminator] [-r
row terminator]
[-i inputfile] [-o outfile] [-a
packetsize]
[-S server name] [-U username] [-P
password]
[-T trusted connection] [-v version] [-R
regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"]
NULL
(12 row(s) affected)
There is no output file created - so I can not tell if it
is telling me that I have a format error, or if this is
the type of message I would get if it works. If it
worked, where is the file?I figured it out - it did not like the command spread
over two lines.
>--Original Message--
>Hello, I am wanting to take the output of sp_who2 to and
>output file and then send that file in an email message
>all within a PROC. I was thinking that I should
>use "xp_cmdShell" and execute the BCP command. After
>trying a number of times to get it to work, someone
>provided me the following and indicated that it work
find
>on their machine. So all I did was change the server
>address and run, but it still generates an error. Here
>is the command:
>master..xp_cmdshell 'bcp "exec master..sp_who" queryout
>"C:\SQLTestOutput.txt" -c -T -S "Fred\NetSDK" '
>Here is the error that I get.
>usage: bcp {dbtable | query} {in | out | queryout |
>format} datafile
> [-m maxerrors] [-f formatfile] [-e
>errfile]
> [-F firstrow] [-L lastrow] [-b
>batchsize]
> [-n native type] [-c character type] [-w
>wide character type]
> [-N keep non-text native] [-V file format version] [-q
>quoted identifier]
> [-C code page specifier] [-t field terminator] [-r
>row terminator]
> [-i inputfile] [-o outfile] [-a
>packetsize]
> [-S server name] [-U username] [-P
>password]
> [-T trusted connection] [-v version] [-R
>regional enable]
> [-k keep null values] [-E keep identity values]
> [-h "load hints"]
>NULL
>(12 row(s) affected)
>There is no output file created - so I can not tell if
it
>is telling me that I have a format error, or if this is
>the type of message I would get if it works. If it
>worked, where is the file?
>.
>sql

BCP output with header and trailer

I'm copying data out to a file with pipe delimiters. I would like to
add a header and trailer.

Is this possible? If so, please help me with the steps.

Also, is it possible to append to a text file while doing the bcp? If
so, how?"sbh" <stephanie.herbert@.tdh.state.tx.us> wrote in message
news:1d68006.0403040850.23a28ccb@.posting.google.co m...
> I'm copying data out to a file with pipe delimiters. I would like to
> add a header and trailer.
> Is this possible? If so, please help me with the steps.
> Also, is it possible to append to a text file while doing the bcp? If
> so, how?

Not directly, but you might be able to use -queryout or osql.exe -o to
execute a query or stored procedure which returns your header and trailer
information as well as the data. Since you don't say what you want in your
header/trailer, it's hard to be more precise. As for appending, I believe
that both bcp and osql overwrite any existing file, but a simple script in
Perl, VBScript etc. would be a more flexible solution.

Simonsql

BCP output - putting double quotes around text

Folks,
How can I program BCP to output text items in double quotes (")?
Here is an example (please try it) that trys to output some columns from a
table to csv file. However, due to the existence of commas within the
fields, the comma separation gets messed up.
USE [MASTER]
IF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'mcg1')
DROP TABLE mcg1
go
CREATE TABLE mcg1
(pk INT IDENTITY(1,1)
,Address_1 VARCHAR(100)
,City VARCHAR(100))
go
INSERT INTO mcg1 (Address_1, City) VALUES ('100 Road1, Suburb1' , 'BigCity1'
)
INSERT INTO mcg1 (Address_1, City) VALUES ('200 Road2, Suburb2' , 'BigCity2'
)
SELECT * FROM mcg1
Exec Master..xp_Cmdshell 'bcp "SELECT Address_1, City FROM mcg1" queryout
"C:\mcg1.csv" -c -t,"'
The output I get is below. You can see how the use of commas in the text
makes the comma separate list all
100 Road1, Suburb1,BigCity1
200 Road2, Suburb2,BigCity2
Thus what I want is
"100 Road1, Suburb1","BigCity1"
"200 Road2, Suburb2","BigCity2"
You can do this OK in DTS by specifying the text identifier to be
double-quotes.
I do NOT want to use DTS and want to be able to do via a T-SQL procedure.
Note that the real table I will export from has numeric datatypes and I woul
d
prefer NOT to wrap them in double-quotes too.
Thus, how can I alter the Exec Master..xp_Cmdshell command, to wrap each
text field in double quotes. I may have to use a format file in which case
please provide the format file too.
Thanks in advance
Mgale1Exec Master..xp_Cmdshell 'bcp "SELECT '"' + Address_1 + '"', '"' + City + '"
'
FROM mcg1" queryout
"C:\mcg1.csv" -c -t,"'
What you see above is a single quote, followed by a double-quote, followed
by another single quote. Instead of trying to get bcp to do the formatting,
have the query do it.
"mgale1" wrote:

> Folks,
> How can I program BCP to output text items in double quotes (")?
> Here is an example (please try it) that trys to output some columns from a
> table to csv file. However, due to the existence of commas within the
> fields, the comma separation gets messed up.
> --
> USE [MASTER]
> IF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'mcg1')
> DROP TABLE mcg1
> go
> CREATE TABLE mcg1
> (pk INT IDENTITY(1,1)
> ,Address_1 VARCHAR(100)
> ,City VARCHAR(100))
> go
> INSERT INTO mcg1 (Address_1, City) VALUES ('100 Road1, Suburb1' , 'BigCity
1')
> INSERT INTO mcg1 (Address_1, City) VALUES ('200 Road2, Suburb2' , 'BigCity
2')
> SELECT * FROM mcg1
> Exec Master..xp_Cmdshell 'bcp "SELECT Address_1, City FROM mcg1" queryout
> "C:\mcg1.csv" -c -t,"'
> --
> The output I get is below. You can see how the use of commas in the text
> makes the comma separate list all
> 100 Road1, Suburb1,BigCity1
> 200 Road2, Suburb2,BigCity2
> Thus what I want is
> "100 Road1, Suburb1","BigCity1"
> "200 Road2, Suburb2","BigCity2"
> You can do this OK in DTS by specifying the text identifier to be
> double-quotes.
> I do NOT want to use DTS and want to be able to do via a T-SQL procedure.
> Note that the real table I will export from has numeric datatypes and I wo
uld
> prefer NOT to wrap them in double-quotes too.
> Thus, how can I alter the Exec Master..xp_Cmdshell command, to wrap each
> text field in double quotes. I may have to use a format file in which cas
e
> please provide the format file too.
> --
> Thanks in advance
> Mgale1|||Sorry, didn't read through your entire post.
Exec Master..xp_Cmdshell 'bcp "SELECT CASE WHEN ISNUMERIC(Address_1) = 1
THEN Address_1 ELSE ''"'' + Address_1 + ''"'' END, CASE WHEN ISNUMERIC(City)
= 1 THEN City ELSE ''"'' + City + ''"'' END
FROM mcg1" queryout
"C:\mcg1.csv" -c -t,"'
Couple of things about the above:
-ISNUMERIC has been known to evaluate to 1 for things that aren't really
numeric. See http://www.aspfaq.com/show.asp?id=2390.
-Whatever datatype Address_1 and City are, if they are not numeric, must be
implicitly convertible to a character data type. If it isn't, you could use
CAST or CONVERT to force it.
"Mark Williams" wrote:
> Exec Master..xp_Cmdshell 'bcp "SELECT '"' + Address_1 + '"', '"' + City +
'"'
> FROM mcg1" queryout
> "C:\mcg1.csv" -c -t,"'
> What you see above is a single quote, followed by a double-quote, followed
> by another single quote. Instead of trying to get bcp to do the formatting
,
> have the query do it.
> --
>
> "mgale1" wrote:
>|||mgale1 (mgale1@.discussions.microsoft.com) writes:
> How can I program BCP to output text items in double quotes (")?
You could use a format file:
8.0
4
1 SQLCHAR 0 0 "\"" 0 ""
2 SQLCHAR 0 0 "\",\"" 1 col1 ""
3 SQLCHAR 0 0 "\",\"" 2 col2 ""
4 SQLCHAR 0 0 "\"\r\n" 3 col3 ""
This format file defines an output for three fields on the form
"data","more data","even, more, data"
There are four fields in the format file, because there are to be an
empty field to get the first " in place. The 0 on that row, means that
there is no database-column mapping here.
I will need to add that I've only tried this for input, not for output.
But it should work...
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Folks,
Thanks for your replies - I am grateful for your help.
Unfortunately, I dont think your suggestions are going to work for me
Mark Williams - I cant get your syntax to work at all. Query Analyser gets
over all the quotes and simply wont run the BCP command. Instead i
t
returns the standard BCP error msg like 'BCP commands should be in the form
of..." etc
Erland - I have be having trouble getting your example to work. My command
is Exec Master..xp_Cmdshell 'bcp "SELECT Address_1, City FROM mcg1"
queryout "C:\mcg1.csv" -t, -f c:\formatfile.txt"' and I get "Host-file
columns may be skipped only when copying into the Server" as an error.
Thanks for your help - another colleague has found a way around this problem
for me by using DTSRUN on a command line. Thus please dont put too much
effort into working on this any further unless it is your wish
Thanks again, much appreciated
Mgale1|||mgale1 (mgale1@.discussions.microsoft.com) writes:
> Erland - I have be having trouble getting your example to work. My
> command is Exec Master..xp_Cmdshell 'bcp "SELECT Address_1, City FROM
> mcg1" queryout "C:\mcg1.csv" -t, -f c:\formatfile.txt"' and I get
> "Host-file columns may be skipped only when copying into the Server" as
> an error.
Drat, it didn't work out. Hm, shat if you change the SELECT to
SELECT '', Address_1, City FROM mcgl
and update the format file to read 1 2 3 and 0 1 2 in the database-
column column?
(Sorry for not testing myself, but it's about bed-time for me.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

BCP output

Hi All,
The syntaxt below is not outputting any text file at the location
specified. Any Ideas !
use tempdb
go
Create view vw_bcpMasterSysobjects as
select
name = '"' + name + '"' ,
crdate = '"' + convert(varchar(8), crdate, 112) + '"' ,
crtime = '"' + convert(varchar(8), crdate, 108) + '"'
from master..sysobjects
go
declare @.sql varchar(8000)
select @.sql = 'bcp "select * from
tempdb..vw_bcpMasterSysobjects
order by crdate desc, crtime desc"
queryout c:\sysobjects.txt -c -t, -T -
S'
+ @.@.servername
exec master..xp_cmdshell @.sql
Thanks in advance
bcp "select * from tempdb..vw_bcpMasterSysobjects order by crdate
desc, crtime desc" queryout d:\sysobjects.txt -c -t, -T -SA03
is working fine . Put the bcp in a single line . There is no space
between S and server name . Also be aware , this will create a file in
SQL Server System and not in client system where you execute the code
M A Srinivas
On Mar 9, 1:42 pm, "Swagener" <riqb...@.gmail.com> wrote:
> Hi All,
> The syntaxt below is not outputting any text file at the location
> specified. Any Ideas !
> use tempdb
> go
> Create view vw_bcpMasterSysobjects as
> select
> name = '"' + name + '"' ,
> crdate = '"' + convert(varchar(8), crdate, 112) + '"' ,
> crtime = '"' + convert(varchar(8), crdate, 108) + '"'
> from master..sysobjects
> go
> declare @.sql varchar(8000)
> select @.sql = 'bcp "select * from
> tempdb..vw_bcpMasterSysobjects
> order by crdate desc, crtime desc"
> queryout c:\sysobjects.txt -c -t, -T -
> S'
> + @.@.servername
> exec master..xp_cmdshell @.sql
> Thanks in advance
|||works fine on my server -- the only difference is that I put the bcp line as
1 line , not spread across 3.
Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=http://www.itjobfeed.com">UK IT Jobs</a>
"Swagener" <riqband@.gmail.com> wrote in message
news:1173429734.273739.316500@.j27g2000cwj.googlegr oups.com...
> Hi All,
> The syntaxt below is not outputting any text file at the location
> specified. Any Ideas !
> use tempdb
> go
> Create view vw_bcpMasterSysobjects as
> select
> name = '"' + name + '"' ,
> crdate = '"' + convert(varchar(8), crdate, 112) + '"' ,
> crtime = '"' + convert(varchar(8), crdate, 108) + '"'
> from master..sysobjects
> go
> declare @.sql varchar(8000)
> select @.sql = 'bcp "select * from
> tempdb..vw_bcpMasterSysobjects
> order by crdate desc, crtime desc"
> queryout c:\sysobjects.txt -c -t, -T -
> S'
> + @.@.servername
> exec master..xp_cmdshell @.sql
>
> Thanks in advance
>
|||Thanks for all these replies,
I have managed to run the code with your guys help.
It was the bcp syntax splitted into 3 lines.
Thanks again.

BCP output

Hi All,
The syntaxt below is not outputting any text file at the location
specified. Any Ideas !
use tempdb
go
Create view vw_bcpMasterSysobjects as
select
name = '"' + name + '"' ,
crdate = '"' + convert(varchar(8), crdate, 112) + '"' ,
crtime = '"' + convert(varchar(8), crdate, 108) + '"'
from master..sysobjects
go
declare @.sql varchar(8000)
select @.sql = 'bcp "select * from
tempdb..vw_bcpMasterSysobjects
order by crdate desc, crtime desc"
queryout c:\sysobjects.txt -c -t, -T -
S'
+ @.@.servername
exec master..xp_cmdshell @.sql
Thanks in advancebcp "select * from tempdb..vw_bcpMasterSysobjects order by crdate
desc, crtime desc" queryout d:\sysobjects.txt -c -t, -T -SA03
is working fine . Put the bcp in a single line . There is no space
between S and server name . Also be aware , this will create a file in
SQL Server System and not in client system where you execute the code
M A Srinivas
On Mar 9, 1:42 pm, "Swagener" <riqb...@.gmail.com> wrote:
> Hi All,
> The syntaxt below is not outputting any text file at the location
> specified. Any Ideas !
> use tempdb
> go
> Create view vw_bcpMasterSysobjects as
> select
> name = '"' + name + '"' ,
> crdate = '"' + convert(varchar(8), crdate, 112) + '"' ,
> crtime = '"' + convert(varchar(8), crdate, 108) + '"'
> from master..sysobjects
> go
> declare @.sql varchar(8000)
> select @.sql = 'bcp "select * from
> tempdb..vw_bcpMasterSysobjects
> order by crdate desc, crtime desc"
> queryout c:\sysobjects.txt -c -t, -T -
> S'
> + @.@.servername
> exec master..xp_cmdshell @.sql
> Thanks in advance|||works fine on my server -- the only difference is that I put the bcp line as
1 line , not spread across 3.
--
Jack Vamvas
___________________________________
The latest IT jobs - www.ITjobfeed.com
<a href="http://links.10026.com/?link=uk/">http://www.itjobfeed.com">UK IT Jobs</a>
"Swagener" <riqband@.gmail.com> wrote in message
news:1173429734.273739.316500@.j27g2000cwj.googlegroups.com...
> Hi All,
> The syntaxt below is not outputting any text file at the location
> specified. Any Ideas !
> use tempdb
> go
> Create view vw_bcpMasterSysobjects as
> select
> name = '"' + name + '"' ,
> crdate = '"' + convert(varchar(8), crdate, 112) + '"' ,
> crtime = '"' + convert(varchar(8), crdate, 108) + '"'
> from master..sysobjects
> go
> declare @.sql varchar(8000)
> select @.sql = 'bcp "select * from
> tempdb..vw_bcpMasterSysobjects
> order by crdate desc, crtime desc"
> queryout c:\sysobjects.txt -c -t, -T -
> S'
> + @.@.servername
> exec master..xp_cmdshell @.sql
>
> Thanks in advance
>|||It worked just fine for me.
1. Make sure that the bcp command is in one line.
2. Print the contents of the @.sql variable and post it here.
3. Also provide error messages.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Swagener" <riqband@.gmail.com> wrote in message
news:1173429734.273739.316500@.j27g2000cwj.googlegroups.com...
> Hi All,
> The syntaxt below is not outputting any text file at the location
> specified. Any Ideas !
> use tempdb
> go
> Create view vw_bcpMasterSysobjects as
> select
> name = '"' + name + '"' ,
> crdate = '"' + convert(varchar(8), crdate, 112) + '"' ,
> crtime = '"' + convert(varchar(8), crdate, 108) + '"'
> from master..sysobjects
> go
> declare @.sql varchar(8000)
> select @.sql = 'bcp "select * from
> tempdb..vw_bcpMasterSysobjects
> order by crdate desc, crtime desc"
> queryout c:\sysobjects.txt -c -t, -T -
> S'
> + @.@.servername
> exec master..xp_cmdshell @.sql
>
> Thanks in advance
>|||Thanks for all these replies,
I have managed to run the code with your guys help.
It was the bcp syntax splitted into 3 lines.
Thanks again.