Как импортировать метку времени из файла CSV в MySQL?
Файл CSV (значения, разделенные запятыми) представляет собой текстовый файл с информацией, разделяемой запятыми. Они чаще всего встречаются в электронных таблицах и базах данных и в настоящее время в основном используются для наборов данных в науке о данных и машинном обучении. Они помогают предприятиям экспортировать большие объемы данных. Однако мы не можем выполнять запросы SQL к таким данным CSV, поэтому мы должны преобразовать их в структурированные таблицы.
Существует множество способов преобразования данных CSV в структурированную таблицу. Мы рассмотрим три наиболее популярных метода преобразования файлов .csv в формат SQL. Эта задача становится более сложной, когда данные, разделенные запятыми, которые мы используем для этого, содержат метку времени.
В этой статье мы рассмотрим три различных метода импорта файлов .csv в таблицы MySQL вместе с атрибутом метки времени.
Импорт файлов .CSV в таблицы MYSQL:
Это может быть достигнуто различными способами. Три метода, которые мы обсудим, используют разные методы для импорта файлов .csv в таблицы MySQL. Мы рассмотрим следующие методы:
- Использование командной строки
- Использование PhpMyAdmin
- Использование MySQL Workbench
Использование командной строки:
Шаг 1: Откройте окно терминала и войдите в клиент MySQL , используя пароль. Обратитесь к следующей команде:
mysql -u root -p
Шаг 2: Создайте базу данных, а затем создайте таблицу внутри этой базы данных. Данные файла .CSV будут входными данными для этой таблицы:
#To create a database CREATE DATABASE database_name; #To use that database use database_name; #To create a table CREATE TABLE table_name( id INTEGER, col_1 VARCHAR(100), col_2 INTEGER, col_3 DATETIME DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id) );
Мы создаем базу данных и таблицу с именем table_name внутри нее. Таблица содержит различные атрибуты, такие как id, col_1, col_2 и col_3. Атрибут col_3 имеет тип DATETIME и содержит как данные, так и время в формате ГГГГ-ММ-ДД чч:мм:сс. В дополнение к атрибутам integer и varchar мы рассмотрим, как импортировать атрибут типа DateTime из файла CSV в таблицу MySQL.
Шаг 3: Теперь мы должны проверить переменную MySQL «secure_file_priv» . По умолчанию сервер MySQL запускается с параметром -secure-file-priv. При использовании LOAD DATA INFILE он указывает каталог, из которого файлы данных могут быть загружены в данный экземпляр базы данных. Чтобы просмотреть настроенный каталог, используйте следующую команду:
SHOW VARIABLES LIKE "secure_file_priv";
Выход:
Переменная «secure_file_priv» будет установлена на каталог файлов, настроенный сервером MySQL. Теперь у нас есть два варианта правильной работы команды LOAD DATA INFILE:
- Переместите входной CSV-файл в указанную структуру папок.
- Изменить настроенную структуру папок под наши нужды.
Мы рассмотрим оба этих подхода.
Подход 1:
При таком подходе нам нужно будет поместить входной CSV-файл в указанную структуру папок, и только тогда мы сможем получить доступ к файлу для загрузки в базу данных. Как только мы это сделаем, теперь мы готовы запустить команду для импорта файлов CSV в базу данных. Обратитесь к следующей команде:
LOAD DATA INFILE "{folder_structure}/{csv_file_name}"
INTO TABLE table_name
FIELDS TERMINATED BY ","
ENCLOSED BY """
LINES TERMINATED BY "
"
IGNORE 1 ROWS;- LOAD DATA INFILE — указывает расположение входного CSV-файла.
- INTO TABLE — указывает таблицу, в которую должны быть помещены данные.
- FIELDS TERMINATED BY — указывает разделитель, с помощью которого разделяются отдельные значения в файле.
- ENCLOSED BY — указывает символ, который определяет значения в CSV-файле.
- LINES TERMINATED BY — указывает код разрыва строки.
- IGNORE 1 ROWS — указывает количество игнорируемых строк во входном CSV-файле, который может содержать метки столбцов и т. д.
Выход:
Подход 2:
Для Windows:
Шаг 1: Необходимо изменить существующую структуру папок в my.ini. Перейдите на этот компьютер -> диск C -> ProgramData -> MySQL -> сервер MySQL * -> my.ini [Папка ProgramData скрыта. Чтобы увидеть папку, перейдите в «Просмотр» в верхней строке меню и включите «Скрытые элементы».]
Шаг 2: Откройте файл и найдите «secure-file-priv». Это будет выглядеть примерно так.
# Secure File Priv. secure-file-priv=”C:/ProgramData/MySQL /MySQL Server 5.7/Uploads”
Шаг 3: Измените структуру папок по вашему выбору и сохраните файл в другом месте, так как файл не может быть сохранен в том же месте. Теперь скопируйте и вставьте файл в его фактическое местоположение. Для этого нам нужен доступ администратора.
Шаг 4: Теперь нажмите «Ctrl + R» -> Введите «services.msc» -> Нажмите «Enter». Откроется вкладка «Услуги».
Шаг 5: Найдите MySQL57 [номер 57 может отличаться на вашем компьютере]. Щелкните его правой кнопкой мыши, а затем нажмите кнопку перезагрузки.
Шаг 6: Войдите в клиент командной строки MySQL и выполните следующую команду:
LOAD DATA LOCAL INFILE "{file_location}"
INTO TABLE table_name
FIELDS TERMINATED BY ","
ENCLOSED BY """
LINES TERMINATED BY "
"
IGNORE 1 ROWS;Следуя шагам, описанным выше, мы можем импортировать CSV-файл в таблицу MySQL в ОС Windows без каких-либо ошибок.
Для Linux:
Шаг 1: Нам нужно добавить новую структуру папок по нашему выбору в файл my.cnf. Файл находится в папке /etc/mysql/. Откройте файл my.cnf с помощью редактора по вашему выбору и добавьте в файл следующие строки:
[mysqld]
secure-file-priv={new_folder_structure}Шаг 2: Сохраните файл после внесения изменений и перезапустите службу MySQL. Для этого обратитесь к следующей команде:
sudo systemctl restart mysql
Шаг 3: снова войдите в клиент MySQL, используя следующую команду:
mysql -u root -p --local-infile
Шаг 4: После входа в систему выполните следующую команду:
set global local_infile = 1; SHOW VARIABLES LIKE "local_infile";
Выход:
Шаг 5: Теперь мы готовы импортировать данные файла CSV в таблицу MySQL. Обратитесь к следующей команде:
LOAD DATA LOCAL INFILE "{file_location}"
INTO TABLE table_name
FIELDS TERMINATED BY ","
ENCLOSED BY """
LINES TERMINATED BY "
"
IGNORE 1 ROWS;Выход:
Следуя описанным выше шагам, мы можем импортировать файл CSV в таблицу MySQL в ОС Linux без каких-либо ошибок.
Следующая команда может использоваться для определения того, правильно ли импортирован атрибут dob с типом метки времени. Таким образом, мы можем гарантировать, что атрибут в таблице соответствует фактическому типу данных DateTime MySQL:
SELECT * FROM table_name WHERE col_3
= TIMESTAMP("{YYYY-MM-DD HH:MM:SS}");
Необязательный шаг: преобразование данных при импорте
Когда вы хотите загрузить данные из файла в таблицу MySQL, вы можете заметить, что входящий формат данных и времени не совсем соответствует целевой таблице. В качестве примера рассмотрим формат метки времени col_3 в элементах. Если формат целевой таблицы — «гггг/мм/дд %H:%i:%s.%f», а формат CSV-файла — «дд/мм/гггг %H:%i:%s.%f» . Предложение SET в операторе LOAD DATA INFILE можно использовать для преобразования формата даты и времени CSV-файла в формат, используемый целевой таблицей в MySQL. Выполняя операцию загрузки данных из файла в таблицу в MySQL, вы можете использовать предложение SET в конце вместе с функцией str to date(). Обратитесь к следующей команде:
LOAD DATA INFILE "{file_location}"
INTO TABLE table_name
FIELDS TERMINATED BY ","
ENCLOSED BY """
LINES TERMINATED BY "
"
IGNORE 1 ROWS
(id, col_1, col_2, @col_3)
SET col_3 = timestamp(str_to_date(@col_3,"%d-%m-%Y %H:%i:%s.%f"));С помощью PhpMyAdmin:
phpMyAdmin — это бесплатный программный инструмент на основе PHP, предназначенный для администрирования MySQL через Интернет. phpMyAdmin может выполнять широкий спектр операций MySQL. Пользовательский интерфейс можно использовать для выполнения часто используемых операций (таких как управление базами данных, таблицами, столбцами, отношениями, индексами, пользователями и разрешениями), в то же время вы можете выполнять любые операторы SQL напрямую.
Давайте посмотрим, как мы можем использовать PhpMyAdmin для импорта CSV-файла в базу данных MySQL.
Шаг 1: Когда вы запустите PhpMyAdmin, вы увидите различные базы данных и таблицы в левой части интерфейса. Создайте таблицу, в которую вы хотите импортировать данные файла CSV. Столбцы и типы данных должны соответствовать соответствующим данным в файле CSV.
Шаг 2: Выберите таблицу, а затем нажмите кнопку импорта в верхней строке меню.
Шаг 3: Нажмите кнопку «Выбрать файл» и перейдите в локальное хранилище, чтобы найти входной CSV-файл. На этой же странице вы также можете указать количество пропущенных строк в файле.
Шаг 4: Прокрутите страницу вниз, чтобы выбрать формат файла, в нашем случае это csv. Как и в случае с командной строкой, здесь у нас есть несколько параметров, специфичных для формата. Когда мы закончим, нажмите кнопку «Перейти» .
Шаг 5: Файл CSV импортируется в таблицу MySQL. При успешном импорте мы видим следующие сообщения.
Шаг 6: Теперь мы можем вернуться к таблице и изучить данные, импортированные из CSV-файла.
Использование MySQL Workbench:
MySQL Workbench — это визуальный инструмент проектирования баз данных, который объединяет разработку SQL, администрирование, проектирование, создание и обслуживание базы данных MySQL в единой интегрированной среде разработки.
Давайте посмотрим, как мы можем использовать MySQL Workbench для импорта CSV-файла в базу данных MySQL.
Шаг 1: Когда вы запустите MySQL Workbench, вы увидите различные базы данных и таблицы в левой части интерфейса. Создайте таблицу, в которую вы хотите импортировать данные файла CSV. Столбцы и типы данных должны совпадать с соответствующими данными в файле CSV.
Шаг 2: Щелкните правой кнопкой мыши таблицу и выберите параметр «Мастер импорта данных таблицы» . Просмотрите локальное хранилище, чтобы выбрать входной CSV-файл.
Шаг 3: Выберите кодировку файла и сопоставьте столбцы источника и назначения, чтобы настроить параметры импорта. Когда вы закончите, нажмите кнопку «Далее».
Шаг 4: Данные входного CSV-файла затем будут импортированы в таблицу MySQL. Затем мы можем проверить импортированные данные в интерфейсе рабочей среды MySQL.
Вывод:
В этой статье продемонстрированы три метода импорта CSV-файла в MySQL. Теперь вы знаете, как импортировать CSV-файлы в MySQL с помощью командной строки, phpMyAdmin и MySQL Workbench . Не стесняйтесь добавлять любые дополнительные инструменты, которые могут помочь в импорте файлов CSV в MySQL.