CSV import into MySQL truncated - more data than columns

Question

sorry if it seems that it has been answered a lot of times, but spent many hours trying to figure it out. I have a complicated import from CSV into MySQL. My CSV files are generated in different software and changing it would be impossible.

My import code:

LOAD DATA INFILE 'krupka_lezaky.csv'
INTO TABLE table_lezaky_krupka5 
    FIELDS TERMINATED BY ',' 
    ENCLOSED BY '"' 
    LINES TERMINATED BY '\n' 
    IGNORE 1 LINES
    (@var1, @var2, NAZEV, DOPLNEK, @var5, @var6, @var7, @var8, @var9, @var10, @var11, @var12)
SET 
    ID_ZBOZI = REPLACE(@var1, '"', ''), -- Remove double quotes
    ID_CISELNIK = REPLACE(@var2, '"', ''), -- Remove double quotes
    MNOZSTVI = REPLACE(REPLACE(@var5, ',', '.'), '"', ''), -- Remove double quotes and commas
    NCENA_BD = REPLACE(REPLACE(@var6, ',', '.'), '"', ''),
    NCENA_SD = REPLACE(REPLACE(@var7, ',', '.'), '"', ''),
    PCENA_SD = REPLACE(REPLACE(@var8, ',', '.'), '"', ''),
    PCENA_RP = REPLACE(REPLACE(@var9, ',', '.'), '"', ''),
    DAT_PRIJEM = STR_TO_DATE(@var10, '%d.%m.%Y %H:%i:%s'),
    DAT_VYDEJ = STR_TO_DATE(@var11, '%d.%m.%Y %H:%i:%s'),
    KOD_VZP = REPLACE(@var12, '"', ''), -- Remove double quotes
    ID_PROVOZOVNA = 4; -- Set the fixed value for the 13th column

My table format:

My table code:

CREATE TABLE `table_lezaky_krupka5` (
  `ID_ZBOZI` int NOT NULL,
  `ID_CISELNIK` varchar(5) DEFAULT NULL,
  `NAZEV` varchar(200) DEFAULT NULL,
  `DOPLNEK` varchar(200) DEFAULT NULL,
  `MNOZSTVI` decimal(10,4) DEFAULT NULL,
  `NCENA_BD` decimal(10,4) DEFAULT NULL,
  `NCENA_SD` decimal(10,4) DEFAULT NULL,
  `PCENA_SD` decimal(10,4) DEFAULT NULL,
  `PCENA_RP` decimal(10,4) DEFAULT NULL,
  `DAT_PRIJEM` datetime DEFAULT NULL,
  `DAT_VYDEJ` datetime DEFAULT NULL,
  `KOD_VZP` varchar(20) DEFAULT NULL,
  `ID_PROVOZOVNA` int NOT NULL,
  PRIMARY KEY (`ID_ZBOZI`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

My csv file:

"ID_ZBOZI","ID_CISELNIK","NAZEV","DOPLNEK","MNOZSTVI","NCENA_BD","NCENA_SD","PCENA_SD","PCENA_RP","DAT_PRIJEM","DAT_VYDEJ","KOD_VZP"
"11311782","*","5PreveMax Imunit nukleotidy+betaglukan","tbl 60+20","1,0000","238,5000","274,2800","365,0000","365,0000","21.07.2022 07:34:57","14.07.2022 14:32:58",""
"15100001","1","ACYLCOFFIN","POR TBL NOB 10","6,0000","307,0100","337,7300","470,0000","470,0000","20.07.2022 08:08:38","10.02.2023 11:07:09","9"
"15103351","1","AERIUS 5 MG","TBL 30X5MG","1,0000","109,9100","120,9000","160,0000","160,0000","27.07.2022 07:55:40","26.07.2022 07:55:05","26329"
"15144399","1","AESCULUS HIPPOCASTANUM","GRA 4GM 5CH","2,0000","148,3200","163,1600","230,0000","230,0000","06.01.2021 08:30:50","","62741"
"15100094","1","AGOLUTIN","INJ 5X2ML/60MG","1,0000","55,7400","61,3100","70,0000","70,0000","11.02.2022 08:13:57","07.02.2022 14:58:29","186149"
"15103137","*","Alpa bylinný masážní gel kaštan ","100ml","1,0000","35,5700","43,0400","65,0000","65,0000","23.12.2022 10:15:38","22.12.2022 09:14:17",""
"15102019","*","Alpa emulze s arnikou ","100ml","2,0000","81,1200","98,1600","138,0000","138,0000","15.02.2023 07:51:23","14.02.2023 10:51:55",""
"15104744","1","AMPRILAN 5","TBL 30X5MG","1,0000","23,6000","25,9600","34,0000","34,0000","23.12.2022 10:15:25","22.12.2022 16:29:00","23962"
"15120270","1","ANALERGIN NEO","5MG TBL FLM 90 II","1,0000","195,7900","215,3700","283,0000","283,0000","23.09.2022 06:48:38","22.09.2022 11:23:23","201946"

Error:

Error Code: 1262. Row 1 was truncated; it contained more data than there were input columns

I guess my problem is formatting of the CSV file, we use commas as decimal separator, then my decimal values get double quotes as well. I tried to get around it, used replacements to get the right format, but still getting error. Any help would be much appreciated, thank you.

You don't need REPLACE(@var1, '"', ''). When you say ENCLOSED BY '"' the quotes will be removed automatically. — Barmar
– Barmar, Commented Aug 24, 2023 at 23:03
What do you mean by "decimal values get double parentheses"? Parentheses are (). Did you mean extra quotes? — Barmar
– Barmar, Commented Aug 24, 2023 at 23:04
Please post the CSV file as text, not a screenshot, so we can copy and paste. — Barmar
– Barmar, Commented Aug 24, 2023 at 23:06
Because the screenshot looks like it has the correct number of fields. — Barmar
– Barmar, Commented Aug 24, 2023 at 23:50
I am sorry for confusion, i meant double quotes "", I edited the original post — Tireur
– Tireur, Commented Aug 25, 2023 at 4:38

Tireur · Accepted Answer · 2023-08-25 13:43:51Z

Thanks guys for trying to help, I think I finally found a solution. There were two problems,

some of dates in the CSV file were null, so the str_to_date resulted in error, making a condition there helped (at least that is the reason that I think)
I resaved the CSV file in UTF-8 coding, it seems that the original file was in ANSI coding

my code is this now:

LOAD DATA INFILE 'hranik_lezaky.csv'
INTO TABLE table_lezaky_hranik
CHARACTER SET latin1 -- Assuming ANSI encoding is similar to ISO 8859-1 (Latin-1)
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n' 
IGNORE 1 LINES
(@var1, @var2, NAZEV, DOPLNEK, @var5, @var6, @var7, @var8, @var9, @var10, @var11, @var12)
SET 
    ID_ZBOZI = @var1,
    ID_CISELNIK = @var2,
    MNOZSTVI = REPLACE(@var5, ',', '.'),
    NCENA_BD = REPLACE(@var6, ',', '.'),
    NCENA_SD = REPLACE(@var7, ',', '.'),
    PCENA_SD = REPLACE(@var8, ',', '.'),
    PCENA_RP = REPLACE(@var9, ',', '.'),
    DAT_PRIJEM = IF(@var11 <> '', STR_TO_DATE(@var11, '%d.%m.%Y %H:%i:%s'), NULL),
    DAT_VYDEJ = IF(@var11 <> '', STR_TO_DATE(@var11, '%d.%m.%Y %H:%i:%s'), NULL),
    KOD_VZP = @var12,
    ID_PROVOZOVNA = 1; -- Set the fixed value for the 13th column

finally, I managed to import the data, now I will try to change the coding of imported CSV file in this code

EDIT// DONE, thanks :)

Collectives™ on Stack Overflow

CSV import into MySQL truncated - more data than columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related