ItSolutionStuff.com

How to Import CSV File using MySQL?

By Hardik Savani • February 3, 2023
MySql

In this post we have learn how to import CSV file using LOAD DATA INFILE statement.

this is a very simple example for a import file to the database.

Here we have provide a full sysntax for LOAD DATA INFILE statement.

Sysntax

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'

[REPLACE | IGNORE]

INTO TABLE tbl_name

[CHARACTER SET charset_name]

[{FIELDS | COLUMNS}

[TERMINATED BY 'string']

[[OPTIONALLY] ENCLOSED BY 'char']

[ESCAPED BY 'char']

]

[LINES

[STARTING BY 'string']

[TERMINATED BY 'string']

]

[IGNORE number LINES]

[(col_name_or_user_var,...)]

[SET col_name = expr,...]

Description

Before imort any file must be follow this.

1. A database table to which the data from the file will be imported.

2. A CSV file fields match with database table's field

3. The account, which connects to the MySQL database server, has FILE and INSERT privileges.

Example

For example we have created a one table that name users

We have use CREATE TABLE Statement for create a database table

CREATE TABLE users (

id INT NOT NULL AUTO_INCREMENT,

first_name VARCHAR(255) NOT NULL,

last_name VARCHAR(255) NOT NULL,

PRIMARY KEY (id)

);

Sample of CSV file

id,first_name,last_name

1,"Hardik","Savani"

2,"Harshad","Pathak"

3,"Vimal","Kashiyani"

4,"Uttam","Panara"

5,"Harsukh","Makwana"

The following statement though you have import that users.csv file to database's users table

LOAD DATA INFILE 'c:/tmp/users.csv'

INTO TABLE users

FIELDS TERMINATED BY ','

ENCLOSED BY '"'

LINES TERMINATED BY '\n'

IGNORE 1 ROWS;

LOAD DATA INFILE : this is a used for reads rows form a file into a table at a very high speed.

FIELD TERMINATED BY : This is a use for which sign use for terminated for a each fields.

ENCLOSED BY : This is a use for a which sign use for enclosed for a fields.

LINES TERMINATED BY : This is a use for a terminated a one recors or one table row.

I hope it can help you...

Tags: MySql
Hardik Savani

Hardik Savani

I'm a full-stack developer, entrepreneur, and founder of ItSolutionStuff.com. Passionate about PHP, Laravel, JavaScript, and helping developers grow.

📺 Subscribe on YouTube

We Are Recommending You

How to Install MySQL in Ubuntu Server?

Read Now →

Node JS CRUD with MySQL Tutorial Example

Read Now →

How to Create a REST API using Node.js and MySQL?

Read Now →

How to Export Mysql Database using Command Line in Ubuntu?

Read Now →

How to Import Database in Mysql using Command Line in Ubuntu?

Read Now →

How to add 1 day to the date column in MySQL?

Read Now →

MYSQL Query for Data between Two Dates Example

Read Now →

How to Get Filename from File Path in MySQL Query?

Read Now →

How to Get Last 2 Days Records from Table using MySQL Query?

Read Now →

MySQL Query to Get Current Year Data Example

Read Now →

MySQL Calculate Age from Date of Birth Example

Read Now →

Laravel Where Clause with MySQL Function Example

Read Now →

Which MySQL Datatype use for Store an IP Address?

Read Now →