Embedded text qualifier does not conform to RFC 4180 during export to CSV by Siva_

Active

Type:	Bug
ID:	778980
Opened:	2/11/2013 10:14:11 AM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

RFC 4180 defines Common Format and MIME Type for Comma-Separated Values (CSV) Files. One of the requirements of the RFC 4180 is stated below. SQL Server 2012 Import and Export wizard does not conform to the below stated requirement when exporting data to CSV file. Attached PDF file contains detailed steps to simulate and compare the output between SQL Server 2000 and SQL Server 2012.

RFC 4180 states the following:

If double-quotes are used to enclose fields, then a double-quote appearing inside a field must be escaped by preceding it with another double quote. For example:
"aaa","b""bb","ccc

Details (expand)

English

SQL Server 2012 SP1

Integration Services (DTS)

Windows Server 2008 R2 Standard (SP1)

US English

Refer the attached PDF file with detailed instructions to reproduce this issue.

X64

File Name	Submitted By	Submitted On	File Size
SQL_Server_Bug_778980.pdf	Siva_	2/13/2013	692 KB

Posted by Siva_ on 2/11/2013 at 10:26 AM

To overcome the issue in SQL Server 2012 Import and Export Wizard for the above given example, I can ignore selecting the table and use the below query where it would replace all double quotes (") in the column data with two double quotes ("") in order for the generated CSV file to conform with RFC 4180. This provides the same output as the file created by SQL Server 2000 DTS Export/Import Wizard.

SELECT ItemId, REPLACE(ItemDesc, '"', '""') AS ItemDesc FROM dbo.ItemInformation

Microsoft