Эта статья показывает, как программа nheplace, используя функцию замены по регулярному выражению параметрами -sre (-searchregularexpression) и -ft (-formattedtext), может преобразовать текстовый файл формата CSV в файл запросов SQL, чтобы потом внести эти строки в базу данных.
Например, из CSV файла с книгами:
Код;Наименование;Автор;Издательство;Цена;Год;Ст;Стр;Описание;Переплет;ISBN;Тематика;N;Сп;ДатаЗаписи;Гриф;НДС;ОгрВозр 7958980;Мама на кушетке. Что нужно знать, обращайтесь к психологу. Валиуллина Л.;Валиуллина Л.;Омега-Л;225,50р.;17;22;252;;О;978-5-370-04042-9;Психология;N;;29.12.2016;;10;12+ 7958970;Жизнь, которая улыбается. Как заниматься любимым делом и зарабатывать много. Маланьин И., Антипенко А.;Маланьин И., Антипенко А.;Омега-Л;225,50р.;17;24;236;;О;978-5-370-04045-0;Психология;N;;29.12.2016;;10;0 + 7953170;Как стать экспертом. Белановский А. и др.;Белановский А. и др.;Омега-Л;363,80р.;17;16;348;;О;978-5-370-03981-2;Бизнес. Предпринимательство;N;;21.12.2016;;10;12+ …
сделать SQL файл с запросами:
insert into books (BookId,BookTitle,Author,Publisher,Price,Year,PageCount,Description,ISBN,Subject,RecordDate) values (7958980,'Мама на кушетке. Что нужно знать, обращайтесь к психологу. Валиуллина Л.','Валиуллина Л.','Омега-Л',225.50,2017,252,'','978-5-370-04042-9','Психология','2016-12-29'); insert into books (BookId,BookTitle,Author,Publisher,Price,Year,PageCount,Description,ISBN,Subject,RecordDate) values (7958970,'Жизнь, которая улыбается. Как заниматься любимым делом и зарабатывать много. Маланьин И., Антипенко А.','Маланьин И., Антипенко А.','Омега-Л',225.50,2017,236,'','978-5-370-04045-0','Психология','2016-12-29'); insert into books (BookId,BookTitle,Author,Publisher,Price,Year,PageCount,Description,ISBN,Subject,RecordDate) values (7953170,'Как стать экспертом. Белановский А. и др.','Белановский А. и др.','Омега-Л',363.80,2017,348,'','978-5-370-03981-2','Бизнес. Предпринимательство','2016-12-21'); …
Как это сделать выполнением всего одной команды:
- сформировать для параметра -set регулярное выражение c capture groups, соответствующими столбцам данных,
- сформировать для параметра -ft необходимый запрос INSERT/UPDATE/DELETE из найденных capture group,
- запустить программу nhreplace с параметрами -set и -ft.
Формирование регулярного выражения
Регулярное выражение должно с помощью capture group разделять каждую строку на поля, чтобы полученные значения можно было вставлять в SQL запрос:
- точка с запятой как символ-разделитель не будет входить в группы.
- для целочисленных полей (Код, Стр и т.д.) использую группу
(\d+)
– одна или несколько цифр. - для текстовых полей (Наименование, Автор и т.д.) использую группу
([^;]*)
– любое число любых символов, кроме точки с запятой. - к полю Цена особый подход, потому что в файле цена указана в формате, непригодном для внесения в базу данных в виде числа – с пробелом между тысячами и с запятой перед дробной частью: фактически три группы
(\d*)\s*(\d+),(\d{2})р\.
, которые потом будут собраны в правильном формате уже в выражении для -ft. - поле Дата также нуждается в переформатировании и поэтому использует три группы
(\d{2})\.(\d{2})\.(\d{4})
.
Регулярное выражение:
^(\d+);([^;]+);([^;]*);([^;]*);(\d*)\s*(\d+),(\d{2})р\.;(\d+);(\d*);(\d*);([^;]*);([\w]*);([^;]*);([^;]*);([^;]*);([^;]*);(\d{2})\.(\d{2})\.(\d{4});.*
Формирование SQL запроса
SQL запрос формируется для каждой строки и начинается с
insert into Books(BookId,BookTitle,Author,Publisher,Price,Year,PageCount,Description,ISBN,Subject,RecordDate) values …
Далее подставляются значения, полученные в результате поиска по регулярному выражению
- Численные значения подставляются группой, например,
$1
для поля Код (столбец BookId)./li> - Текстовые значения подставляются группой в апострофах, например,
'$2'
для поля Наименование (столбец BookTitle). - Цена для SQL запроса формируется из трёх групп
$5$6.$7
, без пробела и с точкой перед дробной частью. - Дата формируется из трёх групп и в апострофах:
'$19-$18-$17'
.
SQL запрос:
insert into Books(BookId,BookTitle,Author,Publisher,Price,Year,PageCount,Description,ISBN,Subject,RecordDate) values ($1,'$2','$3','$4',$5$6.$7,20$8,$10,'$11','$13','$14','$19-$18-$17');
Запуск nhreplace, проверка выполнения
Команда запуска nhreplace:
nhrplc -sre:"^(\d+);([^;]+);([^;]*);([^;]*);(\d*)\s*(\d+),(\d{2})р\.;(\d+);(\d*);(\d*);([^;]*);([\w]*);([^;]*);([^;]*);([^;]*);([^;]*);(\d{2})\.(\d{2})\.(\d{4});.*" -ft:"insert into Books(BookId,BookTitle,Author,Publisher,Price,Year,PageCount,Description,ISBN,Subject,RecordDate) values ($1,'$2','$3','$4',$5$6.$7,20$8,$10,'$11','$13','$14','$19-$18-$17');" -cp:ansi,unicode -o:d omega-l.sql
Вывод программы:
omega-l.sql Unicode : 706
Файлы для загрузки
Скачать bat, csv и sql файлы (Яндекс.Диск)
Что почитать по теме
Узнать больше о программе nhreplace: описание nhreplace, справка nhreplace, примеры использования nhreplace.
Узнать больше о регулярных выражениях: TR1 Regular Expressions,
Конструкции группирования (Grouping Constructs).