Search code examples
mysqlsqlcsvsequelpro

Importing another table into a database and adding fields

I have a csv file which I wish to import into my database. However, the columns in the csv file do not match the columns in the table I want to import it into. I would like to do two things:

  1. Import the table but only some of the columns
  2. Provide default values for columns which the table I am importing does not have.

I hope I explained this well enough. Not too sure how I can add any 'code' for this question, but I have put in some images.

Here is my existing table:

Columns in existing table

Here are the columns for the csv file I wish to import

enter image description here

I would like to somehow only import the name (as title), slug, and created_at, updated_at for this csv file into my table. Additionally, I would like the ID to be added automatically, and the 'exam_id' and 'popular' booleans to be set to 0 by default.

Solution

  • LOAD DATA INFILE is what you need.

    Read carefully this: http://dev.mysql.com/doc/refman/5.7/en/load-data.html.

    The most interesting part for you is:

    By default, when no column list is provided at the end of the LOAD DATA INFILE statement, input lines are expected to contain a field for each table column. If you want to load only some of a table's columns, specify a column list:

    LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata
(col1,col2,...);

    You must also specify a column list if the order of the fields in the input file differs from the order of the columns in the table. Otherwise, MySQL cannot tell how to match input fields with table columns.

    The column list can contain either column names or user variables. With user variables, the SET clause enables you to perform transformations on their values before assigning the result to columns.

    User variables in the SET clause can be used in several ways. The following example uses the first input column directly for the value of t1.column1, and assigns the second input column to a user variable that is subjected to a division operation before being used for the value of t1.column2:

    LOAD DATA INFILE 'file.txt'   INTO TABLE t1   (column1, @var1)   SET
column2 = @var1/100;

    The SET clause can be used to supply values not derived from the input file. The following statement sets column3 to the current date and time:

    LOAD DATA INFILE 'file.txt'   INTO TABLE t1   (column1, column2)   SET
column3 = CURRENT_TIMESTAMP;

    You can also discard an input value by assigning it to a user variable and not assigning the variable to a table column:

    LOAD DATA INFILE 'file.txt'   INTO TABLE t1   (column1, @dummy,
column2, @dummy, column3);