Import CSV File Using Command Line on Mac OS (LOAD DATA INFILE)

Step 1: Access MySQL Shell

Let’s login to MySQL, access your terminal window and log into MySQL using the following command:

mysql –u root 

or

mysql –u username –p

Step 2: Check The Available Databases

Let’s see the available databases using the following command:

show databases;

Step 3: Select The Database We Want To Import Into

Select a database by entering the following command:

use database_name;

Step 4: Create MySQL Table for CSV Import

The columns in your MySQL table need to match the data from the CSV file you plan to import. If you already have a table ready for the CSV import, you can skip to Step 5 of the tutorial.

For example I use the dataset at the following link:

Open Data Provinsi Jawa Barat

This steps to create table:

create table umkm_jabar(
    id int,
    kode_provinsi int,
    nama_provinsi varchar(50),
    kode_kabupaten_kota int,
    nama_kabupaten_kota varchar(50),
    kategori_usaha varchar(50),
    proyeksi_jumlah_umkm int,
    satuan varchar(50),
    tahun int);

Step 5: Import CSV into MySQL Table

Import the data from the CSV file into the MySQL database, using the following lines:

load data local infile '/Users/macbookpro/Downloads/dataumkm.csv' 
into table umkm_jabar 
fields terminated by ',' 
enclosed by '"' 
lines terminated by '\n' 
ignore 1 rows;

Let us, in turn, explore the actions behind each of these lines:

• LOAD DATA INFILE – Defines the location of the CSV file to be imported. Change the path (between the quotes) to match the path and filename of your CSV file. If the CSV file is located on the local machine, you can use the LOAD DATA LOCAL INFILE statement instead.
• INTO TABLE – This indicates the destination table you are importing the CSV file into. Change the table_name to the name of your table.
• FIELDS TERMINATED BY – By default, comma-separated value files use a comma to identify individual data values. If your export file uses a different delimiter, you can modify this value.
• ENCLOSED BY – This specifies that a double-quote mark " surrounds values.
• LINES TERMINATED BY – Use this line to specify the code for a line break.
• IGNORE 1 ROWS; – Many CSV files export with the column labels as the first line. This command tells MySQL to ignore the first row as you have already created your table with the appropriate column headings. The semicolon at the end specifies the end of the command for MySQL to execute.

That’s it we are done, 1350 data is imported in a second. Let’s check it out:

select * from database_name;