Как исправить проблему включения встроенного текста при экспорте данных в плоский файл CSV?

RFC 4180:

RFC 4180 определяет Common Format and MIME Type for Comma-Separated Values (CSV) Files. Одно из требований RFC 4180 указано ниже. Это точка #7 в ссылке RFC.

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 Wizard в SQL Server 2000, похоже, соответствует вышеупомянутым стандартам, хотя сам RFC 4180, похоже, был опубликован только в октябре 2005 года. Я использую приведенный ниже 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 Wizard в SQL Server 2012 не экспортирует данные из таблицы в CSV файл в соответствии со стандартом, определенным в RFC 4180. Я использую приведенную ниже версию 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.

Вот сравнение между двумя файлами с помощью Beyond Compare. Левая сторона содержит файл, сгенерированный SQL Server 2000, а правая сторона содержит файл, сгенерированный SQL Server 2012. Вы можете заметить, что левый файл из SQL Server 2000 содержит дополнительные двойные кавычки для компенсации встроенных кавычек в столбце данных. Это соответствует стандарту, указанному в RFC 4180, но явно отсутствует в файле, созданном SQL Server 2012

Поиск в Интернете:

Я искал эту ошибку в Интернете и нашел следующие ссылки. Ниже приведены отчеты об ошибках в Microsoft Connect. Все эти проблемы, похоже, связаны с импортом файла, но ничего не касаются экспорта данных. Все эти ошибки были закрыты как Fixed.

Ниже в блоге MSDN указано, что в SQL Server 2012 были внесены изменения в отношении Flat file source supports embedded qualifiers and a variable number of columns per row

SSIS - что нового в SQL Server Denali

Другое сообщение в блоге MSDN указано в разделе Embedded Qualifiers.

Изменения файла с плоским файлом в Denali

Обходной путь, о котором я знаю:

Я знаю обходное решение, чтобы исправить проблему, написав запрос, который заменит все двойные кавычки (") в моих данных столбца двумя двойными кавычками ( ""), чтобы экспортированный файл получал правильные встроенные данные классификатора. Это позволит избежать вытаскивания данных непосредственно из таблицы, как есть.

Мои вопросы:

Я не знаю, действительно ли эта проблема была исправлена в SQL Server 2012. Исправлена ли эта проблема только для файлов importing с встроенными разделителями текста и not для exporting данных в CSV?
Наверное, я явно делаю что-то неправильно и пропускаю очевидное. Может ли кто-нибудь объяснить мне, что я делаю неправильно здесь?

Microsoft Connect:

Я представил отчет об ошибке на веб-сайте Microsoft Connect, чтобы получить отзывы. Вот ссылка на отчет об ошибке. Если вы согласны с тем, что это ошибка, перейдите по ссылке ниже, чтобы проголосовать на Microsoft Connect сайте.

Встроенный текстовый определитель при экспорте в CSV не соответствует RFC 4180

Ответ 1

Я бы не предлагал этот ответ, за исключением того, что вы так усердно документировали его, и через месяц он был без ответа. Итак, вот так. Кажется, что ваш единственный выбор - изменить данные или изменить инструмент.

Наверное, я явно делаю что-то неправильно и пропускаю очевидное. Может ли кто-нибудь объяснить мне, что я делаю неправильно здесь?

Когда инструмент сломан, и продавцу все равно, не следует пытаться. Это время переключиться. Вы прикладываете много усилий для исследования того, как он сломался и демонстрирует его, нарушает не только RFC, но и собственную версию инструмента. Сколько еще доказательств вам нужно?

CSV - это якорь лодки. Если у вас есть этот параметр, вам лучше использовать обычный формат файла с разделителями. Для многих приложений разделитель табуляции хорош. Лучшим разделителем IMO является "\", потому что этому персонажу не место в английском тексте. (С другой стороны, он не будет работать для данных, содержащих имена путей Windows.)

CSV имеет две проблемы в качестве формата обмена. Во-первых, это не все, что стандарт; различные приложения распознают разные версии, независимо от того, что может сказать RFC. Второй (и связанный) состоит в том, что он не является регулярным языком в терминах CS, поэтому он не может быть проанализирован как регулярное выражение. Сравните с ^([^\t]*\t)*[\t]*$ для строки с разделителями табуляции. Практическое значение сложности определения CSV (см. Выше) является относительным недостатком инструментов для их обработки и их склонностью к несовместимости, особенно в течение непродолжительных часов.

Если вы даете CSV и DTS загрузку, у вас есть хорошие варианты, один из которых bcp.exe. Это очень быстро и безопасно, потому что Microsoft не испытывала желания обновлять ее в течение многих лет. Я не знаю много о DTS, но в случае, если вы должны использовать его для автоматизации, IIRC есть способ вызвать внешние утилиты. Помните, что bcp.exe не возвращает статус ошибки в оболочку.

Если вы настроены использовать DTS и придерживаться CSV, тогда ваша самая лучшая оставшаяся опция - написать представление, которое соответствующим образом готовит данные. Я бы, если бы в этом углу, создал схему, называемую "DTS2012CSV", чтобы я мог написать select * from DTS2012CSV.tablename, давая любому, кто интересуется боевым шансом, понять это (потому что вы задокументируете это, не будете вы, в комментариях в тексте вида?). В случае необходимости другие могут копировать свою технику для других сломанных выдержек.

НТН.

Ответ 2

Я знаю, что это два года, но у меня также есть эта проблема, так как нам нужно использовать SQL Server 2008 для контракта, который у нас есть (не спрашивайте). Прочитав этот вопрос, я понял, что мне нужно сделать предложение замены, но когда я пошел делать это в запросе, я столкнулся с проблемами усечения, потому что использование функции replace() в самом запросе преобразует текст в varchar (8000) по умолчанию.

Однако я обнаружил, что могу сделать то же самое, используя шаг Derived Column между объектами DB Source и Flat File. Например, у меня есть столбец с именем "short_description", который может содержать в себе кавычки, поэтому я просто использовал следующую функцию в качестве выражения и выбрал "Заменить short_description" в производном столбце:

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

Это, похоже, решило проблему для меня.