Import csv files into MySQL table

[cah]

My 2016 Volvo XC60 T6 comes with Volvo On Call that is handy. It keeps driving journal that helps organize and analyze the driving performance.

The report comes with the following fields:

Category;Started;Start odometer (miles);Start address;Stopped;End odometer (miles);End address;Duration;Distance (miles);Fuel consumption (gallons);Title;User Notes;

I manually created a table to accommodate these fields.
Some steps need to be taken before the import will work.

1. Use 'notepad' CTRL+H to replace " h" to "" on the csv file.
2. Change "duration" format from "HH:MM h" to "HH:MM:SS".
3. Change "started" and "stopped" datetime format from "MM/DD/YY HH:MM APM" to "YYYY-MM-DD HH:MM:SS"
4. Remove the last 2 empty columns (Title and User Notes)
5. Sort "started" with ascending (from earliest to latest)
6. Replace Category "Unassigned" with the index key
7. Remove the first line/row of the file
8. Upload the csv file to the server (hsiao.net)
9. Use "dos2unix" to convert the file

Code: Select all

# dos2unix <original csv file> <new csv file>

10. Import with the following sql command in workbench (Execute SQL Script in Connected Server) or command line:

Code: Select all

LOAD DATA INFILE '/<PATH>/<new csv file>' 
INTO TABLE Automobile.2016_Volvo_XC60_VOC 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

It will report some the following messages:

Code: Select all

199 row(s) affected
Records: 199  Deleted: 0  Skipped: 0  Warnings: 0

[cah]

Since the old Solaris 11 x86 server crashed (for unknown reason), I had to rebuild a server so I installed Centos 7.3.1611.

Things have changed a little (or a lot). For this post's information, 'dos2unix' is slightly different. MySQL execute command needs the csv file to be in a specific directory before the command can be executed.

1. Use 'notepad' CTRL+H to replace " h" to "" on the csv file.
2. Change "duration" format from "HH:MM h" to "HH:MM:SS".
3. Change "started" and "stopped" datetime format from "MM/DD/YY HH:MM APM" to "YYYY-MM-DD HH:MM:SS"
4. Remove the last 2 empty columns (Title and User Notes)
5. Sort "started" with ascending (from earliest to latest)
6. Replace Category "Unassigned" with the index key
7. Remove the first line/row of the file
8. Upload the csv file to the server (hsiao.net)

9. "dos2unix' will convert the file to itself:

Code: Select all

dos2unix <file>

10. "LOAD DATA INFILE" has to pick up files from a specific directory (using the following command to find out). Otherwise, it gives the following error:

Code: Select all

Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

Code: Select all

mysql> SHOW VARIABLES LIKE "secure_file_priv"; 
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.00 sec)

In the future, the SQL command will be more uniformed (at least the path):

Code: Select all

LOAD DATA INFILE '/var/lib/mysql-files/20170317.csv' 
INTO TABLE Automobile.2016_Volvo_XC60_VOC 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'