Как загрузить csv в mysql

Импорт CSV-файла в таблицу MySQL

В этой статье мы расскажем вам, как использовать оператор LOAD DATA INFILE для импорта CSV-файла в таблицу MySQL.

Оператор LOAD DATA INFILE позволяет считывать данные из текстового файла и очень быстро импортировать их в таблицу базы данных.

Перед импортом файла, вам необходимо подготовить следующее:

• Таблицу базы данных, в которую будут импортированы данные из файла;
• CSV-файл с соответствующим числом столбцов и соответствующим форматом данных в каждом столбце;
• Учетную запись пользователя, который подключается к серверу базы данных MySQL и имеет привилегии FILE и INSERT .

Предположим, что мы имеем таблицу, которая называется discounts со следующей структурой:

Мы используем оператор CREATE TABLE , чтобы создать таблицу discounts :

Обновлено: 2014-03-12 11:18:17 Вадим Дворников автор материала

CREATE TABLE discounts ( id INT NOT NULL AUTO_INCREMENT, title VARCHAR(255) NOT NULL, expired_date DATE NOT NULL, amount DECIMAL(10,2) NULL, PRIMARY KEY (id) );

Файл discounts.csv в первой строке содержит заголовки столбцов, в трех других строках — данные:

Форматирование даты в MySQL задаётся при помощи mysql date format.

Следующий оператор импортирует данные из файла c:tmpdiscounts.csv в таблицу discounts :

LOAD DATA INFILE 'c:/tmp/discounts.csv' INTO TABLE discounts FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY 'n' IGNORE 1 ROWS;

Поля файла завершаются запятой, относящейся к FIELD TERMINATED BY ‘,’ , которая заключена в двойные кавычки, предусмотренные форматом ENCLOSED BY ‘»‘ .

Каждая строка CSV файла завершается символом новой строки, обозначающим TERMINATED BY ‘n’ .

Поскольку первая строка файла содержит заголовки столбцов, которые не должны быть импортированы в таблицу, мы игнорируем ее, указав опцию IGNORE 1 ROWS .

Теперь мы можем проверить, импортированы ли данные в таблицу discounts :

SELECT * FROM discounts;

Преобразование данных при импорте

Иногда формат данных не соответствует целевым столбцам таблицы. В простых случаях, вы можете преобразовать их с помощью условия SET в операторе LOAD DATA INFILE .

Предположим, что столбец данных срока действия скидок в файле discount_2.csv имеет формат мм / дд / гггг:

Взаимодействие Java с MySQL требует использования JDBC драйвера для выполнения запросов к базе данных.

При импорте данных в таблицу discounts мы должны преобразовать их в формат даты MySQL с помощью функции str_to_date() :

LOAD DATA INFILE 'c:/tmp/discounts_2.csv' INTO TABLE discounts FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY 'n' IGNORE 1 ROWS (title,@expired_date,amount) SET expired_date = STR_TO_DATE(@expired_date, '%m/%d/%Y');

Импорт файла клиента для замещения на сервере базы данных MySQL

Можно импортировать данные от клиента (локального компьютера) на удаленный сервер базы данных MySQL, с помощью оператора LOAD DATA INFILE .

При использовании опции LOCAL в LOAD DATA INFILE клиентская программа считывает файл на стороне клиента и отправляет его на сервер MySQL. Файл будет загружен во временную папку базы данных сервера операционной системы, например, C: Windows Temp для ОС Windows или / TMP для Linux .

Эта папка не настраивается и не задается MySQL.

Давайте рассмотрим следующий пример:

LOAD DATA LOCAL INFILE 'c:/tmp/discounts.csv' INTO TABLE discounts FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY 'n' IGNORE 1 ROWS;

Разница заключается только в опции LOCAL оператора. Если вы загружаете большой CSV файл, вы увидите, что с опцией LOCAL загрузка осуществляется немного медленнее, потому что требуется определенное время, чтобы передать файл серверу базы данных.

Учетная запись пользователя, под которым мы подключается к серверу MySQL для импорта данных, может не иметь привилегию FILE , если используется опция LOCAL .

При импорте файла от клиента на удаленный сервер базы данных с помощью LOAD DATA LOCAL могут возникнуть некоторые проблемы с безопасностью, о которых вы должны знать, чтобы избежать потенциальных рисков.

Импорт CSV файла с помощью MySQL Workbench

MySQL Workbench предоставляет инструмент для импорта данных в таблицу БД. Он позволяет редактировать данные перед внесением изменений.

Ниже приведены этапы импорта данных в таблицу БД:

Откройте таблицу, в которую будут загружаться данные:

Нажмите кнопку Import , выберите CSV файл и нажмите кнопку Open :

Просмотрите данные, нажмите кнопку Apply :

В MySQL Workbench появится диалоговое окно « Apply SQL Script to Database », нажмите кнопку Apply , чтобы добавить данные в таблицу БД.

Мы рассказали вам, как импортировать CSV в таблицу MySQL с помощью LOAD DATA LOCAL , а также с использованием MySQL Workbench .

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

Вадим Дворников автор-переводчик статьи « Import CSV File Into MySQL Table »

Экспорт и импорт CSV в MySQL

Файл CSV (Comma Separated Values — значения, разделенные запятыми) использует запятые для разделения различных значений в файле. Файл CSV является стандартным форматом при переносе таблицы в другую систему или ее импорте в другое приложение базы данных.

Это подробное руководство покажет вам, как экспортировать базу данных MySQL в файл CSV и импортировать файл CSV обратно в базу данных MySQL.

Экспорт MySQL в CSV

1. Доступ к командной строке или окну терминала
2. Учетная запись пользователя с привилегиями root или sudo
3. Учетная запись пользователя MySQL с правами root
4. Предварительно настроенная учетная запись phpMyAdmin (необязательно)

Экспорт MySQL в CSV с phpMyAdmin

Инструмент phpMyAdmin предоставляет бесплатный графический интерфейс для управления базами данных MySQL. Вы можете использовать его для экспорта любой из отслеживаемых баз данных в файл CSV.

1. Войдите в phpMyAdmin.
2. Затем нажмите кнопку Databases (Базы данных) в верхней части баннера.
3. В списке баз данных щелкните ссылку на базу данных, которую вы хотите экспортировать. В этом примере мы выбрали базу данных user .
4. На следующем экране отображается список таблиц в этой базе данных. Установите флажки для таблиц, которые вы хотите экспортировать.
5. Нажмите кнопку Export на баннере внизу.
6. Оставьте метод экспорта установленным как есть. Используйте раскрывающееся меню Format, чтобы выбрать CSV, затем нажмите Go.
7. Диалоговое окно предлагает указать место, где вы хотите сохранить файл CSV.

Экспорт из MySQL в CSV с помощью командной строки

Вы можете выполнить экспорт без излишеств через CLI, выбрав все данные в таблице и указав место, куда их нужно сохранить.

Начните с открытия оболочки MySQL, затем переключитесь на базу данных, которую вы хотите экспортировать.

Введите следующую команду:

SELECT * FROM myTable INTO OUTFILE ' mpmyExportFile.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY ' ';

Замените myTable реальным именем таблицы из вашей базы данных. Вы можете заменить mpmyExportFile.csv любым другим именем файла или местоположением. Не забудьте сохранить имя файла .csv в конце.

Примечание. В этом примере используется местоположение файла Linux. Если вы работаете в Windows, вы можете использовать c:/folder/file.csv для вашего местоположения файла.

Дополнительные параметры для экспорта из MySQL

Чтобы указать отдельные наборы данных для экспорта из таблицы:

SELECT column1, column2, column3, column4 FROM myTable WHERE column2 = 'value';

Замените column1 (и остальные) фактическими именами столбцов, которые вы хотите экспортировать. Обязательно используйте команду FROM , чтобы указать таблицу, из которой вы экспортируете. Оператор WHERE является необязательным и позволяет экспортировать только те строки, которые содержат определенное значение. Замените значение фактическим значением, которое вы хотите экспортировать. Например:

SELECT order_date, order_number, order_status FROM current_orders WHERE order_status='pending';

myTable реальным именем экспортируемой таблицы.

Примечание: вы можете заметить команду SELECT * FROM в скобках. Мы обернули команду в функцию, которая добавляет метку времени.

Экспорт с заголовками столбцов

Используйте оператор UNION для добавления заголовков столбцов в экспортируемый файл:

(SELECT 'column1','column2','column3','column4') UNION (SELECT column1, column2, column3, column4 FROM myTable INTO OUTFILE ' mpmyExportFile.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY ' ')

Работа с пустыми значениями

Если в базе данных есть нулевые (пустые) значения, этот процесс экспортирует их как букву N . Вы можете заменить значения NULL другой строкой текста, которая имеет больше смысла:

SELECT column1, column2, IFNULL(column3, 'N/A') FROM myTable INTO OUTFILE ' mpmyExportFile.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY ' ');

В этом случае команда IFNULL ищет пустые значения в column3 . Когда он находит их, он заменяет их текстовой строкой N/A .

Экспорт MySQL в CSV с помощью mysqldump

Вы можете использовать приложение mysqldump для экспорта базы данных MySQL в файл CSV. Введите в командной строке следующее:

mysqldump ––tab=/var/lib/mysql–files/ ––fields–enclosed–by='"' ––fields–terminated–by=',' ––lines–terminated–by=' ' myTable

Эта команда создает копию базы данных myTable в /var/lib/mysql-files .

Примечание. Ваша учетная запись должна иметь права доступа к каталогу, в котором сохранен файл CSV. Вы можете сохранить его по другому пути, но это должен быть путь, к которому у MySQL есть полный доступ.

Экспорт MySQL в CSV с использованием CSV Engine

В некоторых случаях вы можете использовать CSV Engine в MySQL для изменения таблицы. Этот метод не будет работать, если ваша таблица имеет индекс или если ваша таблица использует auto_increment . Используйте следующую команду:

ALTER TABLE myTable ENGINE=CSV;

Этот оператор изменяет формат базы данных на CSV. Затем вы можете скопировать файл CSV в другую систему.

В определенных конфигурациях, CSV Engine или метод mysqldump также могут сэкономить много времени.

Импорт CSV в MySQL

1. Доступ к командной строке или окну терминала
2. CSV-файл, содержащий данные, которые вы хотите импортировать
3. Учетная запись пользователя MySQL с привилегиями FILE и INSERT
4. Предварительно настроенная учетная запись phpMyAdmin (необязательно)

Импорт файла CSV с помощью командной строки

Шаг 1: Доступ к оболочке MySQL

Откройте окно терминала и войдите в MySQL, используя следующую команду:

mysql –u username –p

Замените username вашим реальным именем пользователя. Система предложит вам ввести пароль для вашего пользователя MySQL. Ввод правильного пароля дает вам доступ к клиенту MySQL.

Шаг 2. Создание таблицы MySQL для импорта в CSV

Столбцы в вашей таблице MySQL должны соответствовать данным из файла CSV, который вы планируете импортировать. Если у вас уже есть таблица, готовая для импорта в CSV, вы можете перейти к шагу 3 руководства.

Выберите базу данных, введя следующую команду:

USE database_name;

Замените database_name именем базы данных, в которую вы импортируете данные. Используйте следующую команду для создания новой таблицы:

CREATE TABLE table_name ( id INT NOT NULL AUTO_INCREMENT, column_1 VARCHAR(255) NOT NULL, column_2 DATE NOT NULL, column_3 DECIMAL(10 , 2 ) NULL, column_4 INTEGER, PRIMARY KEY (id) );

Замените значение table_name на имя, которое вы хотите использовать для своей таблицы. Переменная column_n представляет имена для каждого столбца в вашем CSV-файле, и вы должны отредактировать их соответствующим образом.

• В этом примере column_1 отформатирован для текста.
• column_2 отформатирован для дат.
• column_3 отформатирован для валют. Числа в скобках указывают максимальный размер значения и десятичные разряды.
• column_4 отформатирован для целых чисел.

Вы можете добавлять, удалять или изменять типы данных, используемые в этом примере, в соответствии с вашими потребностями. Посетите официальную документацию MySQL по типам данных, чтобы узнать больше.

Шаг 3: Импорт CSV в MySQL Table

Импортируйте данные из файла CSV в базу данных MySQL, используя следующие строки:

LOAD DATA INFILE '/home/export_file.csv' INTO TABLE table_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '/n' IGNORE 1 ROWS;

Давайте, в свою очередь, рассмотрим действия, стоящие за каждой из этих строк:

• LOAD DATA INFILE — Определяет расположение файла CSV для импорта. Измените путь (между кавычками), чтобы он соответствовал пути и имени файла вашего CSV-файла. Если файл CSV находится на локальном компьютере, вместо него можно использовать оператор LOAD DATA LOCAL INFILE .
• INTO TABLE — указывает таблицу назначения, в которую вы импортируете файл CSV. Измените table_name на имя вашей таблицы.
• FIELDS TERMINATED BY – По умолчанию файлы значений, разделенные запятыми, используют запятую для идентификации отдельных значений данных. Если в вашем файле экспорта используется другой разделитель, вы можете изменить это значение.
• ENCLOSED BY — указывает, что двойная кавычка » окружает значения.
• LINES TERMINATED BY — Используйте эту строку, чтобы указать код для разрыва строки.
• IGNORE 1 ROWS; — Многие CSV-файлы экспортируются с метками столбцов в первой строке. Эта команда говорит MySQL игнорировать первую строку, так как вы уже создали свою таблицу с соответствующими заголовками столбцов. Точка с запятой в конце указывает конец команды для выполнения MySQL.

Импорт CSV с помощью phpMyAdmin

Если ваш сервер или база данных использует phpMyAdmin, вы можете использовать графический интерфейс для импорта файла CSV.

1. Войдите в cPanel и запустите phpMyAdmin.
2. Используйте левую панель, чтобы выбрать базу данных и таблицу, в которую вы импортируете CSV-файл.
3. Используйте верхнее меню, чтобы выбрать Import.
4. Нажмите Choose File (Выбрать файл) и перейдите к местоположению файла CSV.
5. Используйте раскрывающееся меню Format, чтобы выбрать CSV, и параметры, относящиеся к формату, чтобы определить параметры для отдельных столбцов.
6. Нажмите Goчтобы начать импорт CSV.

Заключение

Теперь вы знаете, как экспортировать CSV-файлы из MySQL и импортировать CSV-файлы в MySQL из командной строки и с помощью phpMyAdmin.

Как импортировать CSV-файл в MySQL Workbench

Фото Ноя Бойера на Unsplash

Если вы знакомы с процессом поиска работы, вы, вероятно, уже заметили, что некоторые компании любят использовать домашние задания, чтобы определить, подходит ли кандидат или нет. Поскольку большинство компаний используют SQL, часто они хотят посмотреть, можете ли вы решить проблемы с помощью SQL. Однако не все компании предоставят вам какой-либо набор данных для работы. Вполне вероятно, что компания может предоставить только схему таблицы, и вам может быть интересно, действительно ли ваши запросы могут выполняться. Следовательно, импорт набора данных в базу данных может быть очень полезным.

В этой статье я расскажу, как установить MySQL Workbench и импортировать данные в MySQL Workbench шаг за шагом.

Начиная

MySQL Workbench — это единый визуальный инструмент для архитекторов, разработчиков и администраторов баз данных. Он доступен в Windows, Linux и Mac OS X. В этой статье я буду использовать систему Mac OSX. Для начала вам необходимо установить MySQL и MySQL Workbench .

Чтобы установить MySQL с помощью установщика пакетов:

Загрузите файл образа диска ( .dmg ) (версия сообщества доступна здесь ), который содержит установщик пакета MySQL. Дважды щелкните файл, чтобы смонтировать образ диска и просмотреть его содержимое.

Страница установки MySQL (снимок экрана автора)

Не забывайте этот пароль, так как он понадобится вам позже для входа в рабочую среду MySQL! (Скриншот автора)

После установки как MySQL и MySQL Workbench , теперь вы можете открыть MySQL Workbench. Я совершил ошибку, не установив MySQL, так как забыл, что установленный мной SQL был Postgresql. Если бы вы не установили MySQL, вы не смогли бы подключиться к базе данных.

MySQL Workbench не может обнаружить MySQL → ВРЕМЯ ЗАГРУЗИТЬ MYSQL. (Скриншот автора)

Если вы успешно загрузили MySQL, вы увидите всплывающее окно, в котором нужно ввести тот же пароль, который вы установили ранее. После ввода пароля вы должны увидеть что-то вроде этого:

Ура! Вы подключены к MySQL (Скриншот автора)

Теперь вы можете щелкнуть экземпляр MySQL.

Если вы все еще видите эту ошибку даже после установки MySQL, вы можете нажать кнопку « Остановлено » в правом верхнем углу экрана или нажать кнопку «Запуск / выключение».

Сообщение об ошибке не установлено соединение (Скриншот автора)

(Скриншот автора)

Создание схемы / таблицы

Если вы хотите, чтобы таблицы были более организованными, вы можете создать схему для каждого проекта, над которым вы работаете. Так вам будет легче найти разные таблицы для каждого проекта. Вы можете просто щелкнуть правой кнопкой мыши и выбрать « Создать схему ».

Как создать схему (Скриншот автора)

Теперь, когда вы создали схему, вы можете выбрать « Создать таблицу » или « Мастер импорта табличных данных », чтобы построить таблицу в схеме.

Как создать таблицу (Скриншот автора)

Вот что вы увидите при создании таблицы:

Создание пустой таблицы (Скриншот автора)

Импортировать файл CSV

Сначала найдите путь к файлу CSV, который вы хотите импортировать.

Найдите путь к данным (Скриншот автора)

Вы можете создать новую таблицу или добавить к существующей таблице (снимок экрана автора)

К сожалению, мне не удалось импортировать данные, потому что « Мастер импорта табличных данных не работает с файлом в кодировке UTF-8 с BOM». Я не понял, почему это произошло, поскольку я сохранил файл как UTF-8 CSV.

В конце концов, я обнаружил, что каким-то образом для системы Mac OSX нам нужно открыть файл CSV или Excel с помощью Numbers / Notepad, а затем экспортировать файл в формате .csv.

Сохранение файла как CSV-файла (Скриншот автора)

Иногда это будет работать, только если вы измените кодировку на другую (Скриншот автора)

Поздравляем . Ты сделал это! Теперь вы успешно загрузили CSV-файл в рабочую среду MySQL и можете приступать к написанию запросов!

Результат простого запроса (Скриншот автора)

Если вы найдете это полезным, подпишитесь на меня и просмотрите другие мои блоги. ❤️

До следующего раза, удачного обучения! ����‍��

Создание системы рекомендаций по продуктам для электронной коммерции: часть I — парсинг веб-страниц

Как загрузить CSV в MySQL: подробная инструкция с легкими шагами

Чтобы загрузить CSV файл в базу данных MySQL, вы можете использовать команду LOAD DATA INFILE. Вот пример:

LOAD DATA INFILE 'путь_к_файлу.csv' INTO TABLE имя_таблицы FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;

В этом примере, вы должны заменить ‘путь_к_файлу.csv’ на фактический путь к вашему CSV файлу и ‘имя_таблицы’ на имя таблицы, в которую вы хотите загрузить данные.

Детальный ответ

Как загрузить csv в mysql

Привет! Сегодня мы поговорим о том, как загрузить данные из CSV файла в базу данных MySQL. Загрузка данных из CSV файла может быть полезной в различных ситуациях, например, при импорте большого объема данных или при необходимости обновления существующих данных в базе данных.

Шаг 1: Создание таблицы в MySQL

Перед загрузкой данных из CSV файла в базу данных MySQL, необходимо создать таблицу, которая будет содержать эти данные. Давайте создадим таблицу с помощью следующего SQL запроса:

 CREATE TABLE students ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(100) NOT NULL, age INT NOT NULL, email VARCHAR(100) NOT NULL, PRIMARY KEY (id) ); 

Вышеуказанный SQL запрос создает таблицу под названием «students», которая содержит столбцы «id», «name», «age» и «email».

Шаг 2: Загрузка CSV файла в MySQL

После создания таблицы в MySQL, мы можем перейти к загрузке данных из CSV файла. Для этого нам потребуется использовать команду LOAD DATA INFILE. Вот пример команды для загрузки CSV файла «students.csv» в таблицу «students» в базе данных «mydatabase»:

 LOAD DATA INFILE 'путь_к_файлу/students.csv' INTO TABLE students FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS; 

В вышеприведенном примере, мы указываем путь к файлу «students.csv» с помощью ключевого слова «INFILE». Затем мы указываем таблицу «students», в которую хотим загрузить данные. Далее мы определяем символ, разделяющий значения в CSV файле с помощью ключевого слова «FIELDS TERMINATED BY ‘,’. В данном случае мы используем запятую в качестве разделителя значений. Keyевое слово «ENCLOSED BY» используется, чтобы указать символ, который будет использоваться для заключения значений в кавычки. В примере мы использовали двойные кавычки («»). Keyевое слово «LINES TERMINATED BY» определяет символ окончания строки в CSV файле. Наконец, мы указываем «IGNORE 1 ROWS», чтобы проигнорировать первую строку в CSV файле. В данном случае, первая строка может содержать заголовки столбцов, которые мы не хотим загружать в базу данных.

Пример:

Давайте представим, что у нас есть CSV файл «students.csv», содержащий следующие данные:

id,name,age,email 1,John Smith,20,john@example.com 2,Jane Doe,22,jane@example.com 3,Michael Johnson,21,michael@example.com

Мы можем загрузить эти данные в базу данных MySQL с помощью предыдущей команды:

 LOAD DATA INFILE 'путь_к_файлу/students.csv' INTO TABLE students FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS; 

После выполнения этой команды, данные из CSV файла будут загружены в таблицу «students» в вашей базе данных MySQL.

Заключение

Загрузка данных из CSV файла в базу данных MySQL — полезная и востребованная задача во многих проектах. Мы рассмотрели шаги, необходимые для выполнения этой задачи, включая создание таблицы в MySQL и загрузку данных с помощью команды LOAD DATA INFILE. Надеюсь, что этот пост поможет вам загрузить CSV данные в MySQL базу данных. Удачи вам в ваших проектах, и не забывайте быть активным и задавать вопросы!