11

I have this BCP command:

'bcp DBName..vieter out c:\test003.txt -c -T /t"\",\"" -S SERVER'

The output CSV I get does not put quotes around the field names, instead it puts it around the commas! How can I get the /t"\",\"" to put quotes around all fields.

Thanks all

Abs
  • 56,052
  • 101
  • 275
  • 409

7 Answers7

12

Setting the row terminator in addition to the field terminator should do the trick

'bcp DBName..vieter out c:\test003.txt -c -T -t"\",\"" -r"\"\n\"" -S SERVER'

This will likely work, but miss off the leading " for the first field of the first line, and perhaps the last field of the last line - I'm not sure, just guessing really, no server here!

or try using QUOTENAME to wrap text fields (you could also wrap numbers, but that isn't normally required.)

'bcp "SELECT id, age, QUOTENAME(name,'"') FROM DBName..vieter" queryout c:\test003.txt -c -T -t"," -S SERVER'
Mark Hions
  • 19
  • 6
Paul Creasey
  • 28,321
  • 10
  • 54
  • 90
  • Is there ever a way to have the first and last have quotes - I really need a well formatted CSV file. – Abs Jan 13 '10 at 23:41
  • 1
    What you said happened - but the last fiedl had two quotes at the end and the first had no quotes. Do I just need to fiddle with the above to get what I want? Is there some docs I can look at as I haven't come across this regex stuff - if it is regex! – Abs Jan 13 '10 at 23:43
  • you could use `QUERYOUT` and then create a query using `QUOTENAME(column,'"')` for text fields. – Paul Creasey Jan 13 '10 at 23:53
  • Can I use QUOTENAME to quote all columns without passing the name of the column as I need to do this for different tables? I tried this but I got quite a few errors - is the quoting correct? I am using the above in `EXEC master..xp_cmdshell @bcpCommand` – Abs Jan 14 '10 at 00:04
  • You could potentially set @bcpCommand programmatically by querying syscolums and building the query, but it's getting very messy, not something i would want to do. This seems like a major problem with BCP! – Paul Creasey Jan 14 '10 at 08:15
  • Thanks Paul - I am not sure if I can go any further with this. Your answer is so close, but I can't believe I can't add a quote to the first field and remove one from the very last field of the last line! Damn it! Is it even possible? – Abs Jan 14 '10 at 16:17
  • Post-processing the generated file using your favorite script language should be able to do that... – Oliver Feb 16 '18 at 10:03
  • 4
    Beware: [QUOTENAME](https://learn.microsoft.com/en-us/sql/t-sql/functions/quotename-transact-sql) only supports input strings of length up to **128** characters, so it won't work for longer field values. – Oliver Feb 16 '18 at 10:51
  • The solution with the quotes does answer the original question. But it does not escape the " characters with the field values. The QUOTENAME does, but as mentioned by Oliver, has a 128 bytes restriction. So both are not rock solid solutions – Ben Ootjers Jan 09 '19 at 08:58
8

You need to use CHAR(34) for the quote. This page has more details: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=153000

user1473461
  • 369
  • 2
  • 2
3

Alternatively, if you are fine for Powershell based script, you can try with below code, which does automatic quoting.

Invoke-sqlcmd -ConnectionString "Server=SERVERNAME, `
3180;Database=DATABASENAME;Trusted_Connection=True;"  `
-Query "SET NOCOUNT ON;SELECT * FROM TABLENAME" -MaxCharLength 700 | `
Export-Csv -NoTypeInformation -path C:\temp\FileName.csv -Encoding UTF8
Venkataraman R
  • 12,181
  • 2
  • 31
  • 58
2
bcp "SELECT char(34) + * +char(34) FROM atable queryout "C:\temp\out.csv" -T -N -c /t"\",\""

This will put quotes before and after each field (including the first and the last).

Venkataraman R
  • 12,181
  • 2
  • 31
  • 58
Seb
  • 21
  • 1
1

Here are the list of commands i used .

BCP "DECLARE @colnames VARCHAR(max);SELECT @colnames = COALESCE(@colnames + ',', '') + column_name from databaseName.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='tableName'; select @colnames;" queryout "C:\HeadersOnly.csv" -r"\n\""  -c -T -Uusername -Ppassword -SserverName

bcp databaseName.schema.tableName out "C:\EmployeeDatawithoutheaders.csv" -T -t"\",\"" -r"\"\n\"" -c -Uusername -Ppassword -SserverName

copy /b C:\HeadersOnly.csv+C:\EmployeeDatawithoutheaders.csv C:\EmployeeData.csv

del C:\HeadersOnly.csv

del C:\EmployeeDatawithoutheaders.csv
TT.
  • 15,774
  • 6
  • 47
  • 88
Modem Rakesh goud
  • 1,578
  • 1
  • 12
  • 11
  • Really like this solution, a few enhancements questions: 1. Any option to avoid the last row to be " 2. Any option to be smart and use the extra " only if the cells containing a column (,) or row (\n) delimiter inside? – Velizar VESSELINOV Dec 05 '18 at 19:33
1

I guess your goal was to clearly seperate field values by using an unique identifier so that import procedure doesn't have an issue.

I had same issue and found this workaroud useful: Using an unusual field terminator, for example | or even a string /#/ can be very unique and shouldn't mess with your string content. You also can HEX-Values (limited, see https://learn.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-2017)

export

bcp DB.dbo.Table out /tmp/output2.csv -c -t "/#/" -U sa -P secret -S localhost

import

bcp TargetTable in /tmp/output2.csv -t "/#/" -k -U sa -P secret -S localhost -d DBNAME -c -b 50000
robie2011
  • 3,678
  • 4
  • 21
  • 20
  • I like this solution, but there is another situation in which this causes troubles. That is, if Carriage return/line feed are being used in the field values. The fields need to be enclosed to overcome that I think. – Ben Ootjers Jan 09 '19 at 09:25
0

The actual workable answer, that removes the leading quote, is to :

A) generate format file with bcp :

bcp db.schema.tabel format nul -c -x -f file.xml -t"\",\"" -r"\"\r\n" -T -k

B) edit that file to manually copy field 1 to field 0 above, as the first field, set Max_Length=1 and remove the separator and one quot the was in field1

<FIELD ID="0" xsi:type="CharTerm" TERMINATOR="\&quot;" MAX_LENGTH="1" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>

The trick works, as you are adding a field (interface to the file) to detect the first seprator, which results in an always null-value, but not add a row (interface for the query output).

user7511843
  • 1