Loading csv into mysql selecting columns

Question

I am trying to learn how to use efficiently mysql. Now, I want to load into a mysql database a csv containing the bibliography of an author. This is the code I have generating the database and trying to upload the file:

USE stephenkingbooks;

DROP TABLE IF EXISTS stephenkingbooks;
CREATE TABLE stephenkingbooks
(
  `id`              int unsigned NOT NULL auto_increment,
  `original_title`  varchar(255) NOT NULL,
  `spanish_title`   varchar(255) NOT NULL,
  `year`            decimal(4) NOT NULL,
  `pages`           decimal(10) NOT NULL,
  `in_collection`   enum('Y','N') NOT NULL DEFAULT 'N',
  `read`            enum('Y','N') NOT NULL DEFAULT 'N',

  PRIMARY KEY     (id)
);

LOAD DATA LOCAL INFILE '../files/unprocessed_sking.csv'
INTO TABLE stephenkingbooks (column1, column2, column4, column3)
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 ROWS;

The csv file is format like this:

Carrie,Carrie,Terror,199,19745,"En 1976, el director de cine Brian de Palma hizo la primera película basada en la novela.7 3"

My idea is to load only the two first columns corresponding to the original_title, the second being the spanish title (the same in mysql and the csv) and after the column3 in csv would be the pages and the column4 the year.

In addition, for the year column, I only want to take the 4 first numbers of the field because I have some of them with a reference that is not part of the year. For example, Carrie was released in 1974 but the csv includes a 5 in the date that I do not want to consider.

My problem is I am not able to obtain what I want without errors in my terminal... any suggestion?

wchiquito · Accepted Answer · 2015-08-13 15:02:12Z

1

13.2.6 LOAD DATA INFILE Syntax

...

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.

...

Try:

mysql> LOAD DATA INFILE '../files/unprocessed_sking.csv'
    -> INTO TABLE `stephenkingbooks`
    -> FIELDS TERMINATED BY ','
    -> ENCLOSED BY '"'
    -> LINES TERMINATED BY '\r\n'
    -> (`original_title`, `spanish_title`, @`genre`, @`pages`, @`year`)
    -> SET `year` = LEFT(@`year`, 4), `pages` = @`pages`;
Query OK, 1 row affected (0.00 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT
    -> `id`,
    -> `original_title`,
    -> `spanish_title`,
    -> `year`,
    -> `pages`,
    -> `in_collection`,
    -> `read`
    -> FROM `stephenkingbooks`;
+----+----------------+---------------+------+-------+---------------+------+
| id | original_title | spanish_title | year | pages | in_collection | read |
+----+----------------+---------------+------+-------+---------------+------+
|  1 | Carrie         | Carrie        | 1974 |   199 | N             | N    |
+----+----------------+---------------+------+-------+---------------+------+
1 row in set (0.00 sec)

answered Aug 13, 2015 at 15:02

wchiquito

16.6k2 gold badges37 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mastodon Over a year ago

Thank you for the answer! Your code raise me the error: ERROR 29 (HY000): File '/home/samuel/github/mysql_tests/files/unprocessed_sking.csv' not found (Errcode: 13) I already try to change the owner of the directory /var/lib/mysql to mysql:mysql and it is not working

wchiquito Over a year ago

@Mastodon: The user account with which MySQL is running must have permissions to access /home/samuel/github/mysql_tests/files/unprocessed_sking.csv, however, the error is not found the file.

Mastodon Over a year ago

Solved! Thank you very much

Collectives™ on Stack Overflow

Loading csv into mysql selecting columns

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related