A common request with MySQL is to import/export CSV files. CSVs are merely delimited text files and so this is relatively easy to accomplish. To import we’ll be using a LOAD DATA query and for exporting the mysqldump tool bundled with MySQL. We’ll be delimiting our CSV with commas and enclosing each field with double quotation marks. Let’s get to it
mysqldump -uusername -p -T/var/tmp --fields-terminated-by=, --fields-enclosed-by=\" tablename databasename
This is to be typed into the terminal. Here’s a breakdown of what’s going on:
-u determines which user we’re logging into MySQL as
-p is the user’s password (If the user doesn’t have a password – not recommended – remove this)
-T is the folder the output file will be stored in. This must be writable by mysqldump
–fields-terminate-by determines how we’re delimiting our fields. You can make this any unique character
–fields-enclosed-by will stop fields with commas in them from screwing up our CSV file
note: The output file will be given the extension .txt.
LOAD DATA LOCAL INFILE '/var/tmp/test.csv' INTO TABLE <table> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' (id, name);
This is pretty similar to the mysqldump however it is a query entered into MySQL. For the most part it’s self explanatory. The final line are the column you’re importing into (in the order they appear in the CSV). This is useful if the CSV doesn’t contain all the columns in your table – for instance the ID column.