显示标签为“quotes”的博文。显示所有博文
显示标签为“quotes”的博文。显示所有博文

2012年3月20日星期二

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 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

2012年3月11日星期日

BCP import - remove quotes from CSV File

My bcp process is now working. FINALLY.
But, my import data file is comma-delimited and everything is surrounded
by quotes; therefore, they data goes into the SQL databases with quotes.
Any suggestions on how I can remove these quotes, either before,during
or after the BCP process?
Sample CSV file:
"204980","33404","Cindy W
Crutcher","O","502-839-9822","A","502-680-9822","H","502-839-5679","4295
00535","Exit Realty Crutcher Team","502-839-9822","53501","Mark
Crutcher","502-839-9822","Residential","05/17/02","","","186900.00","05/
21/02
*** Sent via Developersdex http://www.examnotes.net ***As you specified the column delimiter as “comma”, you can specify the te
xt
qualifier as “quotes”. It will solve the problem.
"Joey Martin" wrote:

> My bcp process is now working. FINALLY.
> But, my import data file is comma-delimited and everything is surrounded
> by quotes; therefore, they data goes into the SQL databases with quotes.
> Any suggestions on how I can remove these quotes, either before,during
> or after the BCP process?
> Sample CSV file:
> "204980","33404","Cindy W
> Crutcher","O","502-839-9822","A","502-680-9822","H","502-839-5679","4295
> 00535","Exit Realty Crutcher Team","502-839-9822","53501","Mark
> Crutcher","502-839-9822","Residential","05/17/02","","","186900.00","05/
> 21/02
> *** Sent via Developersdex http://www.examnotes.net ***
>|||How would I do that with the bcp function? Or is it something that I
would do later?
*** Sent via Developersdex http://www.examnotes.net ***|||If you need to use the bcp you should create a bcp format file.
something like the following:-
6.0
4
1 SQLDATETIME 0 8 "\t" 1 ErrorDate
2 SQLCHAR 0 255 "\t" 3 ErrorMsg
3 SQLCHAR 0 255 "\t" 2 ErrorCaption
4 SQLCHAR 0 255 "\n" 4 ErrorClass
The problem is on the bcp utility you are allowed to only to specify the
field delimiter using [/t field_term] option.
I think the best way to do it to create a DTS package transfer the data from
your csv file to a table on SQL server. On the DTS package you can specify
the text
qualifier as “quotes”. Then you have an option to save it as a DTS packa
ge
or VBS.
I think the DTS is better than creating the bcp format file.
-Nader
"Joey Martin" wrote:

> How would I do that with the bcp function? Or is it something that I
> would do later?
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Joey Martin (joey@.infosmiths.net) writes:
> My bcp process is now working. FINALLY.
> But, my import data file is comma-delimited and everything is surrounded
> by quotes; therefore, they data goes into the SQL databases with quotes.
> Any suggestions on how I can remove these quotes, either before,during
> or after the BCP process?
> Sample CSV file:
> "204980","33404","Cindy W
> Crutcher","O","502-839-9822","A","502-680-9822","H","502-839-5679","4295
> 00535","Exit Realty Crutcher Team","502-839-9822","53501","Mark
> Crutcher","502-839-9822","Residential","05/17/02","","","186900.00","05/
> 21/02
As Nader said, you need to use a format file. Here is a sample of how it
would look like:
8.0
4
1 SQLCHAR 0 0 "\"" 0 "" ""
2 SQLCHAR 0 0 "\",\"" 1 col1 ""
3 SQLCHAR 0 0 "\",\"" 2 col2 ""
4 SQLCHAR 0 0 "\"\n" 3 col3 ""
This is fitted for a CSV file with three fields like:
"Field1","Field2","Field3"
The trick is that we define the file as having four fields. The first
field is just an empty dummy which we don't import. This is why it says
0 in the database-column field.
Also in a format file, what counts is the column numbers. Column names
are just informational, and not used by BCP.
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

2012年2月25日星期六

Bcp data with quotes

I have a table with the following data
A_Id A_Name A_Desig
A324 Author1 Script Writer
T533 Tester Test cases
Now I want to export the data to text file with the following format
"A324" "Author1" "Script Writer"
"T533" "Tester" "Test cases"
Declare @.str varchar(1000) ,@.FileName varchar(100), @.table
varchar(100)
set @.table='Author'
set @.FileName='C:\Author.txt'
set @.str='Exec Master..xp_Cmdshell ''bcp "Select * from
'+db_name()+'..'+@.table+'" queryout "'+@.FileName+'" -t """","""" -c"'''
Exec(@.str)
But I get the result with first and last quotes missing
A324" "Author1" "Script Writer
T533" "Tester" "Test cases
How do I get the desired result?
MadhivananHi Madhivanan
You have a couple of choices. You could either specify a format file or add
the additional "" to your select statement. I've created a quick example
against the pubs database table employee for you ...
bcp "select '"""'+emp_id+'"""', '"""'+fname+'"""' from pubs..Employee"
queryout c:\outputfile.txt -c -t, -Ssql-test -T
This result in...
"A-C71970F","Aria"
"A-R89858F","Annette"
"AMD15433F","Ann"
"ARD36773F","Anabela"
"CFH28514M","Carlos"
"CGS88322F","Carine"
"DBT39435M","Daniel"
"DWR65030M","Diego"
HTH. Ryan
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1138178304.808428.247350@.f14g2000cwb.googlegroups.com...
>I have a table with the following data
> A_Id A_Name A_Desig
> A324 Author1 Script Writer
> T533 Tester Test cases
> Now I want to export the data to text file with the following format
> "A324" "Author1" "Script Writer"
> "T533" "Tester" "Test cases"
> Declare @.str varchar(1000) ,@.FileName varchar(100), @.table
> varchar(100)
> set @.table='Author'
> set @.FileName='C:\Author.txt'
> set @.str='Exec Master..xp_Cmdshell ''bcp "Select * from
> '+db_name()+'..'+@.table+'" queryout "'+@.FileName+'" -t """","""" -c"'''
> Exec(@.str)
> But I get the result with first and last quotes missing
> A324" "Author1" "Script Writer
> T533" "Tester" "Test cases
>
> How do I get the desired result?
> Madhivanan
>|||Thanks, Ryan
When I run your query, I get yhis error
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '+emp_id+'.
Madhivanan|||Madhivanan (madhivanan2001@.gmail.com) writes:
> Thanks, Ryan
> When I run your query, I get yhis error
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near '+emp_id+'.
A query? Ryan did not post a query. He posted something run in a command-
line window. (I tested it, and it works, once I had corrected the
table name, and removed the -S option to run on the local server.)
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|||Thanks Erland
I also made some changes to work
bcp "select '"""'+emp_id+'"""', '"""'+fname+'"""' from pubs..Employee"
queryout c:\outputfile.txt -c -q
Madhivanan

2012年2月23日星期四

bcp and text qualifying

I would like to output to a csv file using bcp with the fields text qualified by double quotes. Like this:

"1","John Doe","100 main st"
"2","John Smith","101 main st"
"3","John Johnson","102 main st"

Right now here's what my bcp command looks like:
bcp "select id, name, address from People" QUERYOUT d:\data.csv -S aserver -T -t"," -R

And it returns results without quotes:
1,John Doe,100 main st
2,John Smith,101 main st
3,John Johnson,102 main st

How to I get the results in the file with quotes? Thanks for the help.I believe it is

/t"\",\""

The inner double quotes have to be escaped. Hope this helps.|||I tried that, which works for every field, except the 2 outside fields. The first field doesn't have a begining quote and the last field doesnt have an ending quote.

Thanks for the reply though. I appricate the help. Same goes to anyone else who has an idea!|||Alright, so now I tried using a format file, and it worked well, I was able to get double quotes around every field except the first one. So I did some research and supposedly you can add the line:
1 SQLCHAR 0 0 "\"" 1 first_quote ""
to your format file, and it will add a " to the beginning of each record.

Upon trying this I get the error: Error = [Microsoft][ODBC SQL Server Driver]Host-file columns may be skipped only when copying into the Server

Doing research on the error I came up with nothing. Any ideas?|||Has anyone created a csv file with text qualifiers using the bcp command?|||Originally posted by WhiZa
Has anyone created a csv file with text qualifiers using the bcp command?

Did you try to format each field in the select command ?
Maybe this is not the best way but is working.|||I guess I figured it out.

I my select statement I added a field like this SELECT '', * FROM...

After doing this I was able to add 1 SQLCHAR 0 0 "\"" 1 first_quote ""
to the format file, and I was able to add a " to the begining of each record. Acctualy it adds a space and a " to each record, but it works all the same.

I hope this helps someone out, it took my slow brain a while to figure it out :)