5

I tried the following command

USE <DBNAME>
SELECT 'exec master..xp_cmdshell'
+ ' '''
+ 'bcp'
+ ' ' + TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME
+ ' out'
+ ' D:\'
+ TABLE_NAME + '.csv'
+ ' -c'
+ ' -t,'
+ ' -T'
+ ' -S' + @@SERVERNAME
+ ''''
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

I want to get the data in following format.

Format will be Pipe delimited text file, with double quote text qualifiers and no header row. How to modify or write the BCP command to get the required result.

My dataset is just a customer table.

CustomerID  CustomerName         ContactName   Address        City    PostalCode  Country
1           Alfreds Futterkiste  Maria Anders  Obere Str. 57  Berlin  12209       Germany

Required data format is

1|"Alfreds Futterkiste"|"Maria Anders"|"Obere Str. 57 Berlin"|12209|"Germany"
  • I appreciate Andriy. – JP Chauhan Aug 18 '15 at 9:08
  • Is BCP the only acceptable means of generating your output or would you accept other tools that will be available on your server? – billinkc Aug 18 '15 at 13:40
7

Now that I better understand that you are trying to extract data, here is a new answer. This just simply extracts data from a table that I created in tempdb.

use tempdb

-- drop table mytable

create table mytable 
(id int, customer_name varchar(55), cityname varchar(55), statename varchar(55))

insert into mytable 
values (1, 'a', 'a city', 'a state')
     , (2, 'b', 'b city', 'b state')
     , (3, 'c', 'c city', 'c state')
     , (4, 'd', 'd city', 'd state')


select cast(id as varchar(10)), customer_name, cityname, statename from mytable

enter image description here

Which results in the following bcp command

bcp "select cast(id as varchar(10)), customer_name, cityname, statename from mytable" queryout c:\temp\myTable.csv /S.\instance_name /d tempdb /c /t"|" -T

Update This bcp command will add double quotes

exec Master..xp_Cmdshell 'bcp "select cast(id as varchar(10)), quotename(customer_name,char(34)), quotename(cityname,char(34)), quotename(statename,char(34)) from mytable" queryout "c:\temp\myTable.csv" /S.\instance_name /d tempdb /c /t"|" /T'

or just with bcp

bcp "select cast(id as varchar(10)), quotename(customer_name,char(34)), quotename(cityname,char(34)), quotename(statename,char(34)) from mytable" queryout "c:\temp\myTable.csv" /S.\instance_name /d tempdb /c /t"|" /T

This bcp command uses the quotename function. Which allows you to delimit your output using whatever characters you like.

enter image description here

  • I appreciate Craig. I just edit my question for dataset and requied result . I think Double quotes text qualifier not available in the SQL Server. – JP Chauhan Aug 18 '15 at 8:50
  • Does it work for double quotes text qualifier? – JP Chauhan Aug 18 '15 at 9:05
  • @JPChauhan. The generate scripts tool does not add delimiters because it just outputs insert statements. I added to my answer a bcp command that will add double quotes to the output. – Craig Efrein Aug 18 '15 at 9:16
  • So in the end, there is no option in BCP to enforce the quotes, you have to generate them in the query. One would think it shouldn't be that hard to add support for them, but... Oh well – Andriy M Aug 18 '15 at 9:44
  • 1
    @JPChauhan: It's the same login method as in your answer. If you want it to be different, you can read the manual and choose the appropriate combination of command-line parameters to match your requirements. – Andriy M Aug 18 '15 at 11:24
2

Another option, at least just to have it stated, is to use BCP Format Files. Format Files allow for specify column properties, including the delimiter. Delimiters are typically just whatever character, but you can add double-quotes into the appropriate field delimiters to produce text-qualified values.

You can do a simple test by having BCP generate a Format File, modify it, and then do an extract with it. Then you at least have a basic model of a file to update for any specific cases.

So try:

bcp sys.objects format nul -f TestFormat.xml -x -T -c -t"|"

If you edit TestFormat.xml you will see elements such as:

...
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="256" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="12"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="12"/>
<FIELD ID="4" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="12"/>
...

Add in &quot; before and/or after the appropriate pipe symbols to inject the text-qualifiers:

...
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="|&quot;" MAX_LENGTH="256" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="&quot;|" MAX_LENGTH="12"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="|&quot;" MAX_LENGTH="12"/>
<FIELD ID="4" xsi:type="CharTerm" TERMINATOR="&quot;|" MAX_LENGTH="12"/>
...

Then run the extract, specifying that Format File:

bcp sys.objects out Output.txt -T -f TestFormat.xml

It should produce something along the lines of:

sysrscols|"3"||"4"|0|S |SYSTEM_TABLE|2012-02-10 20:16:00.707|2012-02-10 20:16:00.713|1|0|0
sysrowsets|"5"||"4"|0|S |SYSTEM_TABLE|2009-04-13 12:59:11.093|2012-02-10 20:16:01.943|1|0|0
sysclones|"6"||"4"|0|S |SYSTEM_TABLE|2012-02-10 20:16:01.523|2012-02-10 20:16:01.530|1|0|0

I'm not sure how to get a text-qualifier on the far left (first character of the file) outside of selecting it as a string literal (i.e. '"') and then specifying no field delimiter between the first two fields.

For more info on XML Format Files:


Two notes (that apply whether you use Format Files or not):

  • Regarding date formats, there are a lot of options that can be specified in the Format Files, so look there first. If that doesn't work out, then you can always use the FORMAT function (if you are using SQL Server 2012 or newer) since the above example exports all fields as "character"

  • !! SUPREMO IMPORTANTE !!: The main reason for using text-qualifiers in delimited flat files is so that you can handle data that has embedded delimiter characters. So by using a text-qualifiers in this context, your string data can have pipe symbols and still be able to parse the fields correctly. Great. All done now, right? WRONG! Using text-qualifiers fixes embedded delimiters, but now you must account for embedded text-qualifiers. Meaning, you need to escape any double-quotes in your data, else it will break the import. For example:

    1|"Alfreds Futterkiste"|"Maria Anders"|"Obere Str. 57 Berlin"|12209|"Germany"
    2|"Johnny "knuckles" Malone"|"Other Name"|"123 Fake Street, IL"|60606|"US"
    

    Record #2 above will be considered a junk row. Or might get imported as a malformed entry, depending on how well the import process validates the data. So, you need to replace a single double-quote with two double-quotes (the standard means of escaping text-qualifiers) via REPLACE(FieldName, '"', '""') so that record #2 comes out as:

    2|"Johnny ""knuckles"" Malone"|"Other Name"|"123 Fake Street, IL"|60606|"US"
    

And for the sake of completeness, other export options include:

  • SSIS (SQL Server Integration Services) -- this should be free in most cases (I don't think it comes with Express Edition, though), but it is its own beast to tame.
  • SQLCLR: You can write your own C# / VB extract to fit your needs, or you can use the DB_BulkExport stored procedure in the SQL# library (which I am the author of) which combines most of the functionality of BCP and SSIS, and can handle dynamic text-qualification of string/date/etc fields and without requiring a Format File. Please note that while there is a Free version of SQL#, this particular stored procedure is only available in the Full version.
  • Using bcp with a format file: What happens if the new terminator, e.g. |&quot; appears in the value itself? – Niklas Peter Sep 23 '16 at 13:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.