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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Экспорт MySQL в CSV

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Импорт CSV в MySQL

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

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

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

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

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

mysql –u username –p

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

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

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

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

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

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

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

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

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

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

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.

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

Заключение

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

How to Import CSV File in MySql Database

How to deal with Common Errors. 7-Step To the Point Guide to Load CSV in MYSQL .

Steps to Follow:

Identify the CSV file dataset

My dataset is named: mpg.csv

Step 2: Connect to MySQL / Or use MYSQL Workbench Directly

To connect to MySQL:

Step 3: Create New Database

Step 4: Use the new database

Step 5: Create a Table in New Database With Column Names:

Note: The column and datatype parameters in the SQL table that we will create must match the number of columns and data types of the CSV file.

Along with the column names, you also need to specify the data types of columns.

Most commonly used is varchar :
Varchar: A variable-length string (can contain letters, numbers, and special characters). The size parameter specifies the maximum column length in characters — can be from 0 to 65535

To Refer to different Data Types in Mysql: W3 school Link

STEP 6: Import the CSV data into the MySQL table

Dealing with Errors if any you encounter:

Loading of local data could be disabled in both client and server. To successfully load the file, you need to enable it:

For client-side enabling use this command to “ON” local sharing

For server-side enabling:

Go to Edit Connection — Advanced Setting Tab and write this commands as shown in the image:

How to Import CSV File in MySQL Database?

How To Import CSV File In MySQL Database

In this tutorial, you will learn how to import a CSV file in MySQL Database using command Line and MySQL Workbench.

Introduction

CSV(Comma Separated Values) file is a type of file that contains values delimited by commas. The CSV files allow data to be stored in tabular format. MySQL is a relational database management software that stores relational data which means that the data is stored in tabular format. What we’ll do in this tutorial is, we’ll import that CSV data into a table so that we could use that data for analysis or manage the data using SQL.

For this tutorial you’ll need:

MySQL installed on your desktop/ laptop
A CSV file

Importing CSV file using MySQL Command Line

Here are the steps to follow and you’ll be able to import any CSV file to the MySQL database very easily.

Step 1: Select a CSV file

Choose the CSV file want to add to your database and save it at this location:

Folder Where You Need To Save Your CSV File For MySQL Import

Save your CSV file in the uploads folder

Note, if you can’t see the program data folder in c drive then this means that the folder is hidden, to make it visible you need to perform the following actions:

Click on View in the toolbar
Click on show
Check Hidden Items

Step 2: Open MySQL command-line client

You can either open it with the window’s command prompt or directly open MySQL command Line Client from the start bar.

MySQL Using Command Prompt

Opening MySQL Using Command Prompt

MySQL Using Command Line Client

Opening MySQL Using Command Line Client

In both cases, you’ll be able to connect to MySQL as long as you are putting in the correct password.

Step 3: Check the databases

Check databases present in your MySQL server, select the apt database to create a table or you can create a new database.

Selecting A Database And Creating A Table

Checking Databases and Tables present in MySQL

In the above image you can see a few commands, now let’s go through them one by one for a better understanding of the process.

This command is for viewing all the databases present in MySQL.

If you have a database to work on then go with the below command:

In the above example, we’ve used the WORK database. The above command helps you to work on a particular database rather than working over MySQL.

If you don’t have any database to work on then you can create a new database by using:

This command helps you to see all the tables present in your current database.

As you can see, in the above example, no tables are present, or if in your case a table is present in your database then you can work on it just remember that it’s empty or of no use and you’d have to do a little tweaking which will be easy by referring to MySQL manual.

Step 4: Load the CSV file into a table

How To Load CSV File To MySQL

How to Import CSV file in MySQL

You can use the below command to import a CSV File in a particular table:

And that’s how you upload a CSV file in MySQL using the MySQL 8.0 command-line client. Note that, if you’re uploading a huge file it takes a lot of time to convert from CSV to Tabular format in MySQL.

Importing CSV files using MySQL Workbench

Importing a CSV file using MySQL Workbench is a much easier and self-explanatory way of importing a CSV file. In this section, we’ll learn how to perform the steps.

Step 1: Launch MySQL Workbench and set the connection.

Select Existing Connection Or Create A New One

Step 2: Select a database you want to work on.

Open Workbench 1

Open Workbench and Select a database to work on

In the above example, We’ve used db database, you can either choose a database or can create a new one. For creating a new database write the following command:

Step 3: Right-click over the table, and select Table Data Import Wizard.

Right Click On Table And Click On Import Data Wizard

Click On Table Import Data Wizard

By clicking on Table Data Import Wizard, a pop-up window will appear and we just need to follow the procedure.

Step 4: Importing the table using Import Wizard

1. Enter the path of your CSV file in the File Path area and press the next button.

Enter The CSV File Path

2. Select Destination tab will give you 2 options, one you can use an existing table or you can create a new one. In this example, we are using an existing table and press next.

Select Existing Table Or Create A New One

3. Configure the Import Settings tab, in this tab you just need to enter the name of columns and let Encoding be set to default, don’t change it.

Set Name For Columns

4. Import Data Tab will prompt you two activities which will take place, Prepare Import and Import data file. Here, at the bottom you’ll find the show logs button, you can click on it if you want to see the logs, or else you can simply click on next.

Click On Next

Import Data Tab

5. Click On Finish.

Click On Finish

Step 4: Check the table, to see if data is imported properly or not. And you are done here.

Check The Data

Conclusion

This is how you import a CSV file in MySQL Database using MySQL Workbench. For more information, check the resources section below!