bcp with quotes

Question

Dear buddies,

I am having a comma delimited data file created using bcp but I also need to include quotes for char fields.

How can I go about it.

I tried to use format file but still couldnt get it working.

I tried using methods stated here as well: http://www.sqlservercentral.com/Forums/Topic457120-19-1.aspx

but managed to get only some parts with quotes.

EG:

Alagu, 20, running around

So, I want it to be

"Alagu", 20, "running around"

Methods I tried definetely gave these output:

Alagu","20","running around

No quotes at the beginning and end.

Please help me.

Thanks a lot.

Answer 1

Try this in queryout option of BCP

bcp "SELECT CASE WHEN ISNUMERIC(Address_1) = 1 THEN field1 ELSE ''"'' + field1 + ''"'' END

Answer 2

I am pretty sure that the issue is quoting columns, not quoting individual column contents based on how the data in each row may or may not convert.

I was going to explain, but I see that Nigel Rivett already has a reference page that should help:
http://www.nigelrivett.net/SQLTsql/BCP_quoted_CSV_Format_file.html

Look at the last example: Quote delimitted fields in data file

His example is bcp in, not out, but note how the leading and ending quotes are defined.  That is what I understand your problem to be.

RLF

Answer 3

You would have to play around with a format file and BCP. Or you could use Export/Import Wizard.

Reference: http://blogs.msdn.com/b/sqlserverfaq/archive/2010/02/04/how-to-remove-unwanted-quotation-marks-while-importing-a-data-file.aspx

---

This posting is provided "AS IS" with no warranties, and confers no rights.

---

My Blog: http://troubleshootingsql.wordpress.com
Twitter: @banerjeeamit
SQL Server FAQ Blog on MSDN: http://blogs.msdn.com/sqlserverfaq

Answer 4

Dear Amit, I have to apply this to few hundred tables, so, I won't be able to use wizards.

Thanks you very much for your reply.

 

Answer 5

Is this not works?

bcp "SELECT CASE WHEN ISNUMERIC(Address_1) = 1 THEN field1 ELSE ''"'' + field1 + ''"'' END

Answer 6

Thanks to all of you.

Managed to find a way to load the files without using quotes.

Thanks for all of your valuable time.