0

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

Edited by Amaina

Attachments
id,reference_type,text_styles,author,year,title,pages,secondary_title,volume,number,number_of_volumes,secondary_author,place_published,publisher,subsidiary_author,edition,keywords,type_of_work,date,abstract,label,url,tertiary_title,tertiary_author,notes,isbn,custom_1,custom_2,custom_3,custom_4,alternate_title,accession_number,call_number,short_title,custom_5,custom_6,section,original_publication,reprint_edition,reviewed_item,author_address,image,caption,custom_7,electronic_resource_number,link_to_pdf,translated_author,translated_title,name_of_database,database_provider,research_notes,language,access_date,last_modified_date,owner,date_requested,date_ordered,date_received,date_dispatched,cost
1,0,,"Fokam, E. B., Levai, L. D., Guzman, H., Amelia, P. A., Titanji, V. P., Tesh, R. B., Weaver, S. C.",2010,Silent circulation of arboviruses in Cameroon,262-8,East Afr Med J,87,6,,,,,,2010/06/01,"Arbovirus Infections/ epidemiology/ transmission/virology., Arboviruses/ isolation & purification., Cameroon/epidemiology., Humans",,Jun,"OBJECTIVES: To investigate the silent circulation and transmission of arthropod-borne viruses (arboviruses) in the Fako Division of Cameroon. DESIGN: This survey was conducted based on clinical observations and laboratory diagnosis; field collections of mosquitoes. SETTING: This study was conducted in the Fako Division of South West Cameroon. SUBJECTS: One hundred and two sera were obtained from febrile patients (with negative laboratory findings for malaria and typhoid fever) at clinics in the Fako Division, and diurnal anthropophilic mosquitoes (4,764) collected. INTERVENTIONS: Virus isolation was attempted from these, and sera were screened for antibodies against 18 African arboviruses by haemagglutination inhibition (HI) and complement fixation (CF) tests. RESULTS: No virus was isolated. Fifty three of 79 (67.1%) sera reacted with one or more viral antigens. Twenty nine sera (36.7%) reacted with members of the genus Alphavirus, with Chikungunya (CHIKV) and O'nyong-nyong (ONNV) viruses as the most frequent (34.2%). Forty six sera (58.2%) reacted with members of the genus Flavivirus: 24 (30.4%) were cross-reactive, but 11.4% reacted monotypically with Zika, 5.1% with yellow fever virus (YFV), 5.1% with dengue virus-2 (DENV-2), 2.5% with DENV-1 and 1.3% with Wesselsbron virus, respectively. The plaque reduction neutralisation test used to specify the agent that elicited the response could not resolve 33.3% of the cross reactions between CHIKV and ONNV. Neutralising antibody titres against ONNV and CHIKV were very high indicating probable re-infection. CONCLUSION: Our results indicate previously undetected circulation of arboviruses in Cameroon, and suggest that they are important, overlooked public health problems.",,,,,"Fokam, E B., Levai, L D., Guzman, H., Amelia, P A., Titanji, V P K., Tesh, R B., Weaver, S C., AI069145/AI/NIAID NIH HHS/United States., AI25489/AI/NIAID NIH HHS/United States., TW006590/TW/FIC NIH HHS/United States., Research Support, N.I.H., Extramural., Kenya., East African medical journal., East Afr Med J. 2010 Jun;87(6):262-8.","0012-835X (Print)., 0012-835X (Linking)",,,,,,23057269,,,,,,,,,"Department of Plant and Animal Sciences, and Biotechnology Unit, University of Buea, Cameroon.",,,,,,,,,Nlm,,eng,,,John Doe,2016-02-09,2016-03-18,
2
Contributors
7
Replies
28
Views
1 Year
Discussion Span
Last Post by Amaina
2

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.

0

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

0

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?

1

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.

0

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

1

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:

1

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

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.