CODEWITHSUNDEEP
mysqlcsvload
Mastodon
Mastodon

Reputation: 151

Loading csv into mysql selecting columns

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?

Upvotes: 0

Views: 1165

Answers (1)

wchiquito
wchiquito

Reputation: 16551

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)

Upvotes: 1

Related Questions