Добрый вечер,

Есть целевая задача, которая требует загрузить данные из Excel файла в существующую таблицу на SQL-сервере.

Перед реальной загрузкой (по сути это будет merge) необходимо будет выполнить валидацию в 2 этапа:
1. Валидация метаданных (надеюсь правильно выражаю суть этим словом) excel-файла с помощью T-SQL;
2. Валидация данных excel-файла с помощью T-SQL перед выполнением Merge с существующей таблицей.

Оба случая требуют выполнения логирования, т.е. записать, что было не так с файлом и данными в нем.

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


Итак, первый этап - валидация метаданных.
Мои вводные:
1. Структура Excel-файла предопредела заранее, но, как это часто бывает, что-то может пойти не так и могут потеряться столбцы, их названия, их порядок в файле и т.д. Вот это я должен проверить, что загружаемый файл соответствует требованиям.
Предположим файл состоит из 7 столбцов со следующими названиями и типами:
- ID (int)
- Name (nvarchar(256))
- Address (nvarachar(512))
- Phone (nvarchar(24))
- Col1 (bigint)
- Col2 (numeric(15, 4))
- Col3 (bit)

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

Как только Excel-файл будет соответствовать на все 100% требованиям я смогу перейти ко второму этапу.

Данные всегда будут находится на первой странице файла.

Грузить данные я могу исключительно 2 способами:
1. С помощью CLR-функции
2. С помощью Linked Server через openquery

SSIS отпадает сразу, поэтому не стоит предлагать его.


Пытался по разному спрашивать гугл, но ничего не помогло...
Буду благодарен за любые подсказки.

Из того, что мне удалось сейчас придумать - это непосредственная загрузка имеющихся данных из Excel-файла во временную таблицу (через CLR или Linked Server), где далее я по имени этой временной таблицы обращаюсь в базе tempdb к обьекту sys.columns и получаю список названий столбцов, которые были в файле с их порядком. После этого делаю с помощью конструктура табличных выражений свою эталонную таблицу с порядком столбцов и их названиями и к этому результату применяю EXCEPT отсортированного результата из sys.columns. То есть сравниваю порядки с их названиями и если где-то не совпало, то я считаю, что структура не корректна.

Мне кажется, что должно существовать более красивое решение этой задачи.
Может кто знает его или подскажет где поискать?

Если каких-то данных не хватает, то напишите.

Спасибо!

Владислав Колосов,

С точки зрения архитектурного подхода в целом скорее всего вы правы, но есть одно большое НО.
У меня очень мало времени на эту задачу, но что еще хуже - знаний C# и VBA у меня еще меньше...
Поэтому на сегодня только один вариант - T-SQL. Пусть он будет не такой железобетонный, как на C# или VBA, но будет на T-SQL.
Хотелось бы более элегантное решение нежели мое.

vikkiv,

Спасибо. Что-то я и забыл про этих 2-х товарищей sp_tables_ex и sp_columns_ex.


Еще я только что обратил внимание, что допустил некоторую неточность в исходных данных.
Грузить я могу действительно только 2-мя способами - через CLR или через Linked Server, но при этом оба способа должны быть реализованы и будут выбираться в зависимости от опции. В связи с этим валидация метаданных должны быть реализована и для первого случая и для второго.

Выходит, что на сейчас для CLR - это обращение к sys.columns после физической загрузки данных из Excel во временную таблицу.
А для Linked Server - это комбинация из sp_tables_ex и sp_columns_ex, где я могу до начала загрузки осуществить проверку по столбцам.

Исходя из этих уточнений мне придется написать процедурку, которая аналогично в зависимости от опции будет работать тем или другим способом. Насколько я выиграю от того, что реализую 2 разных обработки вместо одной (я так понимаю, что это возможно только что sys.columns в обоих случаях после реальной загрузки данных)?

aleks222,

Спасибо за ваш комментарий.
Постараюсь объяснить суть моих тараканов.
1. С порядком вы правы и тут мне не чем крыть кроме своей безграмотности. Я банально забыл, что могу из openquery вернуть не просто select *, а задать конкретные имена столбцов в нужном мне порядке. В таком случае порядок столбцов в файле мне действительно не важен. Вот лень к чему приводит, что такие вещи забываю.
2. А вот просто ошибка от SQL при именованном обращении мне не совсем подходит, так как она не мне важна в логе, а обычному пользователю, который будет грузить Файлы в базу. Надо ему по-русски написать, что твой файл корявый и в нем нахватает таких-то столбцов или их названия не корректны. То есть сперва вот такой лог, а потом уже исключения от sql можно для меня.
3. С типами данных в столбцах ровно такая же проблема. Мне сперва надо пользователю сообщить, что у него, к примеру, в такой-то строке в таком-то столбце каша, которая не соответствует исходным требованиям. Мне на этом этапе уже важно отсечь такие данные и только потом их пытаться мержить.

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

aleks222otets1988aleks222,

Спасибо за ваш комментарий.
Постараюсь объяснить суть моих тараканов.
1. С порядком вы правы и тут мне не чем крыть кроме своей безграмотности. Я банально забыл, что могу из openquery вернуть не просто select *, а задать конкретные имена столбцов в нужном мне порядке. В таком случае порядок столбцов в файле мне действительно не важен. Вот лень к чему приводит, что такие вещи забываю.
2. А вот просто ошибка от SQL при именованном обращении мне не совсем подходит, так как она не мне важна в логе, а обычному пользователю, который будет грузить Файлы в базу. Надо ему по-русски написать, что твой файл корявый и в нем нахватает таких-то столбцов или их названия не корректны. То есть сперва вот такой лог, а потом уже исключения от sql можно для меня.
3. С типами данных в столбцах ровно такая же проблема. Мне сперва надо пользователю сообщить, что у него, к примеру, в такой-то строке в таком-то столбце каша, которая не соответствует исходным требованиям. Мне на этом этапе уже важно отсечь такие данные и только потом их пытаться мержить.

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

2. Дык, транслируйте ошибку пользователю в "человеческий язык". Если так за него переживаете.
3. Тут есть два пути... 1) Грузить в текст и преобразовывать в MS SQL; 2) При возникновении ошибки, методом select top(n)... и делением отрезка пополам (т.е. за log(числа строк) запросов) локализовать первую строку с ошибкой.

Владислав Колосовotets1988,

Вы сейчас создадите эрзац решение и будете его развивать, т.к. бизнес постоянно меняет требования. А завтра поймете, что зашли в тупик и надо было время потратить все же на создание приложения загрузки. Это всё пройдено давным-давно.
Я понимаю и разделяю вашу позицию, но бизнес разный бывает.
Поставленная задача - это очень маленький винтик, который не стоит ничего. Это не фундамент, ни база для чего-либо дальнейшего, это не самостоятельное решение, это всего лишь часть большой машины за которую готовы заплатить копейки.
Я лишь со своей стороны пытаюсь в этом болоте хоть что-то сделать по-нормальному, если это возможно. Вот и обратился за советами сюда.


invmotets1988,

Можете сделать примерно так:

Далее анализируйте метаданные получившейся временной таблицы.
Интересное решение. Я даже и близко о таком не думал. Спасибо!


aleks2223. Тут есть два пути... 1) Грузить в текст и преобразовывать в MS SQL; 2) При возникновении ошибки, методом select top(n)... и делением отрезка пополам (т.е. за log(числа строк) запросов) локализовать первую строку с ошибкой.
Не будет ли этот путь стоит дороже по сравнению с анализом данных из sp_columns_ex/sys.columns?

aleks222,

Да нет, что вы... Я там не тип собрался проверять, а лишь названия полей. Тип я буду по данным проверять. К примеру, если столбец должен быть числовые, то примитивной к данным в этом столбце применю isnumeric. Если длина текстового поля должна быть фиксированной, то через функцию Len посчитаю не вышел ли я за пределы и так далее в таком же духе изврат.

Santa89,

Клиент генерирует excel файл в своей программе и далее выгржает на фтп. В эту часть никто не будет лезть.

Руслан Дамировичotets1988Santa89,
Клиент генерирует excel файл в своей программе и далее выгржает на фтп. В эту часть никто не будет лезть.
Будете ТЗ из себя выдавливать потихоньку?
SSIS пробовали использовать?
Там все есть из коробки - и заберет с FTP, и метаданные валидирует, и в случае ошибки в лог запишет, и даже email с sms отправить сможет - даже думать не нужно.
Про SSIS я сразу написал.
otets1988SSIS отпадает сразу, поэтому не стоит предлагать его.
Если каких-то данных не хватает, то напишите.

Задача - валидация исключительно силами T-SQL.
Уже видно, что можно что-nj попробовать. Да, оно не идеально и не совсем красиво, но хоть как-то.
Я пытаюсь сейчас собрать различные варианты, которые можно применить с помощью T-SQL