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

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?

CodePudding user response：

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)

CodePudding user response：

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.