locked
Как удалить нежелательные кавычки во время импорта файла данных RRS feed

  • Общие обсуждения

  • Это сообщение описывает, как избавиться от нежелательных кавычек вокруг строки символов в файле данных.

    Допустим, что у вас есть текстовый файл, который имеет следующие данные и вы желаете импортировать данные в таблицу базы данных SQL Server .

    ABC”,” DEF”,” Adventure Works

    PQR”,” XYZ”,” Adventure Works

    ABCD ”,” MNO ”,” Adventure Works

    Структура таблицы следующая:

    create table tblPeople

    ( FName varchar ( 5), LName varchar ( 5), Country varchar ( 50))

    Вы можете использовать следующие три опции вставки данных в таблицу базы данных SQL Server :

    1)      Bulk Insert ( команда T-SQL)

    2)      BCP ( утилита командной строки )

    3)      Export/Import Wizard (GUI)

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

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

    9.0

    3

    1       SQLCHAR       0       5       "\t"     1     FName              SQL_Latin1_General_CP1_CI_AS

    2       SQLCHAR       0       5       "\t"     2     LName              SQL_Latin1_General_CP1_CI_AS

    3       SQLCHAR       0       50      "\r\n"   3     Company            SQL_Latin1_General_CP1_CI_AS

    Модифицированный формат файла представляет правильный разделитель колонок для каждой колонки. Новый формат файла будет выглядеть так:

    9.0

    4

    1        SQLCHAR       0        0      "\""       0      FIRST _ QUOTE      SQL _ Latin 1_ General _ CP 1_ CI _ AS

    2       SQLCHAR       0       5     "\",\""   1     FNAME               SQL_Latin1_General_CP1_CI_AS

    3       SQLCHAR       0       5     "\",\""   2     LNAME            SQL_Latin1_General_CP1_CI_AS

    4       SQLCHAR       0       50    "\"\r\n"  3     COMPANY          SQL_Latin1_General_CP1_CI_AS

    Логика поведения здесь следующая:

    1.      Первый макет колонки данных добавляется чтобы избавиться от первой двойной кавычки в начале каждого столбца.

    2.      Каждый символ колонки данных разделен символом “,”. Так каждый разделитель колонок получается как “\”,\””. Символ “\” переходящей последовательности говорит BCP о том, что символ следующий за обратной косой чертой является частью разделителя.

    3.      Последний разделитель колонки опять одна двойная кавычка. Так, разделителем колонок для последней колонки будет символ “\”\ r \ n ”, означающий одну двойную кавычку и конец ряда.

    4.      Число колонок изменяется от 3-х до 3+1 должное введение в модель колонок. (выделено красным).

     

    Выбор 1: команда Bulk Insert.

    Если вы используете следующие команды Bulk Insert для импортирования данных без использования формата файла, то вы достигните с кавычками префикс первого значиния колонки и суффикс кавычки для последнего значения колонки и префикс кавычки для первого значения колонки. Используемая команда была бы:

     

    BULK INSERT tblPeople

       FROM 'bcp.txt'

       WITH (

          DATAFILETYPE = 'char' ,

          FIELDTERMINATOR = '","' ,

          ROWTERMINATOR = '\ n ' );

     

    Вставленные данные выглядели бы так:

     

     

    Вам необходимо использовать функцию REPLACE , чтобы избавится от суффикса/префикса кавычки для последней и первой колонок соответственно.

    В случае если вы решили использовать выше преобразованный формат файла, то вы сможете правильно импортировать данные в таблицу, используя следующую команду:

     

    BULK INSERT tblPeople

       FROM 'bcp.txt'

       WITH (

          DATAFILETYPE = 'char' ,

          FIELDTERMINATOR = '","' ,

          ROWTERMINATOR = '\n' ,

          FORMATFILE = 'bcp.fmt' );

     

    Выбор 2: BCP .

    Если вы используете BCP утилиту для вставки данных в таблицу, то вы столкнетесь с теми же проблемами как и при вставке данных с использованием BULK INSERT без формата файла. Таким образом, вам снова понадобится использовать формат файла для вставки данных. Команда BCP будет как следствие для вставки данных в разных случаях если выполняется из SSMS окна запроса:

     

    EXEC xp_cmdshell 'bcp <database name>.<schema name>.tblPeople in "bcp.txt" -T -f "bcp.fmt"'

     

    В случае если вы не хотите использовать T - SQL запрос, то перейдите к папке < System Drive :>\ Program Files \ Microsoft SQL Server \90\ Tools \ binn и выполните команду, заключенную в одиночные кавычки, по импортирту данных в таблицу, используя BCP .

     

    Выбор 3: Export / Import Wizard

    Если вы планируете использовать Export / Import Wizard , то вы должны будете сделать небольшие изменения после того как выберете неструктурированный файл для импорта в GUI .

    Первое, что вам нужно сделать после выбора файла данных в GUI , это добавить две пустые колонки до и после номера уже представленных колонок.

     


    В примере, который мы используем, мы добавим пустую Колонку 0 и затем установим Разделитель Колонки в ( ), чтобы удалить первую двойную кавычку.

    Далее вы должны будете установить ("{,}" ) как Разделитель Колонки для Колонок 1 и 2. Для Колонки 3 вы должны будете установить Разделитель Колонки как ( ). После этого пустая колонка, Колонка 4 нуждается в добавлении разделителя строк. Для этой колонки вы должны установить Разделитель Колонки как ({ CR }{ LF } ).

    Как только это сделано, вам нужно кликнуть по опции Edit Mappings под изображением Select Source Tables and Views . Вам нужно будет задать Destination для Колонок 0 и 4 как < ignore >,   таким образом пакет SSIS не импортирует данные для Колонок 0 и 4 в таблицу.

     

     

    Примечание: Есть предположение, что у всех рядов в неструктурированном файле одинаковое число разделителей колонки.

     

    Дополнительная информация

    В случае, если данные имели нехарактерные для Целочисленных данных значения, то файл формата будет следующим:

     

    Пример данных:

    ABC ”, 1,” USA

    PQR ”, 2,” UK

     

    Тогда ваш формат файла будет следующим для различных разделителей:

     

    9.0

    4

    1       SQLCHAR       0       0     "\""      0     FIRST_QUOTE      SQL_Latin1_General_CP1_CI_AS

    2       SQLCHAR       0       5     "\","     1     FNAME            SQL_Latin1_General_CP1_CI_AS

    3       SQLCHAR       0       5     ",\""     2     LNAME            SQL_Latin1_General_CP1_CI_AS

    4       SQLCHAR       0       50    "\"\r\n"  3     COMPANY          SQL_Latin1_General_CP1_CI_AS

     

    Такая информация существовала для старших версий BCP , таким образом я думаю, что расширю выбор наряду с другими утилитами, доступными в настоящее время:

     

    132463  INF : Использование BCP когда данные в кавычках

     

    http :// support . microsoft . com / default . aspx ? scid = kb ; EN - US ;132463

     

    73182   INFO : Использование BCP когда данные в пределах кавычек

     

    http :// support . microsoft . com / default . aspx ? scid = kb ; EN - US ;73182

     

     

    Счастливого импорта!! J


    Для связи [Mail]
    • Перемещено Siddharth Chavan 2 октября 2010 г. 0:35 MSDN Forums Consolidation (От:SQL Server для разработчиков)
    4 мая 2010 г. 11:32