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

How to import a CSV file into a MySQL database?

Using mock data from a fictional app, you will learn how to import a CSV file into a MySQL database via the terminal.

To accomplish this task you will need to:

1. Identify your data
2. Connect to MySQL
3. Create a database
4. Create a table
5. Load the data

*I am using MySQL version 5.7.21 on macOS High Sierra

Step 1: Identify your data

The name of the file is called mock_data.csv

The data represents general information about our users in the app.

Let’s open the CSV file with a text editor (I’m using Sublime):

*Notice that the data is a string with the comma character as the field separator (delimiter). Each line of the CSV file is terminated by a newline character.

Step 2: Connect to the MySQL server

Once you have installed MySQL, log in as the root users

Step 3: Create a database

Before we create a database, let’s see all the current databases on the server.

Let’s add a new database called “app”

Yes. Let’s use the database “app” moving forward.

Step 4: Create a table

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

• We created a table named “users”, with six columns: “id”, “first_name”, “last_name”, “email”, “transactions”, and “account_creation”. This matches the headers in the CVS file.
• AUTO_INCREMENT allows a unique number to be generated automatically when a new record is inserted into a table.
• INT, VARCHAR, and DATE are data types. Data types specify what the type of data can be found in that specific column.
• By default, a column can hold NULL VALUES (represents a missing value). Using the NOT NULL statement, we are telling MySQL that columns can not accept NULL values. This enforces the columns to always contain a value.
• VARCHAR(255) holds a variable length string. The maximum size is specified in parenthesis.
• PRIMARY KEY(id) means that the id column will only hold unique values and not NULL values.

Step 6: Import the CSV data into the MySQL table

• “The LOCAL keyword affects where the file is expected to be found. The file is read by the client program on the client host and sent to the server. If LOCAL is not specified, the file must be located on the server host and is read directly by the server.” Reference manual
• Since the comma is the delimiter, we use the FIELDS TERMINATED BY ‘,’
• Each line of the CSV file is terminated by a newline character, thus we use LINES TERMINATED BY ‘\n’
• We already have the header in the MySQL table so we use IGNORE 1 ROWS
• The default format for the DATE datatype is YYYY-MM-DD. The dates in our CSV is different than what’s expected. Therefore, we need to change the format using the STR_TO_DATE() function.

We can check the table to see whether the data was imported:

If you have any questions or find any errors, let me know in the comments below. Thanks for reading!

How do I import CSV file into a MySQL table?

I have an unnormalized events-diary CSV from a client that I’m trying to load into a MySQL table so that I can refactor into a sane format. I created a table called ‘CSVImport’ that has one field for every column of the CSV file. The CSV contains 99 columns , so this was a hard enough task in itself:

No constraints are on the table, and all the fields hold VARCHAR(256) values, except the columns which contain counts (represented by INT), yes/no (represented by BIT), prices (represented by DECIMAL), and text blurbs (represented by TEXT).

I tried to load data into the file:

The whole table is filled with NULL .

I think the problem is that the text blurbs contain more than one line, and MySQL is parsing the file as if each new line would correspond to one databazse row. I can load the file into OpenOffice without a problem.

The clientdata.csv file contains 2593 lines, and 570 records. The first line contains column names. I think it is comma delimited, and text is apparently delimited with doublequote.

I added some information to the LOAD DATA statement that OpenOffice was smart enough to infer, and now it loads the correct number of records:

But still there are lots of completely NULL records, and none of the data that got loaded seems to be in the right place.

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:

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.

Opening MySQL Using Command Prompt

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.

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 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 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.

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.

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!

Import CSV File Into MySQL Table

This tutorial shows you how to use the LOAD DATA INFILE statement to import CSV file into MySQL table.

The LOAD DATA INFILE statement allows you to read data from a text file and import the file’s data into a database table very fast.

Before importing the file, you need to prepare the following:

• A database table to which the data from the file will be imported.
• A CSV file with data that matches with the number of columns of the table and the type of data in each column.
• The account, which connects to the MySQL database server, has FILE and INSERT privileges.

Suppose we have a table named discounts with the following structure:

We use CREATE TABLE statement to create the discounts table as follows:

The following discounts.csv file contains the first line as column headings and other three lines of data.

The following statement imports data from the c:\tmp\discounts.csv file into the discounts table.

The field of the file is terminated by a comma indicated by FIELD TERMINATED BY ‘,’ and enclosed by double quotation marks specified by ENCLOSED BY ‘» ‘.

Each line of the CSV file is terminated by a newline character indicated by LINES TERMINATED BY ‘\n’ .

Because the file has the first line that contains the column headings, which should not be imported into the table, therefore we ignore it by specifying IGNORE 1 ROWS option.

Now, we can check the discounts table to see whether the data is imported.

Transforming data while importing

Sometimes the format of the data does not match the target columns in the table. In simple cases, you can transform it by using the SET clause in the LOAD DATA INFILE statement.

Suppose the expired date column in the discount_2.csv file is in mm/dd/yyyy format.

When importing data into the discounts table, we have to transform it into MySQL date format by using str_to_date() function as follows:

Importing file from client to a remote MySQL database server

It is possible to import data from client (local computer) to a remote MySQL database server using the LOAD DATA INFILE statement.

When you use the LOCAL option in the LOAD DATA INFILE , the client program reads the file on the client and sends it to the MySQL server. The file will be uploaded into the database server operating system’s temporary folder e.g., C:\windows\temp on Windows or /tmp on Linux. This folder is not configurable or determined by MySQL.

Let’s take a look at the following example:

The only difference is the LOCAL option in the statement. If you load a big CSV file, you will see that with the LOCAL option, it will be a little bit slower to load the file because it takes time to transfer the file to the database server.

The account that connects to MySQL server doesn’t need to have the FILE privilege to import the file when you use the LOCAL option.

Importing the file from client to a remote database server using LOAD DATA LOCAL has some security issues that you should be aware of to avoid potential security risks.

Importing CSV file using MySQL Workbench

MySQL workbench provides a tool to import data into a table. It allows you to edit data before making changes.

The following are steps that you want to import data into a table:

Open table to which the data is loaded.

Click Import button, choose a CSV file and click Open button

Review the data, click Apply button.

MySQL workbench will display a dialog “Apply SQL Script to Database”, click Apply button to insert data into the table.

We have shown you how to import CSV into MySQL table using LOAD DATA LOCAL and using MySQL Workbench. With these techniques, you can load data from other text file formats such as tab-delimited.