На прошлой неделе в каком-то обсуждении всплыл старый хабротекст «Стратегия для технического интервью». Точнее, приведённая в нём задача №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.
Я этому так много времени уделяю не из природного занудства, а потому что если данные не будут загружены из-за уже имеющихся или уничтожены из-за инъекции, то поставленная задача не будет выполнена.
Как говорил известный эксперт: «Лучше день потерять, зато потом за пять минут долететь!»
Итак, подготовительный этап завершён — полетели. В смысле, приступаем к выполнению задания различными способами.
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 ... < ...sql4. 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:
- нанять суб-подрядчика
- поставить задачу подчинённому
- свалить эту хрень на другой отдел
Не надо, из-за желания доказать себе и окружающим, что ты ещё торт, делать эту фигню и отвлекаться от решения других задач. Есть случаи, когда человек не то что бы «может», а вот просто «обязан» переделегировать задачу.
Пора уже научиться расставлять приоритеты.
Автор: muxa_ru
