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:

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

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

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

    Only registered users can participate in poll. Log in, please.

    Ваш выбор

    • 19.1%1. Загружаю CSV в phpMyAdmin17
    • 12.4%2. SQL в веб-форму11
    • 25.8%3. Клиент SQL23
    • 18.0%4. PHP-скрипт16
    • 18.0%5. У меня же теперь есть Linux!16
    • 20.2%Переделегировать18
    • 12.4%Иное11
    AdBlock has stolen the banner, but banners are not teeth — they will be back

    More
    Ads

    Comments 19

      +3

      Почему бы не составить полный INSERT INTO ... конкатенацией строк и значений в формуле Экселя?
      Только не забыть экранировать одинарные кавычки в строках.
      А потом протянуть его до низу.

        0
        Вариант с конкатенацией хороший.

        Но протягивать на 1000 строк это неудобно. Надоедает. Даже если снизу тянуть и то надоедает. Сперва до низу скроллить, потом доверху тянуть.

        Я час больше чем на пару сотен строк стараюсь не протягивать и решать другими средствами.
          +2
          1. Вводим формулу в первую строку
          2. Выбираем диапазон с клавиатуры (вводим D1:D1000 в поле ввода слева от поля ввода для формул)
          3. Выбираем в меню Fill
            0
            Отлично, спасибо.

            Только в п.2 нужно не просто ввести, но и энтер нажать.
            +4

            Зачем тянуть? Можно даблкликнуть на уголок, за который вы тяните — оно само скопируется до низа таблицы.
            Не благодарите. =)

              0
              Это в чём так?

              У меня LibreOffice и в нём не получилось :(
                +2

                MS Excel

                  0
                  Круто.
                  +2
                  LibreOffice5 — всё работает
                    +1
                    Да, Ваша правда.

                    Пытался делать так на столбце находящимся через один пустой от столбца с данными.

                    Проверил — он копирует до низа соседнего столбца слева. Не до конца самого короткого или самого длинного столбца на листе.
            +3
            Кажется любая приличная база данных умеет загружать данные из csv напрямую

            http://dev.mysql.com/doc/refman/5.7/en/load-data.html
            https://www.sqlite.org/cli.html#csv_import

            Кстати, в условии задачи не было специфицировано, что БД именно MySQL или его форк.
              0
              MySQL это я на своём примере, как и PHP. И того и другого можно легко заменить на любой другой SQL или язык программирования.

              А в условиях просто SQL и неизвестно поддерживает ли он импорт из CSV. Поэтому этот вариант просто не рассматривал.
              0
              Когда хочется поиграть в vimgolf, но под рукой только Excel…
                +1

                [^0-9,-] — мягко говоря, не самая удачная регулярка для данной задачи… строки типа "1-2-3", "1,2,3", etc. останутся как валидные

                  +1
                  Задача не в том что бы проверить достоверность или корректность вводимых данных, а в том что бы не дать пропихнуть среди этих данных SQL-код.

                  Просто потому что вариант некорректности данных не рассматривается в условиях задачи.
                  А вариант что данные небезопасны должен учитываться всегда, а то будет как в случае с Joomla и взломом через User-Agent — http://www.securitylab.ru/news/477643.php

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

                  Вот картинка в LO после работы регулярных выражений.
                  image
                    +1

                    Во Вы даёте! Такой длинный коммент в ответ написать, при этом не прочитав тот, на который отвечали )))
                    Я ж Вам примеры "чисел" привёл, которые Ваша проверка пропустит до уровня SQL.
                    Или это был велосипед для защиты от SQL-Injection? А то, что SQL на вставку не сработает, — это ok?


                    В любом случае, то, что Вы делаете, противоречит фразе "применил регулярные выражения для удаления всего кроме числовых значений". Т.к. ряд вариантов нечисловых значений останется нетронутым.

                      +1
                      А то, что SQL на вставку не сработает, — это ok?


                      Да, регулярка нужна не для исправления некорректных данных, а для безопасности.

                      Не срабатывание импорта по причине наличия смысловых ошибок в исходном файле — ответственность поставщика файла. Всё на что я имею право в данной ситуации — сообщить поставщику файла о том, что данные были импортированы некорректно.

                      Но, Вы навели меня на одну мысль — в реальной задаче, сперва нужно постить в тестовую таблицу. Ну или с транзакциями.
                  0
                  А я бы использовал хранимую процедуру, которая бы непосредственно занималась вставкой данных в таблицу.
                  Любой мусор-нечисло споткнётся на проверке параметров процедуры, не дойдя и близко к инсерту.
                  После загрузки процедуру можно сразу же дропнуть, или оставить для следующей порции данных.
                    0
                    Я бы использовал powershell. И данные можно причесывать, и с бд умеет работать.

                    Only users with full accounts can post comments. Log in, please.