Импорт dbf в SQL Server

  • [アーティクル]

Несмотря на то, что dbf давно считается legacy форматом, сабж до сего времени остается насущной задачей судя по количеству вопросов в Интернете. В частности, я с ней столкнулся при попытке затянуть в таблицу карту. Карта ArcGIS содержала метаданные в формате dbf. Имело смысл прочитать их заодно в SQL Server, чтобы не делать вручную подписи к полигонам, линиям и иным картографическим объектам. В давние времена Visual FoxPro 6 и SQL Server 7.0 это не составляло проблемы, но с тех пор многое изменилось. С выходом SQL Server 2005 в MSDN появилась информация, что мастер импорта и экспорта в SQL Server не поддерживает импорт и экспорт dBASE-файлов и других DBF-файлов. В качестве решения рекомендовано использовать SQL Server Integration Services или промежуточный импорт в Access или Excel. Такая же ситуация формально сохраняется по сей день, включая SQL Server 2012. Это не всегда удобно, потому что, помимо SQL Server, требует дополнительной установки MS Office, а средства разработки ETL-пакетов не входят в состав бесплатной редакции SQL Server Express. В этой заметке я постараюсь импортировать dbf в SQL Server, не пользуясь ничем, кроме SQL Server.

 

Имеется файл regions2010_wgs.dbf, взятый отсюда. Открываем SQL Server Management Studio, в Object Explorer выбираем базу, в таблицу которой будем импортировать dbf, и из контекстного меню говорим Import Data:

 

image001

Рис.1

 

В качестве источника данных указываем .Net Framework Data Provider for ODBC, коль скоро ODBC теперь снова наше все, в качестве ConnectionString - следующую строку соединения:

Driver={Microsoft dBase Driver (*.dbf)};SourceType=DBF;SourceDB=NA;Exclusive=No; NULL=No;Deleted=No;BackgroundFetch=No

 

image002

Рис.2

 

Нажимаем Next. Если теперь нажать Back, мы увидим, что свойства соединения развернулись из строки в столбец так, чтобы можно было лицезреть их список и видеть, чему каждое из них равно:

 

image003

Рис.3

 

Примеры строки соединения для ODBC-драйвера dBase приводятся, например, в Microsoft Knowledge Base или на ресурсе connectionstrings.com. В целом, о назначении тех или свойств легко догадаться из их названий, кроме, пожалуй, свойства Deleted, которое имеет прямо противоположный смысл. Как известно, операция удаления строки в dBase/FoxPro не приводит к ее немедленному физическому удалению из файла. Строка лишь помечается, что она удалена. Физическая очистка строк, у которых проставлен признак удаления, и реорганизация файла выполняются командой PACK. Значение NO говорит драйверу включить удаленные строки в возвращаемый набор результатов. Чтобы, наоборот, их не показывать, надо поставить YES. Жмем Next.

На следующем экране все просто. Задается соединение с SQL Server, включая ту базу, в которой будет создана таблица с результатами импорта из dbf:

 

 image004

Рис.4

 

Идем дальше. Предлагается выбрать dbf-ную таблицу из списка таблиц или написать руками запрос. Имеет смысл, например, для FoxProшной базы, которая, как и всякая нормальная база, представляет собой контейнер, в котором содержится несколько таблиц, в данном случае в виде отдельных dbf-файлов. Для индивидуального dbf-файла это не работает - см., например, OdbcConnection.GetSchema("tables") all wrong for .dbf file, и сотрудники поддержки Microsoft рекомендовали в этой ситуации использовать OLE DB Provider for Visual FoxPro. Во-первых, случай имел место задолго до коренного перелома генеральной линии партии. OLE DB тогда было наше все, a ODBC, наоборот, относилось к старым унаследованным интерфейсам. Во-вторых, я не понимаю, зачем броузить список dbf, когда он и так один.

В случае разрозненных dbf, лежащих в одной директории, надо задать в строке ODBC-соединения (Рис.3) свойство DefaultDir, например,

Driver={Microsoft dBase Driver (*.dbf)};sourcetype=DBF;DefaultDir=c:\Temp;exclusive=No;null=No;deleted=No;backgroundfetch=No

Тогда можно отметить Copy Data from one or more tables or views.

 

image005

Рис.5

 

и будет выведен список dbf в этой директории, из которого будет предложено выбрать:

 

image006

Рис.6

 

Но я не задавал DefaultDir на Рис.3, поэтому выбираю написать запрос:

 

image007

Рис.7

 

и пишу:

 

image008

Рис.8

 

а в ответ получаю ошибку The Microsoft Jet database engine could not find the object 'regions2010_wgs.dbf':

 

image009

Рис.9

 

Эта ошибка происходит из-за того, что глупый драйвер до сих пор воспринимает имена файлов в формате MS-DOS 8.3. Если переименовать файл regions2010_wgs.dbf в, скажем, aaa.dbf, а запрос Рис.8, соответственно, заменить на select * from c:\Temp\aaa.dbf, ошибка пропадает. Будет предложено выбрать существующую или задать название таблицы, которая будет создана на SQL Server в базе Database1 (см.Рис.4) под результаты импорта из dbf. Oставляю предлагаемое название как есть:

 

image010

Рис.10

 

Нажав здесь же кнопку Preview, можно предварительно ознакомиться с содержимым dbf, которoе предполагается перенести на SQL Server:

 

 image011

Рис.11

 

Все хорошо, только удручает абракадабра вместо русского текста. Причину ее появления в популярной форме объясняет уважаемый автор Lalex здесь. Русские символы слетают из-за того, что глупый драйвер ожидает dbfный файл в DOSовской кодировке (CP866, она же OEM). Он, похоже, считает формат dbf очень древним, чисто досовским наследием. ArcView же по умолчанию считает DBF виндосовским форматом (ANSI 1251). Так и стоят эти две программы, как два бычка, упершись лбами.

Итак, причина ясна, осталось ее поправить. Пляски с бубном прописать в строку соединения collate=Machine или Russian / CodePage=ANSI / Collating Sequence=1251  к успеху не привели. Изменил 29-й байт в aaa.dbf на 0хС9 - ноль эмоций. Действительно, признак кодовой страницы в заголовке dbf драйвером игнорируется. Однако настройку драйвера можно изменить в реестре. Она хранится в DataCodePage по пути HKLM\SOFTWARE\Microsoft\Jet\4.0\Engines\xBase или HKLM\SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Xbase или, соответственно, HKLM\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\xBase или HKLM\SOFTWARE\Wow6432Node\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Xbase в зависимости от того, был ли установлен на машину Office и если да, то как. По умолчанию, свойство действительно имеет значение OEM, что заставляет драйвер читать все dbfы из расчета этой кодировки. Если изменить его на ANSI

 

image012

Рис.12

 

кириллица в ANSIшном dbf'e, естественно, будет читаться по-человечески:

 

image013

Рис.13

 

Перезапускаться при этом, к счастью, не требуется, однако визард импорта следует закрыть и повторить по-новой с Рис.1.

Жмем ОК, Next, заканчиваем визард, в результате чего неявно создается и выполняется SSIS-пакет:

 

image014

Рис.14

 

и получаем фигню. Гы!

 

image015

Рис.15

 

Это, на самом деле, тоже понятно, почему. В таблице Query под результаты импорта визард создал поле region типа varchar(200) без явного указания коллации. Следовательно, для него по умолчанию используется коллация базы. Так получилось, что база Database1 имела нерусскую коллацию:

 

image016

Рис.16

 

Чтобы исправить ситуацию, надо сделать поле region юникодовским или откорректировать ему коллацию. Кстати, давайте ему еще длину увеличим. Так, на всякий случай.

 

image017

Рис.17

 

Сохраняем изменения структуры, очищаем данные truncate table Query и повторяем импорт Рис.1-14

 

image018

Рис.18

 

Теперь все импортируется нормально. Единственно, я сказал "очищаем данные", но у себя это сделать забыл, и на картинке они задвоились. Переделывать уже не буду, потому что непринципиально. Смысл понятен.

 

Алексей Шуленин

Comments

  • Anonymous
    January 01, 2003
    Спасибо за статью!

  • Anonymous
    January 01, 2003
    Я предвидел этот вопрос, поэтому специально сделал select @@version на Рис.18. Внимательно смотрим на нижнюю строчку в панели результатов.

  • Anonymous
    May 17, 2012
    А под SQL 64 бит пробовали? работает?

  • Anonymous
    May 17, 2012
    Под x64 скорее всего работать не будет. Для себя пролему решил написанием бинарного парсера dbf - работате раза в 2-3 быстрей чем линкед.

  • Anonymous
    August 19, 2012
    Корректировать реестр нельзя  - я уже одажды по глупости это сделал - так другие программы использующие Jet - а это все бесплатные дельфийские поменялили кодировку  dbf - я залил на сайт ДКЦПФУ козибяки

  • Anonymous
    August 27, 2013
    Зачетная статья! Спасибо! очень помогла!

  • Anonymous
    November 22, 2013
    Спасибо! Просто и понятно, как все гениальное!

  • Anonymous
    February 18, 2014
    Отличная статья! Очень помогла.

  • Anonymous
    April 14, 2014
    Спасибо, очень помогли!
    Кстати, на x64 сработало нормально вроде бы..

    Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)

  • Anonymous
    April 29, 2014
    Спасибо, помогло при импорте КЛАДР'а в MSSQL 2012.

  • Anonymous
    May 30, 2014
    Все бы вот так писали. Спасибо огромное"

  • Anonymous
    June 19, 2014
    Спасибо большое, тоже помогло при импорте кладра, кракозябр в русских буквах не было даже после выполнения рисунка 6

  • Anonymous
    July 29, 2014
    Не помогло изменение реестра, по-прежнему выводится абракадабра, может кто помочь с тем как это исправить?)

  • Anonymous
    August 06, 2014
    Автору спасибо. У кого после правок реестра всё равно отображаются крокозябры, проверьте текущую локаль винды в Control Panel -> Region and Language -> Administrative -> Change system locale . Если там, к примеру, стоял English, а импортируемые данные заведомо на русском, то поставьте Russian, перезагрузите систему и попробуйте импортировать заново. Возможно, флажок в реестре придётся вернуть обратно в OEM.

  • Anonymous
    September 17, 2014
    Спасибо, помогло очень!!!!

  • Anonymous
    September 30, 2014
    Спасибо. Помогло и под MSSQL 2014 64 бит.

  • Anonymous
    February 03, 2015
    я этой тулзой пользовался http://dbutils.ru/, мастером при больших объемах не получается.

  • Anonymous
    May 22, 2015
    Если кто то решит менять байт кодовой страницы DBF файла - ставьте 0 и все получится.:)

  • Anonymous
    July 06, 2015
    Спасибо, друг. Помогло.

  • Anonymous
    August 14, 2015
    а если не исползовать инерфейсь ползователья написать код как будит выглядит этот код

  • Anonymous
    August 26, 2015
    To Slowdima: СПАСИБО ! ПОМОГЛО Server 2012 R2