Как решить проблему смешанных данных в столбце при импортировании Excel в SQL?

Question

[Ivan Pavlov]

Всем привет! Есть задача написать программку импортирования данных из Excel в SQL. Подробный гайд есть на сайте MSDN с этим сложностей нет. Сложность возникает, если в столбце находятся смешанные данные. Excel проверяет первые 8 строк (по умолчанию) и по ним решает какой тип ставить. Для первого типа, который превалирует по количеству записей данные вставляются как надо, для другого же типа проставляются NULL во все ячейки. Здесь, думаю, ни для кого Америки не открыл. На сайте Microsoft по этому вопросу есть рекомендация использовать в Extended Properties IMEX=1. Но Excel в этом случае начинает всем данным проставлять строковый тип, после чего SQL не может конвертировать полученные строки в свой формат (таблица имеет разные типы столбцов, не только строки). Если всю SQL-таблицу сделать из строковых столбцов, то и в этом случае NULL-данные остаются в результирующей таблице, только вставляются как пустые строки.
На данные момент используется следующий алгоритм:
- сначала делается запрос на типы столбцов в таблице SQL;
- после чего столбцы DataTable настраиваются на эти типы;
- данные считываются OleDbDataReader'ом по строке;
- записываются в результирующий DataTable по одной ячейке;
- некоторые столбцы "чистятся" от различных символов для числовых значений (например, знаков "%");
- получившаяся DataTable отправляется на запись в SQL.
Такой алгоритм работает хорошо за исключением одного НО, которое отбивает всякое желание использовать этот алгоритм: скорость загрузки данных увеличилась почти в 4 раза (если быть точным, то на 700к строк 7 минут старого алгоритма против 40 минут описанного выше).
Объем данных составляет от 300к строк и до нескольких миллионов.
Используется Oledb драйвер - Microsoft.ACE.OLEDB.12.0. Формат Excel - xlsx.
Собственно вопрос: можно ли такое поведение каким-нибудь образом победить? Оставить скорость оригинального алгоритма и решить проблему смешанных данных в столбце при импортировании из Excel в SQL.

Comments

[V Sh.]

А что мешает сначала записать все данные во временную таблицу Temp (все столбцы строковые), а уже потом, средствами самой БД, вытянуть нужные данные в нужные столбцы нужного формата?

[AtomKrieg]

В своё время я для самой правой колонки писал формулу

=concatenate("insert dbtab values('", a1, "', '", b1,

Множил формулу и получившийся текстовый файл грузил. Может и вам ну нужна что-то попроще?

[Ivan Pavlov]

V Sh.: Неплохой вариант, правда и в нем два раза обрабатывать одни и те же данные придется.

Answer 1

[ThunderCat]

Выгнать в csv и импортировать?

Comments

[Ivan Pavlov]

Слишком затратно выходит.
Проблема решилась сама собой. OleDb-дайвер стал адекватно воспринимать таблицу, если в Excel поставить строковый тип столбцу со смешанными данными.