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:
- Identify your data
- Connect to MySQL
- Create a database
- Create a table
- 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
User information
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
$ mysql -u root -p
MySQL on the terminal
Step 3: Create a database
Before we create a database, let?s see all the current databases on the server.
mysql> SHOW DATABASES;
Let?s add a new database called ?app?
mysql> CREATE DATABASE app;
Did it work?
Yes. Let?s use the database ?app? moving forward.
mysql> USE app;
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.
mysql> CREATE TABLE users (id INT NOT NULL AUTO_INCREMENT, first_name VARCHAR(255) NOT NULL, last_name VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL, transactions INT NOT NULL, account_creation DATE NOT NULL, PRIMARY KEY (id));
What?s going on?
- 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
mysql> LOAD DATA LOCAL INFILE ‘/Users/miguelgomez/Desktop/mock_data.csv’ INTO TABLE users FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘n’ IGNORE 1 ROWS (id, first_name, last_name, email, transactions, @account_creation)SET account_creation = STR_TO_DATE(@account_creation, ‘%m/%d/%y’);
What?s going on?
- ?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:
mysql> SELECT * FROM users;
Tutorial complete!
If you have any questions or find any errors, let me know in the comments below. Thanks for reading!
