Генерируйте осмотр схемы таблицы Excel (CSV) и импортируйте данные
Как я обошел бы создание схемы таблицы MYSQL, осмотрев Excel (или CSV) файл. Есть ли какие-либо готовые библиотеки Python для задачи?
Заголовки столбцов были бы санированы к именам столбцов. Тип данных был бы оценен на основе содержания столбца электронной таблицы. При выполнении данные будут загружены в таблицу.
У меня есть файл Excel ~200 столбцов, которые я хочу начать нормализовать.
4 ответа
Просто для (моей) справки, я задокументировал ниже, что я сделал:
- XLRD практичен, однако я только что сохранил данные Excel как CSV, так что я могу использовать
LOAD DATA INFILE - Я скопировал строку заголовка и начал писать скрипт импорта и нормализации
- Скрипт делает:
CREATE TABLEсо всеми столбцами как TEXT, кроме Primary key - query mysql:
LOAD DATA LOCAL INFILEзагрузка всех CSV данных в TEXT поля. - на основе результатов
PROCEDURE ANALYSEя смогALTER TABLEпридать столбцам нужные типы и длины.PROCEDURE ANALYSEвозвращаетENUMдля любого столбца с несколькими различными значениями, что не то, что мне нужно, но я нашел это полезным позже для нормализации. С помощьюPROCEDURE ANALYSEотследить 200 столбцов было проще простого. Вывод из PhpMyAdmin, предлагающий структуру таблиц, был хламом. - Я написал несколько нормализаций, в основном используя
SELECT DISTINCTдля столбцов иINSERTс занесением результатов в отдельные таблицы. Сначала я добавил в старую таблицу столбец для FK. Сразу послеINSERTя получил его ID иUPDATEотредактировал колонку FK. После завершения цикла я удалил старый столбец, оставив только столбец FK. Аналогично с несколькими зависимыми столбцами. Это оказалось намного быстрее, чем я ожидал. - Я запустил (django)
python manage.py inspctdb, скопировал вывод в models.py и добавил все этиForeignkeyFieldы, поскольку FK не существуют в MyISAM. Написал немного python views.py, urls.py, несколько шаблонов...TADA
Быстрый и грязный обходной путь с phpmyadmin:
- Создайте таблицу с нужным количеством столбцов. Убедитесь, что данные помещаются в столбцы.
- Импортируйте CSV в таблицу.
- Используйте предложенную структуру таблицы.
Используйте модуль xlrd; начните здесь. [Отказ от ответственности: я автор]. xlrd классифицирует ячейки на текст, число, дату, булеву, ошибку, пустые и пустые. Он отличает даты от чисел, проверяя формат, связанный с ячейкой (например, "dd/mm/yyyy" против "0.00").
Работа по программированию некоторого кода для просмотра введенных пользователем данных, чтобы решить, какой тип данных БД использовать для каждого столбца, не является чем-то, что можно легко автоматизировать. Вы должны быть в состоянии просмотреть данные и назначить такие типы, как целое число, деньги, текст, дата, время и т.д., и написать код для проверки ваших предположений. Обратите внимание, что вы должны уметь справляться с такими вещами, как числовые данные или дата, введенные в текстовые поля (в графическом интерфейсе они могут выглядеть нормально). Вам нужна стратегия для обработки ячеек, которые не соответствуют типу данных "расчетный". Необходимо проверять и очищать данные. Убедитесь, что вы нормализовали текстовые строки (удалите ведущие и последующие пробельные символы, замените несколько пробельных символов одним пробелом. Текст Excel - это (только для BMP) Юникод; не перегоняйте его в ASCII или "ANSI" - работайте в Юникоде и кодируйте в UTF-8, чтобы поместить его в базу данных.
Насколько я знаю, не существует инструмента, который мог бы автоматизировать этот процесс (я хотел бы, чтобы кто-нибудь доказал мою неправоту, поскольку у меня была именно эта проблема раньше).
Когда я это сделал, у меня было два варианта:
(1) Вручную создайте столбцы в базе данных с соответствующими типами, а затем импортируйте, или
(2) Напишите какой-нибудь фильтр, который мог бы «выяснить», какими типами данных должны быть столбцы.
Я выбрал первый вариант в основном потому, что не думал, что смогу написать программу для вывода типов.
Если вы все же решите написать средство вывода / преобразования типа, вот пара проблем, с которыми вам, возможно, придется столкнуться:
(1) Даты Excel фактически хранятся как количество дней, прошедших с 31 декабря 1899 года; как тогда сделать вывод, что столбец - это даты, а не некоторые числовые данные (например, численность населения)?
(2) Для текстовых полей вы просто создаете столбцы типа varchar (n), где n - самая длинная запись в этом столбце, или вы делаете его неограниченным полем char, если одна из записей длиннее некоторого верхнего предела? Если да, то каков хороший верхний предел?
(3) Как автоматически преобразовать число с плавающей запятой в десятичное с правильной точностью и без потери разряда?
Очевидно, это не значит, что вы не сможете (я довольно плохой программист). Я надеюсь, что вы это сделаете, потому что это был бы действительно полезный инструмент.