Why is it that my data is being truncated while uploading an csv file in my database?

Question

Im trying to upload data to my contacts table by importing an csv file. My csv file contains just data, i have removed the column titles.

And my csv looks like this:

Im trying to import it by going to localhost phpmydamin and importing it in a table by browsing this file. Im using utf-8 and my format is CSV using LOAD DATA.

When click on continue i have the following errors:

Error Code: 1265. Data truncated for column 'id' at row 1 
Row 1 does not contain data for all columns

I get the errors above for several rows.

This is my create table in mysql:

CREATE TABLE `contacts` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `lastname` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `position` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `mobile_no` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `mobile_no2` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `descriptionMobile` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `descriptionMobile2` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` int(11) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

csv data in notepad:

1,30 de Agosto TV Color - Miguel Angel de Arrieta,de Arrieta,x,NULL,NULL,Movil,Movil,MdeArrieta@30deagosto.com.ar,1
2,"Banda De Frecuencia 4G LTE Banda 4  1700 MHz Banda 7 2600 MHz Banda 28 700 MHz, Celulares",4G LTE Banda 4  1700 MHz,x,NULL,NULL,Movil,Movil,NULL,1
3,A Beneficio CONABIP,A,x,NULL,NULL,Movil,Movil,abenef@conabip.gov.ar,1
4,AAATesters,AAATesters,x,NULL,NULL,Movil,Movil,orders2@aaatesters.com,1
5,Aaron Isaac Gonzalez,Aaron Isaac,x,1134432558,NULL,Movil,Movil,NULL,1
6,"Aaronia AG,Germany",Aaronia,x,4.97E+12,NULL,Movil,Movil,mail@aaronia.de,1
7,ABA GAS GNC,ABA GAS,x,1122598905,11-4572-3900 ::: 11 6382-7811,Movil,Movil,NULL,1
8,abel cortez,abel,x,NULL,NULL,Movil,Movil,NULL,1
9,Abel Curin,Abel,x,NULL,NULL,Movil,Movil,NULL,1
10,Abogada John Maria Jauregui,Abogada John,x,NULL,NULL,Movil,Movil,abelcor86@yahoo.com.ar,1
11,Abogada Silvina Corazza VIAVI,Abogada Silvina,x,1147536648,NULL,Movil,Movil,abelcurin@mymcom.com.ar,1
12,Abogado Jose M�ndez Marcas Y Patentes,Abogado Jose,x,1147324171,11-474-712-44,Movil,Movil,NULL,1
13,Abogado Multas Infracciones,Abogado,x,54108184,549108184,Movil,Movil,scorazza@clarkemodet.com.ar,1
14,Abonados vtcc,Abonados,x,1150084093,NULL,Movil,Movil,mendezacostaJo@gmail.com,1
15,ACA Automovil Club Argentino,ACA,x,1132696543,NULL,Movil,Movil,NULL,1
16,ACA Nro Socio,ACA Nro Socio,x,NULL,NULL,Movil,Movil,abonados@vtcc.com.ar,1
17,Acontece SRL,Acontece SRL,x,NULL,NULL,Movil,Movil,administracion_seguros@aca.org.ar,1
18,Adan Solian,Adan,x,1144509164,(011) 4458-3194,Movil,Movil,acontec@acontec.com.ar,1
19,Adela Liebhardt,Adela,x,NULL,NULL,Movil,Movil,actv@iverde.dataco33.com.ar,1
20,Adema,Adema,x,NULL,NULL,Movil,Movil,asolian@cybercom-cw.com.ar,1


LOAD DATA INFILE 'C:\\xampp\\tmp\\phpF39A.tmp' INTO TABLE `contacts` FIELDS TERMINATED BY ';' ENCLOSED BY '\"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'

Any reason while im getting this errors?

Add sample rows from the csv file as text, add table definition as text, add load data infile code as text, confirm row terminator. — P.Salmon, Dec 01 '21 at 14:48
Open csv file in a text editor eg notepad copy and paste to question, show create table from phpmyadmin copy and paste result to question, copy and paste load data infile command used to question. — P.Salmon, Dec 01 '21 at 15:38
I do not know if i used LOAD DATA INFILE @P.Salmon is there any way to tell if i used it? — matuco1998, Dec 01 '21 at 15:49
Okey, no i did not use import wizard, is used CSV as LOAD DATA, columns separated by ";", and columns enclosed by between " @P.Salmon How can i get the code of the LOAD DATA INFILE on phpmyadmin? — matuco1998, Dec 01 '21 at 15:54
LOAD DATA INFILE expects you to provide all columns (including defaulted columns) your csv files is 2 short - see manual how to specify columns to be loaded.. — P.Salmon, Dec 01 '21 at 15:56
So should i also add to my csv columns as created_at and updated_at? @P.Salmon — matuco1998, Dec 01 '21 at 15:57
The first column it is for id indeed, and why should my second column be bigint if it is a name field i created it as varchar? I also added the LOAD DATA INFILE code to my question @P.Salmon — matuco1998, Dec 01 '21 at 16:10
You are being disingenuous your table definition before the last edit included a bigint customerid as the second column.. — P.Salmon, Dec 01 '21 at 16:16
Thats because i pasted a wrong table definition, that one was the old one @P.Salmon — matuco1998, Dec 01 '21 at 16:20
You could try the **CSV Lint** plug-in for Notepad++ https://github.com/BdR76/CSVLint to check any errors in the CSV file. Or use the `Convert to SQL` option and just run that as a script to insert all data. — BdR, Dec 01 '21 at 16:23

P.Salmon · Answer 1 · 2021-12-03T08:45:08.053

As describe in the manual https://dev.mysql.com/doc/refman/8.0/en/load-data.html you can specify map the columns to be loaded

so loading the first 4 rows..

drop table if exists t;
CREATE TABLE t (
  `id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `lastname` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `position` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `mobile_no` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `mobile_no2` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `descriptionMobile` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `descriptionMobile2` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` int(11) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
);
load data infile
'C:\\Program Files\\MariaDB 10.1\\data\\csv.txt'
into table t
columns terminated by ','
optionally enclosed by '"'
lines terminated by '\r\n'
(   `id`,
  `name` ,
  `lastname` ,
  `position` ,
  `mobile_no` ,
  `mobile_no2`,
  `descriptionMobile`,
  `descriptionMobile2`,
  `email`,
  `status` 
 )
;

select * from t;
+----+-------------------------------------------------------------------------------------------+--------------------------+----------+-----------+------------+-------------------+--------------------+------------------------------+--------+------------+------------+
| id | name                                                                                      | lastname                 | position | mobile_no | mobile_no2 | descriptionMobile | descriptionMobile2 | email                        | status | created_at | updated_at |
+----+-------------------------------------------------------------------------------------------+--------------------------+----------+-----------+------------+-------------------+--------------------+------------------------------+--------+------------+------------+
|  1 | 30 de Agosto TV Color - Miguel Angel de Arrieta                                           | de Arrieta               | x        | NULL      | NULL       | Movil             | Movil              | MdeArrieta@30deagosto.com.ar |      1 | NULL       | NULL       |
|  2 | Banda De Frecuencia 4G LTE Banda 4  1700 MHz Banda 7 2600 MHz Banda 28 700 MHz, Celulares | 4G LTE Banda 4  1700 MHz | x        | NULL      | NULL       | Movil             | Movil              | NULL                         |      1 | NULL       | NULL       |
|  3 | A Beneficio CONABIP                                                                       | A                        | x        | NULL      | NULL       | Movil             | Movil              | abenef@conabip.gov.ar        |      1 | NULL       | NULL       |
|  4 | AAATesters                                                                                | AAATesters               | x        | NULL      | NULL       | Movil             | Movil              | orders2@aaatesters.com       |      1 | NULL       | NULL       |
+----+-------------------------------------------------------------------------------------------+--------------------------+----------+-----------+------------+-------------------+--------------------+------------------------------+--------+------------+------------+
4 rows in set (0.00 sec)

Using ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

The first 13 rows do load even with the dubious character in row 12

drop table if exists t;
CREATE TABLE t (
  `id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `lastname` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `position` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `mobile_no` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `mobile_no2` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `descriptionMobile` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `descriptionMobile2` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` int(11) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
load data infile
'C:\\Program Files\\MariaDB 10.1\\data\\csv.txt'
into table t
columns terminated by ','
optionally enclosed by '"'
lines terminated by '\r\n'
(   `id`,
  `name` ,
  `lastname` ,
  `position` ,
  `mobile_no` ,
  `mobile_no2`,
  `descriptionMobile`,
  `descriptionMobile2`,
  `email`,
  `status` 
 );
 
 select * from t;
+----+-------------------------------------------------------------------------------------------+--------------------------+----------+------------+-------------------------------+-------------------+--------------------+------------------------------+--------+------------+------------+
| id | name                                                                                      | lastname                 | position | mobile_no  | mobile_no2                    | descriptionMobile | descriptionMobile2 | email                        | status | created_at | updated_at |
+----+-------------------------------------------------------------------------------------------+--------------------------+----------+------------+-------------------------------+-------------------+--------------------+------------------------------+--------+------------+------------+
|  1 | 30 de Agosto TV Color - Miguel Angel de Arrieta                                           | de Arrieta               | x        | NULL       | NULL                          | Movil             | Movil              | MdeArrieta@30deagosto.com.ar |      1 | NULL       | NULL       |
|  2 | Banda De Frecuencia 4G LTE Banda 4  1700 MHz Banda 7 2600 MHz Banda 28 700 MHz, Celulares | 4G LTE Banda 4  1700 MHz | x        | NULL       | NULL                          | Movil             | Movil              | NULL                         |      1 | NULL       | NULL       |
|  3 | A Beneficio CONABIP                                                                       | A                        | x        | NULL       | NULL                          | Movil             | Movil              | abenef@conabip.gov.ar        |      1 | NULL       | NULL       |
|  4 | AAATesters                                                                                | AAATesters               | x        | NULL       | NULL                          | Movil             | Movil              | orders2@aaatesters.com       |      1 | NULL       | NULL       |
|  5 | Aaron Isaac Gonzalez                                                                      | Aaron Isaac              | x        | 1134432558 | NULL                          | Movil             | Movil              | NULL                         |      1 | NULL       | NULL       |
|  6 | Aaronia AG,Germany                                                                        | Aaronia                  | x        | 4.97E+12   | NULL                          | Movil             | Movil              | mail@aaronia.de              |      1 | NULL       | NULL       |
|  7 | ABA GAS GNC                                                                               | ABA GAS                  | x        | 1122598905 | 11-4572-3900 ::: 11 6382-7811 | Movil             | Movil              | NULL                         |      1 | NULL       | NULL       |
|  8 | abel cortez                                                                               | abel                     | x        | NULL       | NULL                          | Movil             | Movil              | NULL                         |      1 | NULL       | NULL       |
|  9 | Abel Curin                                                                                | Abel                     | x        | NULL       | NULL                          | Movil             | Movil              | NULL                         |      1 | NULL       | NULL       |
| 10 | Abogada John Maria Jauregui                                                               | Abogada John             | x        | NULL       | NULL                          | Movil             | Movil              | abelcor86@yahoo.com.ar       |      1 | NULL       | NULL       |
| 11 | Abogada Silvina Corazza VIAVI                                                             | Abogada Silvina          | x        | 1147536648 | NULL                          | Movil             | Movil              | abelcurin@mymcom.com.ar      |      1 | NULL       | NULL       |
| 12 | Abogado Jose Mï¿½ndez Marcas Y Patentes                                                   | Abogado Jose             | x        | 1147324171 | 11-474-712-44                 | Movil             | Movil              | NULL                         |      1 | NULL       | NULL       |
| 13 | Abogado Multas Infracciones                                                               | Abogado                  | x        | 54108184   | 549108184                     | Movil             | Movil              | scorazza@clarkemodet.com.ar  |      1 | NULL       | NULL       |
+----+-------------------------------------------------------------------------------------------+--------------------------+----------+------------+-------------------------------+-------------------+--------------------+------------------------------+--------+------------+------------+
13 rows in set (0.00 sec)

That's not to say that the csv file does not contain other unhelpful artefacts.

Please augment your example by specifying `CHARACTER SET utf8mb4` -- assuming that the file is encoded UTF-8. — Rick James, Dec 03 '21 at 05:17

Rick James · Answer 2 · 2021-12-03T05:20:54.473

This seems to be already bad:

Abogado Jose M�ndez

See "black diamond" in Trouble with UTF-8 characters; what I see is not what I stored

That link also talks about "truncation", which is another symptom of inconsistent configuration in MySQL UTF-8 data.

If possible, get a hex dump of the word with a mangled character. Also, try to get SELECT HEX(col), col... from the database table. Both of these will help with getting to the bottom of the problem.

It may be useful to use chcp 65001 in the Windows run window.

Why is it that my data is being truncated while uploading an csv file in my database?

2 Answers2