데이터를 CSV 플랫 파일로 내보내는 동안 포함 된 텍스트 한정자 문제를 해결하는 방법은 무엇입니까?

데이터를 CSV 플랫 파일로 내보내는 동안 포함 된 텍스트 한정자 문제를 해결하는 방법은 무엇입니까?

---

RFC 4180 :

RFC 4180 은Common Format and MIME Type for Comma-Separated Values (CSV) Files. 의 요구 사항 중 하나RFC 4180는 다음과 같습니다. 이것은#7RFC 링크의 요점입니다.

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"

SQL Server 2000 :

DTS Export/Import WizardSQL Server 2000RFC 4180 자체가 2005 년 10 월 에만 게시 된 것처럼 보이지만 in 은 위에서 언급 한 표준을 따르는 것으로 보입니다 . 아래에 명시된 SQL Server 2000 버전을 사용하고 있습니다 .

Microsoft SQL Server  2000 - 8.00.2039 (Intel X86) 
May  3 2005 23:18:38 
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

SQL Server 2012 :

SQL Server Import and Export Wizardin SQL Server 2012은 RFC 4180에 정의 된 표준에 따라 테이블의 데이터를 CSV 파일로 내 보내지 않습니다. 아래 명시된 SQL Server 2012 버전을 사용하고 있습니다 .

Microsoft SQL Server 2012 - 11.0.2316.0 (X64) 
Apr  6 2012 03:20:55 
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

이슈 시뮬레이션 :

다음은 SQL Server 2000 과 SQL Server 2012 에서 실행 한 샘플입니다 . 아래 쿼리를 실행하여 테이블을 만들고 몇 개의 레코드를 삽입했습니다. ItemDesc열은 그 안에 큰 따옴표와 데이터가 있습니다. 내 의도는 내장 된 내보내기 데이터 마법사를 사용하여이 두 SQL Server 버전에서 데이터를 내보내고 생성 된 CSV 파일을 비교하는 것입니다.

CREATE TABLE dbo.ItemInformation(
    ItemId nvarchar(20) NOT NULL,
    ItemDesc nvarchar(100) NOT NULL
) 
GO

INSERT INTO dbo.ItemInformation (ItemId, ItemDesc) VALUES ('100338754', 'Crown Bolt 3/8"-16 x 1" Stainless-Steel Hex Bolt');
INSERT INTO dbo.ItemInformation (ItemId, ItemDesc) VALUES ('202255836', 'Simpson Strong-Tie 5/8" SSTB Anchot Bolt');
INSERT INTO dbo.ItemInformation (ItemId, ItemDesc) VALUES ('100171631', 'Grip-Rite #11 x 1-1/2" Electro-Galvanized Steel Roofing Nails');
INSERT INTO dbo.ItemInformation (ItemId, ItemDesc) VALUES ('202210289', 'Crown Bolt 1/2" x 3" "Zinc-Plated" Universal Clevis Pin');
INSERT INTO dbo.ItemInformation (ItemId, ItemDesc) VALUES ('100136988', 'Tapcon 3/16" x 1-3/4" Climaseal Steel "Flat-Head" Phillips Concrete Anchors (75-Pack)');
INSERT INTO dbo.ItemInformation (ItemId, ItemDesc) VALUES ('203722101', 'KwikTap 3/16" x 2-1/4" "Flat-Head" Concrete Screws (100-Pack)');
GO

온 DTS Export/Import Wizard에 SQL Server 2000, 내가 CSV 파일로 데이터를 내보낼 설정 아래를 사용했다. 이름으로 파일을 저장했습니다 SQLServer2000_ItemInformation.csv.

온 SQL Server Import and Export Wizard에 SQL Server 2012, 내가 CSV 파일로 데이터를 내보낼 설정 아래를 사용했다. 이름으로 파일을 저장했습니다 SQLServer2012_ItemInformation.csv.

Here is the comparison between the two files using Beyond Compare. The left side contains the file generated by SQL Server 2000 and the right side contains the file generated by SQL Server 2012. You can notice that the left side file from SQL Server 2000 contains additional double-quotes to compensate the embedded quotes in the data column. This conforms to the standard specified in RFC 4180 but it is clearly missing from the file generated by SQL Server 2012

Searches on the web:

I searched for this bug on the web and found the following links. Following are the bug reports on Microsoft Connect. All these issues seem to be related to importing a file but nothing about exporting data. All these bugs have been closed as Fixed.

• SSIS flat file parser does not read Column delimiters embedded in text data
• Flat File Connection Manager not handling Text Delimiters in CSV Files
• Embedded quotes in Flat File Import fails
• BUG: Flat File Connection Manager: multiple-character text qualifier does not load all data

Below post on MSDN blog states that changes have been made in SQL Server 2012 with respect to Flat file source supports embedded qualifiers and a variable number of columns per row

• SSIS - What’s New in SQL Server Denali

Another post on MSDN blog states the same under the section Embedded Qualifiers.

• Flat File Source Changes in Denali

Workaround that I know of:

I know a workaround to fix the issue by writing a query that would replace all double-quotes (") in my column data with two double-quotes ("") so that the exported file will end up with correct embedded qualifier data. This would avoid pulling the data directly from the table as it is.

My questions:

• I don't know if this issue has been truly fixed in SQL Server 2012. Has this issue been fixed only for importing files that have embedded text qualifiers and not for exporting data to CSV?

• Probably, I am clearly doing something wrong and missing the obvious. Could someone please explain to me what I am doing wrong here?

Microsoft Connect:

I have submitted a bug report on Microsoft Connect website to get their feedback. Here is the link to the bug report. If you agree that this is a bug, please visit the below link to vote up on Microsoft Connect website.

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

---

I wouldn't offer this answer except that you worked so hard to document it and it's been upvoted with no answer after a month. So, here goes. Your only choices appear to be to change the data or change the tool.

Probably, I am clearly doing something wrong and missing the obvious. Could someone please explain to me what I am doing wrong here?

When the tool is broken and the vendor doesn't care, it's mistake to keep trying. It's time to switch. You put a lot of effort into researching exactly how it's broken and demonstrating it violates not only the RFC but the tool's own prior version. How much more evidence do you need?

CSV is a boat anchor too. If you have the option, you're better off using an ordinary delimited file format. For lots of applications, tab-delimited is good. The best delimiter IMO is '\' because that character has no place in English text. (On the other hand it won't work for data containing Windows pathnames.)

CSV has two problems as an exchange format. First, it's not all that standard; different applications recognize different versions, whatever the RFC may say. Second (and related) is that it doesn't constitute a regular language in CS terms, which is why it can't be parsed as a regular expression. Compare with ^([^\t]*\t)*[\t]*$ for a tab-delimited line. The practical implication of the complexity of CSV's definition is (see above) the relative dearth of tools to handle them and their tendency to be incompatible, particularly during the wee hours.

If you give CSV and DTS the boot, you have good options, one of which is bcp.exe. It's very fast, and safe because Microsoft hasn't been tempted to update it for years. I don't know much about DTS, but in case you have to use it for automation, IIRC there is a way to invoke external utilities. Beware though, that bcp.exe does not return error status to the shell dependably.

If you're determined to use DTS and to stick with CSV, then really your best remaining option is to write a view that prepares the data appropriately for it. I would, if backed into that corner, create a schema called, say, "DTS2012CSV", so that I could write select * from DTS2012CSV.tablename, giving anyone who cares a fighting chance to understand it (because you'll document it, won't you, in comments in the view text?). If need be, others can copy its technique for other broken extracts.

HTH.

---

I know this is two years old, but I am also now having this issue, as we need to use SQL Server 2008 for a contract we have (don't ask). After reading through this question, I realized I needed to do the replace suggestion, but when I went to do it in the query, I ran into truncation issues, because using the replace() function in the query itself would convert the text to a varchar(8000) by default.

However, I discovered I could do the same thing using a Derived Column step in between the DB Source and Flat File objects. For example, I have a column named "short_description," that could have quotes in it, so I just used the following function as the expression, and selected "Replace short_description" in the Derived Column:

REPLACE(short_description,"\"","\"\"")

This seems to have solved the issue for me.

---

종종 성과 이름이 같은 필드에 있고 형식이 지정됩니다 (성, 이름). 데이터베이스에서 바로 작업-> 데이터 내보내기 (더 많은 옵션이있는 SSIS를 통하지 않음)를 사용하고 쉼표로 구분 된 파일로 CSV로 내 보내야하는 경우 텍스트 한정이어야합니다.

이것은 큰 따옴표가 필요한 널이 아닌 선택된 필드에 도움이 될 것입니다.

CASE WHEN NOT PersonName IS NULL AND LEN(PersonName) > 0 THEN QUOTENAME(PersonName, '"') ELSE NULL END as 'PersonName'

결과:

PersonName

"콜린스, 재커리 E"

참고 URL : https://stackoverflow.com/questions/14815574/how-to-fix-the-embedded-text-qualifier-issue-while-exporting-data-to-csv-flat-fi