BULK INSERT / OPENROWSET FormatFile Terminator для CSV-файла с , (запятая) в данных

Я написал хороший импорт для моего CSV с миллионом строк, который работает довольно хорошо (используя OPENROWSET BULK (я не использовал BULK INSERT, потому что мне нужно перекрестное соединение с некоторыми другими столбцами). Форматный файл использует запятую в качестве терминатора.

Вот пример CSV, который я использовал для разработки:

Reference, Name, Street
1,Dave Smith, 1 Test Street
2,Sally SMith,1 Test Street

Как только я заработал, кто-то напомнил мне, что в самих данных может быть запятая, упс!!!!

Reference, Name, Street
"1","Dave Smith", "1 Test Street"
"2","Sally Smith","1,Test Street" <-comma in street

Как люди справляются с CSV с запятой в данных, используя FormatFiles? (Или я просто говорю, что файл должен быть разделен табуляцией)?


sql sql-server openrowset bulkinsert c#-4.0
person DomBat    schedule 16.07.2012    source источник
comment
Большинство утилит импорта, о которых я знаю, игнорируют запятые (или другие разделители) внутри строк — они считают их частью строки. Кроме того, что произойдет, если в строке все равно окажется символ табуляции (между кавычками)? Вы закончите с той же проблемой. Посмотрите, можете ли вы указать разделитель строк, и, вероятно, все будет в порядке.   -  person Clockwork-Muse    schedule 16.07.2012

Ответы (2)


arrow_upward
2
arrow_downward

Если ваш терминатор поля может встречаться в данных, то лучше всего использовать разделитель TAB или PIPE (или любой другой, который подходит для ваших данных).

Если в данных встречается символ конца, он интерпретируется как признак конца, а не как данные, а данные после этого символа интерпретируются как принадлежащие следующему полю или записи. Поэтому тщательно выбирайте терминаторы, чтобы убедиться, что они никогда не появятся в ваших данных.

http://msdn.microsoft.com/en-us/library/ms191485.aspx

person Tim    schedule 16.07.2012

arrow_upward
0
arrow_downward

Разделители в полях данных — распространенная проблема с файлами с разделителями. Некоторые общие тактики для решения этой проблемы включают в себя:

  1. Воссоздайте файл данных, убрав все вхождения разделителя из полей данных до того, как они будут записаны в файл: это устраняет ошибки OPENROWSET, но не сохраняет целостность данных.
  2. Воссоздайте файл данных с другим символом-разделителем: по моему опыту, лучше использовать разделитель табуляции. Символ табуляции встречается реже, чем запятая в данных. Но это, конечно, не неслыханно. Я тоже видел вкладки внутри данных.
  3. Заключите поля данных в двойные кавычки: это требует некоторых изменений в файле формата XML.

Редактирование файла данных вручную может быть выполнимо для любого из вышеперечисленных вариантов. Но это может быть утомительно, особенно для больших файлов. (Просто открыть файл размером в несколько ГБ в Notepad.exe — это упражнение в терпении.) На самом деле вы бы хотели, чтобы автор воссоздал его для вас. Вариант №1 всегда должен «работать». Но опять же, есть проблема с целостностью данных, с которой вы, возможно, не сможете жить. Вариант № 2, вероятно, сработает во многих случаях, но он не является пуленепробиваемым. Вариант № 3 тоже не является пуленепробиваемым (всегда возможно иметь разделитель в поле данных), но он настолько близок, насколько это возможно. Кроме того, он сохраняет целостность данных.

Вот одна из возможностей для вашего файла формата XML:

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR='","' MAX_LENGTH="5"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR='","' MAX_LENGTH="128" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR='"\r\n' MAX_LENGTH="128" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="Reference" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="2" NAME="Name" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="3" NAME="Street" xsi:type="SQLVARYCHAR"/>
 </ROW>
</BCPFORMAT>

Обратите внимание на ТЕРМИНАТОР ПОЛЯ: я использовал одинарные кавычки, чтобы обозначить "," как признак конца, а "\r\n как признак конца строки (разделитель COLUMN 3). Я сделал обоснованное предположение, что Name и Street могут содержать до 128 символов — отредактируйте его по мере необходимости.

Проблемы:

  1. Запросы OPENROWSET() будут возвращать Reference с ведущим символом двойной кавычки ". И из-за этого...
  2. Reference не может быть возвращено как INT (или SMALLINT, BIGINT и т. д.). Он возвращается как VARCHAR (xsi:type="SQLVARYCHAR")

Для конкретного предоставленного образца данных я бы удалил двойные кавычки из Reference полей данных, отрегулировал файл формата XML так, чтобы FIELD ID="1" имел TERMINATOR=',"', и дополнительно откорректировал файл формата XML, чтобы COLUMN SOURCE="1" имел xsi:type="SQLINT".

Для дополнительной информации может помочь этот пост в блоге: Начало работы с OPENROWSET и поставщиком наборов строк BULK — часть 2

person Dave Mason    schedule 30.01.2016