matuco1998
Reputation: 53
Why is it that my data is being truncated while uploading an csv file in my database?
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,[email protected],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,[email protected],1
4,AAATesters,AAATesters,x,NULL,NULL,Movil,Movil,[email protected],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,[email protected],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,[email protected],1
11,Abogada Silvina Corazza VIAVI,Abogada Silvina,x,1147536648,NULL,Movil,Movil,[email protected],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,[email protected],1
14,Abonados vtcc,Abonados,x,1150084093,NULL,Movil,Movil,[email protected],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,[email protected],1
17,Acontece SRL,Acontece SRL,x,NULL,NULL,Movil,Movil,[email protected],1
18,Adan Solian,Adan,x,1144509164,(011) 4458-3194,Movil,Movil,[email protected],1
19,Adela Liebhardt,Adela,x,NULL,NULL,Movil,Movil,[email protected],1
20,Adema,Adema,x,NULL,NULL,Movil,Movil,[email protected],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?
Upvotes: 0
Views: 1046
Answers (2)
P.Salmon
Reputation: 17655
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 | [email protected] | 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 | [email protected] | 1 | NULL | NULL |
| 4 | AAATesters | AAATesters | x | NULL | NULL | Movil | Movil | [email protected] | 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 | [email protected] | 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 | [email protected] | 1 | NULL | NULL |
| 4 | AAATesters | AAATesters | x | NULL | NULL | Movil | Movil | [email protected] | 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 | [email protected] | 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 | [email protected] | 1 | NULL | NULL |
| 11 | Abogada Silvina Corazza VIAVI | Abogada Silvina | x | 1147536648 | NULL | Movil | Movil | [email protected] | 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 | [email protected] | 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.
Upvotes: 1
Rick James
Reputation: 142346
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.
Upvotes: 1
Related Questions
- MySQL "ERROR 1262 (01000): Row 1 was truncated; it contained more data than there were input columns" why is this?
- mysql [Row 1 was truncated; it contained more data than there were input columns] ERROR
- Getting a "data truncated for column" warning in mysql
- How to solve Data truncated?
- Data truncated when trying to import CSV file
- large csv file not uploaded to database
- mysql> error 1265: data truncated for column 'xxx' at row 1
- Importing CSV into database, insert without truncating table
- MySQL import csv only gets half the lines
- MySql import CSV space problem