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:

  • найняти суб-підрядника
  • поставити завдання підлеглому
  • звалити цю хрень на інший відділ
Не треба, через бажання довести собі і оточуючим, що ти ще торт, робити цю фігню і відволікатися від вирішення інших завдань. Є випадки, коли людина не те що б «може», а от просто «зобов'язаний» переделегувати завдання.

Пора вже навчитися розставляти пріоритети.

Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.