Working with MySql?testjem123
There is an easy way to get data in or out using CSV files.
- Getting the data from your MySql table into a CSV fileFrom your terminal log into mysql using your credentials. Usually something like this:
$ mysql -u <user> -pEnter your password and go to the respective database from which you would like to retrieve the data
mysql> use my_database;Now pick the fields you would like to have from your table and write them into a file:
SELECT field1, field2 FROM my_table INTO OUTFILE '/home/me/file.csv' FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n'You can add more options to it. See here: SELECT INTO OUTFILE
- Getting the data back into a database table
This is the opposite process and as simple as the first:
Here you will have to determine which the fields are you would like to put your information in, and how these fields are defined in the CSV file.
mysql> LOAD DATA INFILE 'file.csv' INTO TABLE my_table FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n' IGNORE 1 LINES;The “IGNORE 1 LINES” is there of course only if the first line of your CSV is representing the field names.
For more options, see LOAD DATA INFILE