Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Валидация метаданных excel файла с помощью T-SQL  [new]
otets1988
Member

Откуда:
Сообщений: 21
Добрый вечер,

Есть целевая задача, которая требует загрузить данные из 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. То есть сравниваю порядки с их названиями и если где-то не совпало, то я считаю, что структура не корректна.

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

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

Спасибо!
7 июл 18, 22:34    [21552728]     Ответить | Цитировать Сообщить модератору
 Re: Валидация метаданных excel файла с помощью T-SQL  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7762
otets1988,

это же вопрос не к сиквелу, а к умению программировать на VBA или C#. На них валидаторы и пишите.
7 июл 18, 23:06    [21552768]     Ответить | Цитировать Сообщить модератору
 Re: Валидация метаданных excel файла с помощью T-SQL  [new]
otets1988
Member

Откуда:
Сообщений: 21
Владислав Колосов,

С точки зрения архитектурного подхода в целом скорее всего вы правы, но есть одно большое НО.
У меня очень мало времени на эту задачу, но что еще хуже - знаний C# и VBA у меня еще меньше...
Поэтому на сегодня только один вариант - T-SQL. Пусть он будет не такой железобетонный, как на C# или VBA, но будет на T-SQL.
Хотелось бы более элегантное решение нежели мое.
7 июл 18, 23:22    [21552794]     Ответить | Цитировать Сообщить модератору
 Re: Валидация метаданных excel файла с помощью T-SQL  [new]
vikkiv
Member

Откуда: London
Сообщений: 2704
otets1988,

linked_server на базе провайдера от Microsoft.ACE.OLEDB.12.0 до Microsoft.ACE.OLEDB.16.0 (или может быть уже даже 17),
на это дело вешаешь
sp_tables_ex
и
sp_columns_ex
7 июл 18, 23:52    [21552870]     Ответить | Цитировать Сообщить модератору
 Re: Валидация метаданных excel файла с помощью T-SQL  [new]
otets1988
Member

Откуда:
Сообщений: 21
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 в обоих случаях после реальной загрузки данных)?
8 июл 18, 08:23    [21553299]     Ответить | Цитировать Сообщить модератору
 Re: Валидация метаданных excel файла с помощью T-SQL  [new]
aleks222
Member

Откуда:
Сообщений: 962
1. Проще надо быть.
2. Тупо открываем файл Linked Server. И запрашиваем те столбцы, которые нужны.
3. Либо они есть и фсе срабатывает.
4. Либо чего-то не хватает и отгребаем ошибку. Ошибку в лог.

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


1 - будет отработано в процессе открытия.
2 - дык, суть 1.
3 - будет отработано в процессе загрузки.
4. А вот реляционная теория дозволяет менять порядок. Вам то каким местом это вперлось?
8 июл 18, 10:02    [21553351]     Ответить | Цитировать Сообщить модератору
 Re: Валидация метаданных excel файла с помощью T-SQL  [new]
otets1988
Member

Откуда:
Сообщений: 21
aleks222,

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

Я стараюсь быть проще, но не всегда это получается либо меня поставят в позу с такими дурацкими требованиями и тут ничего непопишешь...
8 июл 18, 10:45    [21553415]     Ответить | Цитировать Сообщить модератору
 Re: Валидация метаданных excel файла с помощью T-SQL  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7762
otets1988,

Вы сейчас создадите эрзац решение и будете его развивать, т.к. бизнес постоянно меняет требования. А завтра поймете, что зашли в тупик и надо было время потратить все же на создание приложения загрузки. Это всё пройдено давным-давно.
8 июл 18, 11:56    [21553526]     Ответить | Цитировать Сообщить модератору
 Re: Валидация метаданных excel файла с помощью T-SQL  [new]
invm
Member

Откуда: Москва
Сообщений: 9349
otets1988,

Можете сделать примерно так:
if object_id('tempdb..#t') is not null
 drop table #t;

select top (0) * into #t from openrowset(...);

Далее анализируйте метаданные получившейся временной таблицы.
8 июл 18, 12:16    [21553575]     Ответить | Цитировать Сообщить модератору
 Re: Валидация метаданных excel файла с помощью T-SQL  [new]
aleks222
Member

Откуда:
Сообщений: 962
Владислав Колосов
otets1988,

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


Вы все еще создаете универсальный ковырятель в носу свое приложение для загрузки?

Давно уже пора понять "т.к. бизнес постоянно меняет требования" - ничего универсального создать не получится.
8 июл 18, 12:26    [21553599]     Ответить | Цитировать Сообщить модератору
 Re: Валидация метаданных excel файла с помощью T-SQL  [new]
aleks222
Member

Откуда:
Сообщений: 962
otets1988
aleks222,

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

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


2. Дык, транслируйте ошибку пользователю в "человеческий язык". Если так за него переживаете.
3. Тут есть два пути... 1) Грузить в текст и преобразовывать в MS SQL; 2) При возникновении ошибки, методом select top(n)... и делением отрезка пополам (т.е. за log(числа строк) запросов) локализовать первую строку с ошибкой.
8 июл 18, 12:31    [21553609]     Ответить | Цитировать Сообщить модератору
 Re: Валидация метаданных excel файла с помощью T-SQL  [new]
otets1988
Member

Откуда:
Сообщений: 21
aleks222
otets1988
aleks222,

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

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


2. Дык, транслируйте ошибку пользователю в "человеческий язык". Если так за него переживаете.
3. Тут есть два пути... 1) Грузить в текст и преобразовывать в MS SQL; 2) При возникновении ошибки, методом select top(n)... и делением отрезка пополам (т.е. за log(числа строк) запросов) локализовать первую строку с ошибкой.
9 июл 18, 08:37    [21554950]     Ответить | Цитировать Сообщить модератору
 Re: Валидация метаданных excel файла с помощью T-SQL  [new]
otets1988
Member

Откуда:
Сообщений: 21
Владислав Колосов
otets1988,

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

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


invm
otets1988,

Можете сделать примерно так:
if object_id('tempdb..#t') is not null
 drop table #t;

select top (0) * into #t from openrowset(...);

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

Интересное решение. Я даже и близко о таком не думал. Спасибо!


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

Не будет ли этот путь стоит дороже по сравнению с анализом данных из sp_columns_ex/sys.columns?
9 июл 18, 08:40    [21554953]     Ответить | Цитировать Сообщить модератору
 Re: Валидация метаданных excel файла с помощью T-SQL  [new]
aleks222
Member

Откуда:
Сообщений: 962
otets1988

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


Не будет ли этот путь стоит дороже по сравнению с анализом данных из sp_columns_ex/sys.columns?


Интересуюсь, Сонечка, чаво вы там собираетесь "анализировать"?
Или наивно полагаете, что сервер чудесным образом корректность данных в столбцах экселя проверит?
9 июл 18, 10:03    [21555160]     Ответить | Цитировать Сообщить модератору
 Re: Валидация метаданных excel файла с помощью T-SQL  [new]
otets1988
Member

Откуда:
Сообщений: 21
aleks222,

Да нет, что вы... Я там не тип собрался проверять, а лишь названия полей. Тип я буду по данным проверять. К примеру, если столбец должен быть числовые, то примитивной к данным в этом столбце применю isnumeric. Если длина текстового поля должна быть фиксированной, то через функцию Len посчитаю не вышел ли я за пределы и так далее в таком же духе изврат.
9 июл 18, 10:10    [21555191]     Ответить | Цитировать Сообщить модератору
 Re: Валидация метаданных excel файла с помощью T-SQL  [new]
Santa89
Member

Откуда:
Сообщений: 1477
А не проще ли это логику сделать таксказать на клиенте, тоесть в Excel файле с помощью VBA и с помощью него же писать лог в Sql-таблицу?
9 июл 18, 10:19    [21555232]     Ответить | Цитировать Сообщить модератору
 Re: Валидация метаданных excel файла с помощью T-SQL  [new]
otets1988
Member

Откуда:
Сообщений: 21
Santa89,

Клиент генерирует excel файл в своей программе и далее выгржает на фтп. В эту часть никто не будет лезть.
9 июл 18, 10:24    [21555248]     Ответить | Цитировать Сообщить модератору
 Re: Валидация метаданных excel файла с помощью T-SQL  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
Santa89
А не проще ли это логику сделать так сказать на клиенте, то есть в Excel файле с помощью VBA и с помощью него же писать лог в Sql-таблицу?

И в нем же подключить ADO и сразу писать в целевую таблицу, и отметочку ставить "(не)загружено".

Но автор хочет через TSQL.
(с) Шурик, вы комсомолец? Это же не наш метод. Где гуманизм?
9 июл 18, 10:34    [21555273]     Ответить | Цитировать Сообщить модератору
 Re: Валидация метаданных excel файла с помощью T-SQL  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
otets1988
Santa89,
Клиент генерирует excel файл в своей программе и далее выгржает на фтп. В эту часть никто не будет лезть.

Будете ТЗ из себя выдавливать потихоньку?
SSIS пробовали использовать?
Там все есть из коробки - и заберет с FTP, и метаданные валидирует, и в случае ошибки в лог запишет, и даже email с sms отправить сможет - даже думать не нужно.
9 июл 18, 10:37    [21555284]     Ответить | Цитировать Сообщить модератору
 Re: Валидация метаданных excel файла с помощью T-SQL  [new]
otets1988
Member

Откуда:
Сообщений: 21
Руслан Дамирович
otets1988
Santa89,
Клиент генерирует excel файл в своей программе и далее выгржает на фтп. В эту часть никто не будет лезть.

Будете ТЗ из себя выдавливать потихоньку?
SSIS пробовали использовать?
Там все есть из коробки - и заберет с FTP, и метаданные валидирует, и в случае ошибки в лог запишет, и даже email с sms отправить сможет - даже думать не нужно.

Про SSIS я сразу написал.
otets1988
SSIS отпадает сразу, поэтому не стоит предлагать его.
Если каких-то данных не хватает, то напишите.

Задача - валидация исключительно силами T-SQL.
Уже видно, что можно что-nj попробовать. Да, оно не идеально и не совсем красиво, но хоть как-то.
Я пытаюсь сейчас собрать различные варианты, которые можно применить с помощью T-SQL
9 июл 18, 10:57    [21555334]     Ответить | Цитировать Сообщить модератору
 Re: Валидация метаданных excel файла с помощью T-SQL  [new]
Santa89
Member

Откуда:
Сообщений: 1477
otets1988
Santa89,

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


Хорошая программа. Не дает гарантий что в поле даты не будет записан Int и парочку полей может не выгрузить...
Вобщем вы пишите костыль, ну или программу для программы....
9 июл 18, 11:31    [21555474]     Ответить | Цитировать Сообщить модератору
 Re: Валидация метаданных excel файла с помощью T-SQL  [new]
aleks222
Member

Откуда:
Сообщений: 962
Руслан Дамирович
SSIS пробовали использовать?
Там все есть из коробки - и заберет с FTP, и метаданные валидирует, и в случае ошибки в лог запишет, и даже email с sms отправить сможет - даже думать не нужно.


Там плохая коробка. С подвохами.
И слишком большая.
9 июл 18, 14:01    [21556165]     Ответить | Цитировать Сообщить модератору
 Re: Валидация метаданных excel файла с помощью T-SQL  [new]
Massa52
Member

Откуда:
Сообщений: 379
otets1988,
Может не стоит из пушки по воробьям мочить, а попробовать напрячься и разобраться в инструменте,
который предлагает чел. в данной статье:
https://www.codeproject.com/Articles/1128472/CSV-File-Parser
9 июл 18, 14:11    [21556236]     Ответить | Цитировать Сообщить модератору
 Re: Валидация метаданных excel файла с помощью T-SQL  [new]
Alexander Us
Member

Откуда:
Сообщений: 1130
otets1988,

если для Excel файлов *.xlsx надо что то сложнее чем просто залить/отлить

то используйте компоненту EPPLus
по ссылке есть и солюшн с кучей примеров
вот ещё пошаговые примеры

напишите .NET программку и вызывайте EPPLus из неё
9 июл 18, 14:45    [21556478]     Ответить | Цитировать Сообщить модератору
 Re: Валидация метаданных excel файла с помощью T-SQL  [new]
Гулин Федор
Member

Откуда: МИНСК
Сообщений: 1239
otets1988
Владислав Колосов,

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


я тоже знаю вообщем только SQL прилично
в ETL часто используется подход

1 грузится все в ландинговую таблицу - в данном случае строки (varchar() )
2 из нее уже в 2-ю пытаясь преобразовать к нужными типам int , date и т.д
возожмные ошибки
TRY_CAST, TRY_CONVERT
надеясь что версия 2012

зы хотя если одбс драйвер будет екесл. поля воспринимать не как строки - не прокатит
- тогда возможно нужен маленький vba для экспорта в промежуточный csv

ps Прочитла внимательней - если структура файлов нарушена
нафиг - максимум записать что это файл кривой и все - разбираться что там не так автоматически это пусть в никда
9 июл 18, 15:14    [21556584]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить