Importing Data

There are a number of ways to import data into a MySQL or MariaDB database.

My SQL Workbench

If you connect to a database instance you can then right click on a schema or "Tables" and select "Table Data Import Wizard", from where it is quite easy to locate a CSV file to import as well as control field types for the new table that will be created.

Command Line

You can use something like this:
load data local infile 'my_data_file.csv' into table my_data_table;
However I found MariaDB does not support this. In addition you need to have created the table first. It is well worth looking this command up in the documentation as it has a lot of very useful options, see MySQL :: MySQL 5.7 Reference Manual :: 13.2.6 LOAD DATA INFILE Syntax.

The following does work in MariaDB and show how you get load data to use a dd/mm/yyyy date format instead of insisting on yyyy-mm-dd which is the default. The CSV file I was using was generated by Excel, which explains the other options.
load data infile '/home/geoff/DataFile_20180619.csv' into table VersionInfo fields optionally enclosed by '"' terminated by ',' ignore 1 lines (Version, @var1, Notes) set Date = STR_TO_DATE(@var1, '%d/%m/%Y');
The date formatting is documented at MySQL :: MySQL 5.7 Reference Manual :: 12.7 Date and Time Functions.

If "load data infile...." gives a permission denied error message then you might be trying to load the file off the server when you wanted it from your local machine, hence "load data local infile...." is what you need.


If you just want to run a command from the shell then try this:
mysqlimport --local database_name table_name.csv
mysqlimport --local --fields-optionally-enclosed-by='"' --fields-terminated-by=',' --lines-terminated-by='\r\n' --ignore-lines=1 database_name table_name.csv
Internally this utility uses "load data" as described above and like it has many options.
It is important to note that you will need the "--local" as without this mysqlimport looks for the file on the server and will report "Error: 13, Can't get stat...". The other point to note is that the csv filename must match that of the table. So this is two areas where "load data" is actually easier and more flexible. See MySQL :: MySQL 5.7 Reference Manual :: 4.5.5 mysqlimport — A Data Import Program for more details.