Mysql load infile

[Amaina]

I am at my wits end. I have written this query which is aimed at importing my csv data uinto mysql table 'refs'

load data local infile '/home/amaina/Documents/march2016.csv' into table refs fields terminated by ',' enclosed by '"' escaped by '' lines terminated by '\n' ignore 1 rows;

My pain is that whenever i run this query i get the following results:
Query OK, 0 rows affected, 33 warnings (0.00 sec)
Records: 11 Deleted: 0 Skipped: 11 Warnings: 33

The CSV file looks something like this (attached) i changed it to .txt for it to be uploaded

What can be possibly be doing wrong. I have been splitting hairs for the past 6 hours

[cereal]

Hi,

look at the result message:

Query OK, 0 rows affected, 33 warnings (0.00 sec)
Records: 11 Deleted: 0 Skipped: 11 Warnings: 33

by running SHOW WARNINGS; after the load query, you should get some information about the statement issues.

[Amaina]

Thanks Cereal. I am getting a host of warnings mainly warning 1265, 1366, 1262

[Amaina]

After filling-out the empty fields, i get the following message

Query OK, 0 rows affected (0.00 sec)
Records: 11 Deleted: 0 Skipped: 11 Warnings: 0

Still, no records are loading. What might i be doing wrong?

[cereal]

Your query, with a custom table and data, works fine for me. Could you provide your table schema? Run this:

show create table `refs`\G

Also: is the above example data still valid? So that I can test it.

[Amaina]

Yes the data is still valid. See schema below

mysql> show create table refs \G;
*************************** 1. row ***************************

mysql> show create table refs \G;
*************************** 1. row ***************************
       Table: refs
Create Table: CREATE TABLE `refs` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `reference_type` smallint(5) unsigned NOT NULL DEFAULT '0',
  `text_styles` mediumtext NOT NULL,
  `author` mediumtext NOT NULL,
  `year` mediumtext NOT NULL,
  `title` mediumtext NOT NULL,
  `pages` mediumtext NOT NULL,
  `secondary_title` mediumtext NOT NULL,
  `volume` mediumtext NOT NULL,
  `number` mediumtext NOT NULL,
  `number_of_volumes` mediumtext NOT NULL,
  `secondary_author` mediumtext NOT NULL,
  `place_published` mediumtext NOT NULL,
  `publisher` mediumtext NOT NULL,
  `subsidiary_author` mediumtext NOT NULL,
  `edition` mediumtext NOT NULL,
  `keywords` mediumtext NOT NULL,
  `type_of_work` mediumtext NOT NULL,
  `date` mediumtext NOT NULL,
  `abstract` mediumtext NOT NULL,
  `label` mediumtext NOT NULL,
  `url` mediumtext NOT NULL,
  `tertiary_title` mediumtext NOT NULL,
  `tertiary_author` mediumtext NOT NULL,
  `notes` mediumtext NOT NULL,
  `isbn` mediumtext NOT NULL,
  `custom_1` mediumtext NOT NULL,
  `custom_2` mediumtext NOT NULL,
  `custom_3` mediumtext NOT NULL,
  `custom_4` mediumtext NOT NULL,
  `alternate_title` mediumtext NOT NULL,
  `accession_number` mediumtext NOT NULL,
  `call_number` mediumtext NOT NULL,
  `short_title` mediumtext NOT NULL,
  `custom_5` mediumtext NOT NULL,
  `custom_6` mediumtext NOT NULL,
  `section` mediumtext NOT NULL,
  `original_publication` mediumtext NOT NULL,
  `reprint_edition` mediumtext NOT NULL,
  `reviewed_item` mediumtext NOT NULL,
  `author_address` mediumtext NOT NULL,
  `image` mediumtext NOT NULL,
  `caption` mediumtext NOT NULL,
  `custom_7` mediumtext NOT NULL,
  `electronic_resource_number` mediumtext NOT NULL,
  `link_to_pdf` mediumtext NOT NULL,
  `translated_author` mediumtext NOT NULL,
  `translated_title` mediumtext NOT NULL,
  `name_of_database` mediumtext NOT NULL,
  `database_provider` mediumtext NOT NULL,
  `research_notes` mediumtext NOT NULL,
  `language` mediumtext NOT NULL,
  `access_date` mediumtext NOT NULL,
  `last_modified_date` mediumtext NOT NULL,
  `owner` varchar(40) DEFAULT NULL,
  `date_requested` date DEFAULT NULL,
  `date_ordered` date DEFAULT NULL,
  `date_received` date DEFAULT NULL,
  `date_dispatched` date DEFAULT NULL,
  `cost` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `yearIndex` (`year`(5))
) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR: 
No query specified       Table: refs

Create Table: CREATE TABLE refs (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
reference_type smallint(5) unsigned NOT NULL DEFAULT '0',
text_styles mediumtext NOT NULL,
author mediumtext NOT NULL,
year mediumtext NOT NULL,
title mediumtext NOT NULL,
pages mediumtext NOT NULL,
secondary_title mediumtext NOT NULL,
volume mediumtext NOT NULL,
number mediumtext NOT NULL,
number_of_volumes mediumtext NOT NULL,
secondary_author mediumtext NOT NULL,
place_published mediumtext NOT NULL,
publisher mediumtext NOT NULL,
subsidiary_author mediumtext NOT NULL,
edition mediumtext NOT NULL,
keywords mediumtext NOT NULL,
type_of_work mediumtext NOT NULL,
date mediumtext NOT NULL,
abstract mediumtext NOT NULL,
label mediumtext NOT NULL,
url mediumtext NOT NULL,
tertiary_title mediumtext NOT NULL,
tertiary_author mediumtext NOT NULL,
notes mediumtext NOT NULL,
isbn mediumtext NOT NULL,
custom_1 mediumtext NOT NULL,
custom_2 mediumtext NOT NULL,
custom_3 mediumtext NOT NULL,
custom_4 mediumtext NOT NULL,
alternate_title mediumtext NOT NULL,
accession_number mediumtext NOT NULL,
call_number mediumtext NOT NULL,
short_title mediumtext NOT NULL,
custom_5 mediumtext NOT NULL,
custom_6 mediumtext NOT NULL,
section mediumtext NOT NULL,
original_publication mediumtext NOT NULL,
reprint_edition mediumtext NOT NULL,
reviewed_item mediumtext NOT NULL,
author_address mediumtext NOT NULL,
image mediumtext NOT NULL,
caption mediumtext NOT NULL,
custom_7 mediumtext NOT NULL,
electronic_resource_number mediumtext NOT NULL,
link_to_pdf mediumtext NOT NULL,
translated_author mediumtext NOT NULL,
translated_title mediumtext NOT NULL,
name_of_database mediumtext NOT NULL,
database_provider mediumtext NOT NULL,
research_notes mediumtext NOT NULL,
language mediumtext NOT NULL,
access_date mediumtext NOT NULL,
last_modified_date mediumtext NOT NULL,
owner varchar(40) DEFAULT NULL,
date_requested date DEFAULT NULL,
date_ordered date DEFAULT NULL,
date_received date DEFAULT NULL,
date_dispatched date DEFAULT NULL,
cost int(11) DEFAULT NULL,
PRIMARY KEY (id),
KEY yearIndex (year(5))
) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR:
No query specified

[cereal]

By loading your data with that table structure I get:

load data local infile '/tmp/march2016.csv' into table refs fields terminated by ',' enclosed by '"' escaped by '' lines terminated by '\n' ignore 1 rows;
Query OK, 1 row affected, 3 warnings (0.02 sec)      
Records: 1  Deleted: 0  Skipped: 0  Warnings: 3

> show warnings;
+---------+------+----------------------------------------------------+
| Level   | Code | Message                                            |
+---------+------+----------------------------------------------------+
| Warning | 1265 | Data truncated for column 'date_received' at row 1 |
| Warning | 1261 | Row 1 doesn't contain data for all columns         |
| Warning | 1261 | Row 1 doesn't contain data for all columns         |
+---------+------+----------------------------------------------------+
3 rows in set (0.00 sec)

But the data is inserted, however it seems that your csv has only 58 columns, while the table has 60 columns, so this generates the warnings with code 1261.

You could also check if an SQL Mode is enabled, you can do that by running:

SELECT @@sql_mode;

In some cases, for example when strict mode is enabled, the insert can be rejected. More info here:

• http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

[Amaina]

Thank you very much @cereal for your help. The problem has been solved.