Excel, SQL и легендарный барометр — решаем простую задачу разными способами

На прошлой неделе в каком-то обсуждении всплыл старый хабротекст «Стратегия для технического интервью». Точнее, приведённая в нём задача №4

Дано: .xls (Excel) файл с одним листом в 4 числовых колонки и 1000 строк.
Требуется: Загрузить его в SQL базу данных, таблица с соответствующими колонками имеется. Ну и, сперва, оценить время на решение.

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



Update: В коментариях рассказывают методы заполнения столбцов без «протягивания»: раз, два

0. Прежде чем приступить к работе

В условиях задачи есть два очень важных пункта:

• нам дают готовый файл с данными
• таблица в базе данных уже создана

Оба эти пункта влияют на успешность решения поставленной задачи, состоящей в перемещении данных их XLS в SQL.

Начну со второго. Находящиеся в таблице данные могут не дать записать те данные что есть у Вас. Ну, например, если какой-то столбец это unique id, а в имеющейся таблице такой id уже есть. Тут всё просто. Узнаёте что делать с данными и либо первой операцией очищаете таблицу, либо вместо INSERT делаете REPLACE.

А теперь про полученный файл. Вы вот прямо так будете гнать его в базу? Уверены? А вы уверены что вам туда ничего лишнего не напихали? Все 1000 строк глазами проглядывать будете?

Я сделал просто — прямо в редакторе XLS-файла (в моём случае — LibreOffice Calc) применил регулярные выражения для удаления всего кроме числовых значений.



В результате остались только цифры, разделитель «запятая» и знак «минус».

Дальше я сделал замену «запятая» на «точка» и при сохранении в CSV получал данные вот такого вида:

11,4667.25,6874573,21336
12,466726,-6874574,21337

Теперь данные безопасны и SQL-friendly.

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

Как говорил известный эксперт: «Лучше день потерять, зато потом за пять минут долететь!»

Итак, подготовительный этап завершён — полетели. В смысле, приступаем к выполнению задания различными способами.

Update 2: в комментариях навели на идею. Данные могут быть безопасны, но состоять из бессмысленного набора цифр, "-" и ",". В этом случае импорт сработает неполностью. Как поступаем:
— сперва делать прогон на тестовой таблице
— сразу в рабочую, но с роллбэком
?

1. Загружаю CSV в phpMyAdmin

Если есть phpMyAdmin (или аналог для используемого SQL), то:

• обеззараживаем данные (см. п.0)
• первой строкой в файле прописываем имена полей в SQL
• сохраняем CSV
• загружаем

2. SQL в веб-форму

Если нет веб-морды принимающей CSV, но есть принимающая просто SQL запросы то, казалось бы, это указанный в исходном тексте вариант:

Добавлю колонку в excel файле, куда во всех ячейках вставлю (растяну) «insert into» и дополнительные колонки с запятыми, получу sql скрипт. Сразу плюс, даже в оценке не нуждаемся.

А вот и нет. Ну правда же, протягивать колонку с INSERT INTO ещё можно, но протягивать запятые. На 1000 строк. И так три раза. Нафиг-нафиг.

Тут вариантов два.

Во-первых, можно:

• обеззараживаем данные (см. п.0)
• сохранить данные в CSV, используя разделитель «запятая»
• открыть CSV в code-based текстовом редакторе (в моём случае — Notepad++ )
• заменить перевод строк на

);
INSERT INTO … (

• поправить первую и последнюю строчку файла
• пульнуть через форму

Во-вторых, можно не сохранять в CSV, а через буфер вставить содержимое таблицы в Notepad++ (предварительно выполнив п.0). Потом заменяем «табуляция» на «запятая», переносы строка на инсёрты, правим начало и конец файла. Постим через веб-форму.

3. Клиент SQL

Виндового клиента MySQL у меня нет уже давно (ни гуёвого, ни консольного). Да и доступ извне к нему врядли дадут. Поэтому заливаю файл полученный в п. 2 на сервер и делаю там в консоли.

mysql ... < ...sql

4. PHP-скрипт

Конечно же, идеальным вариантом будет написать скрипт на 10 строк, который будет делать fgetcsv(), формировать INSERT INTO и пулять всё это в базу.

Ну правда же, тот кто даёт Вам это тестовое задание в любой момент скажет «Ой, а мне надо что бы строчки у которых в третьем столбце стоят нечётные целые числа шли в другую таблицу» или «а в пятый столбец нужно было записывать кубический корень из произведения значений данных из всех 4 столбцов».

И у него не будет ответа на вопрос «чувак, а чё ты сам эти расчёты в Excel не сделал?». Всё что он сможет сказать «не я такой — жизнь такая».

Кстати, в этот скрипт можно вставить веб-форму с загрузкой CSV-файла, сделать обеззараживание данных и пусть автор задания сам всё грузит.

Хотя, конечно же, такой вариант не подходит. Этот скрипт с формочкой потом останется на сайте, про него забудут и будет какая-никакая, а дырка.

Поэтому, решаем задание так же как в п.2, только сохраняем всё в php-файл и вместо

);
INSERT INTO … (

делаем:

)",$connect);
mysql_query("INSERT INTO … (

Ну и mysql_connect в начале

5. У меня же теперь есть Linux!

После обретения Windows Subsystem for Linux жизнь прям заиграла новыми красками.

Поэтому:

• обеззараживаем данные (см. п.0)
• сохранить данные в CSV, используя разделитель «запятая»
• и….

cat test.csv | awk '{ gsub("\r", ""); print "INSERT INTO … ( ... ) VALUES (" $0 ");"; }' | ssh ... mysql ..

Готово.

* * *

А вот сейчас будет кусок из-за которых этот текст не только в хабе «MySQL», но и в хабе «Разработка веб-сайтов».

Кроме приведённых ранее очевидных вариантах решения поставленной задачи есть ещё 3:

• нанять суб-подрядчика
• поставить задачу подчинённому
• свалить эту хрень на другой отдел

Не надо, из-за желания доказать себе и окружающим, что ты ещё торт, делать эту фигню и отвлекаться от решения других задач. Есть случаи, когда человек не то что бы «может», а вот просто «обязан» переделегировать задачу.

Пора уже научиться расставлять приоритеты.