*_jemarea_*

Website Development

  • Home
  • Articles
    • All articles
    • Linux
    • WordPress
    • Codeigniter
    • PHP
    • JavaScript
    • MySql
  • About
  • Contact

June 3, 2018 By jerome Leave a Comment

Load data infile – Select into outfile

Working with MySql?testjem123

There is an easy way to get data in or out using CSV files.

  1. 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
  2. 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

Filed Under: MySql

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Copyright © 2023 · jemarea.com