Load .CSV Data into MySQL and Combine Date and Time

Prerequisite – How To Import Timestamp From a CSV File in MySQL?

A CSV (comma-separated values) file is a text file with commas separating information. They are most commonly found in spreadsheets and databases and are now primarily used for datasets in data science and machine learning. They assist businesses in exporting large amounts of data to a larger data lake. However, we cannot run SQL queries on such CSV data, so we must convert it to structured tables.

We’ll look for a way to convert CSV data into a structured table by combining the date and time attributes from the.csv file example image above by using the command line. We achieve this goal using the method called Transformation.

Import .CSV Files Into MYSQL Tables:

Step 1:  Open the terminal window and log in to the MySQL Client using the password. Refer to the following command: 

mysql -u root -p

Step 2: Create a database and then create a table inside that database. The CSV file data will be the input data for this table:  

#To create a database
CREATE DATABASE database_name;

#To use that database
use database_name;

#To create a table
CREATE TABLE table_name(
id INTEGER,
col_1 VARCHAR(100),
col_2 INTEGER,
col_3 DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id)
);

We create a database and a table called table_name inside it. The table contains various attributes such as id, col_1, col_2, and col_3. The col_3 attribute is of the DATETIME type, and it contains both data and time in the format YYYY-MM-DD hh:mm: ss. In addition to the integer and varchar attributes, we’ll look at how to import a DateTime type attribute from a CSV file into a MySQL table.  

Step 3: Now we must examine the MySQL variable “secure_file_priv”. By default, the MySQL server starts with the -secure-file-priv option. When using LOAD DATA INFILE, it specifies the directory from which data files can be loaded into a given database instance. To view the configured directory, use the following command:

SHOW VARIABLES LIKE ‘secure_file_priv’;

Output:

Output For Steps 1, 2 and 3

Step 4: Move the input .csv file into the specified folder structure. We will need to put the input csv file into the specified folder structure, and then only we will be able to access the file to be loaded into the database. Once we do this, now we are ready to run the command to import csv file to the database. Refer to the following command:

LOAD DATA INFILE '{folder_structure}/{csv_file_name}'
INTO TABLE table_name
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(id, col_1, col_2, @date, @time)
SET col_3 = timestamp(str_to_date(@date,'%Y-%m-%d'), 
str_to_date(@time, '%H:%i:%s'));

1. LOAD DATA INFILE – It specifies the location of the input CSV file.
2. INTO TABLE – It specifies the table into which data is to be populated.
3. FIELDS TERMINATED BY – It specifies the delimiter through which the individual values in the file are separated.
4. ENCLOSED BY – It specifies the symbol which specifies the values in CSV file.
5. LINES TERMINATED BY – It specifies the code for a line break.
6. IGNORE 1 ROWS – It specifies the number of lines to be ignored from an input CSV file which might contain column labels, etc.

The last two lines, however, are the most important in this task. Each of the columns corresponding to our input data has been named (id, col 1, col 2, @date, @time). When a column name is preceded by a @ sign, it is transformed into a column represented by a local variable. The other column names are implied to correspond with database columns, even if the database columns aren’t always in the same order. The SET clause allows you to perform preprocessing transformations on the values of these local variables before assigning the result to columns.

The SET statement instructs MySQL to populate col_3 with timestamp datatype from our .csv file by combining date and time columns. The timestamp function in MySQL converts given dates and times to DateTime format. To convert our date and time strings into different MySQL data types, we use the str_to_date() function. It accepts a date/time string as well as its format as an argument.

Output:

Read more about the str_to_date() function here and how to load a locally stored .csv file into a MySQL table here.

Step 5: Following the steps outlined above, we can import a CSV file to a MySQL table without encountering any errors. The following commands can be used to check the imported data as well as to determine whether or not the dob attribute with timestamp type has been properly imported. In this way, we can ensure that the attribute in the table corresponds to the actual DateTime datatype of MySQL:

SELECT * FROM table_name;
SELECT * FROM table_name WHERE 
col_3 = TIMESTAMP('{YYYY-MM-DD HH:MM:SS}');

Output: