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 Problems with ISO Latin-1 Characters past ASCII 192

I am trying to export data from a table with char and varchar fields
(Non-Unicode) that contains the ISO Latin-1 Characters. The problem exist
with the character past ASCII 192 the characters like , , , etc. My
export statement looks like this as I am using the -c flag.
bcp database.dbo.table out C:\table.txt" -c -t"|" -SMACHINE -Usa -P
But I get the following:
MXICO| |MX|0|1
The data in the table looks like this
MXICO| |MX|0|1
-n (Native) did not seem to work.
-w (Unicode character) did work, geting it out, but I can not put it back
in...
Do I have some sort of code page/collation not set correct?
Thanks
Reeves
Hi
Have you tried -C RAW?
John
"Reeves Smith" <ReevesSmith@.WillTellYouLater.com> wrote in message
news:uXjvY5UVEHA.3988@.tk2msftngp13.phx.gbl...
> I am trying to export data from a table with char and varchar fields
> (Non-Unicode) that contains the ISO Latin-1 Characters. The problem exist
> with the character past ASCII 192 the characters like , , , etc. My
> export statement looks like this as I am using the -c flag.
> bcp database.dbo.table out C:\table.txt" -c -t"|" -SMACHINE -Usa -P
> But I get the following:
> MXICO| |MX|0|1
> The data in the table looks like this
> MXICO| |MX|0|1
> -n (Native) did not seem to work.
> -w (Unicode character) did work, geting it out, but I can not put it back
> in...
> Do I have some sort of code page/collation not set correct?
> Thanks
> Reeves
>
|||Reeves,
We had a similar problem that I believe was fixed when we switched to
sing -C ACP. (RAW may do for you as well, try it out.) The problem is
that the default is -C OEM, which is basically the old DOS character set and
that is trashing some characters for you.
Russell Fields
"Reeves Smith" <ReevesSmith@.WillTellYouLater.com> wrote in message
news:uXjvY5UVEHA.3988@.tk2msftngp13.phx.gbl...
> I am trying to export data from a table with char and varchar fields
> (Non-Unicode) that contains the ISO Latin-1 Characters. The problem exist
> with the character past ASCII 192 the characters like , , , etc. My
> export statement looks like this as I am using the -c flag.
> bcp database.dbo.table out C:\table.txt" -c -t"|" -SMACHINE -Usa -P
> But I get the following:
> MXICO| |MX|0|1
> The data in the table looks like this
> MXICO| |MX|0|1
> -n (Native) did not seem to work.
> -w (Unicode character) did work, geting it out, but I can not put it back
> in...
> Do I have some sort of code page/collation not set correct?
> Thanks
> Reeves
>

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 Errors

I execute a store procedure like
Sploadnew 'may'
The may is char type which is converted to a datetime and the query is
something like and I create a tem table
SELECT into ##tetable SUM(sales)+SUM(sales1)
FROM table
group by state
where datepart(mm,prod_date)=datepart(mm,@.datenew)
I want to BCP this result to a server location from within the store
procedure and I did
exec master..xp_cmdshell BCP ##tetable
OUT "//server/folder/rsult.txt"
-S server
-U sa
-P Password
I get this error
erver: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '##tetable'
what is wrong in this approach
Any Answers?I'm not quite sure where the error is occurring here, but
one way to help to determine this is instead of using the
actual BCP command syntax within the xp_cmdshell call
(which may be what is causing the SQL parser to find an
error), put your BCP command in a Windows NT .cmd command
file. Then use xp_cmdshell to execute this .cmd file.
This should eliminate any wacky allowable syntax
differences between what BCP allows and what the SQL
parser allows.
Also, I don't know if you left this out just in your post
or if you left it out in your stored proc, but you might
want to try enclosing the entire BCP command syntax in
single quotes:
exec master..xp_cmdshell 'BCP ##tetable
OUT "//server/folder/rsult.txt"
-S server
-U sa
-P Password'
generally, with xp_cmdshell, if your command syntax
includes spaces, the commnad syntax needs to be within
single quotes.
I hope that this helps.
Matthew Bando
BandoM@.CSCTechnologies (remove) . com
>--Original Message--
>I execute a store procedure like
>Sploadnew 'may'
>The may is char type which is converted to a datetime
and the query is
>something like and I create a tem table
>SELECT into ##tetable SUM(sales)+SUM(sales1)
>FROM table
>group by state
>where datepart(mm,prod_date)=datepart(mm,@.datenew)
>I want to BCP this result to a server location from
within the store
>procedure and I did
>exec master..xp_cmdshell BCP ##tetable
>OUT "//server/folder/rsult.txt"
>-S server
>-U sa
>-P Password
> I get this error
>erver: Msg 170, Level 15, State 1, Line 1
>Line 1: Incorrect syntax near '##tetable'
>what is wrong in this approach
>Any Answers?
>
>.
>

bcp

Hello World,
I have to load table from a a delimited file. The delimited char is (";").
Bcp is used with a format file (*.fmt).
First step :
The table contain a date field.
In the import file, the date format is : DD/MM/YYYY.
When the import runs, I obtain the message like : right truncating, so the
import stops.
Second step :
The struct table is modified with a varchar (10) for the date field.
The format file is modified.
The import runs well.
My question is :
Can we import directly date field from a file with bcp ?
If it is possible, how can we do it ?
Thank's in advance.
MLTry reformatting the source file so that the datatime values are in an
unambiguous format "yyyymmdd", or (which is my favourite) connect to the fil
e
using sp_addlinkedserver and describe it's format using the schema.ini file,
which enables you to query the file as if it were a regular SQL table.
Read more here:
http://msdn.microsoft.com/library/d... />
a_8gqa.asp
(Example H.)
...and here:
http://msdn.microsoft.com/library/d...ma_ini_file.asp
ML|||Hi
I would expect that you would get a message about the date being invalid if
dates were being interpretted as MM/DD/YYYY, therefore it may not be your
date field where this error message is being generated!!!
You may want to post your format file, command and a small section of
example data that re-creates this problem.
John
"Michel" wrote:

> Hello World,
> I have to load table from a a delimited file. The delimited char is (";").
> Bcp is used with a format file (*.fmt).
> First step :
> The table contain a date field.
> In the import file, the date format is : DD/MM/YYYY.
> When the import runs, I obtain the message like : right truncating, so the
> import stops.
> Second step :
> The struct table is modified with a varchar (10) for the date field.
> The format file is modified.
> The import runs well.
> My question is :
> Can we import directly date field from a file with bcp ?
> If it is possible, how can we do it ?
>
> Thank's in advance.
> ML
>
>|||Michel wrote:
> Hello World,
> I have to load table from a a delimited file. The delimited char is
> (";").
> Bcp is used with a format file (*.fmt).
> First step :
> The table contain a date field.
> In the import file, the date format is : DD/MM/YYYY.
> When the import runs, I obtain the message like : right truncating,
> so the import stops.
> Second step :
> The struct table is modified with a varchar (10) for the date field.
> The format file is modified.
> The import runs well.
> My question is :
> Can we import directly date field from a file with bcp ?
> If it is possible, how can we do it ?
You can use DTS and define a transformation for this column.
You could also try to not use bcp but do it from QA: change the session's
date format and use BULK INSERT (untested)
http://support.microsoft.com/defaul...kb;en-us;173907
http://msdn.microsoft.com/library/d...br />
4fec.asp
Kind regards
robert