Mysql create table from csv

Question

In order to create a table from csv file , i have followed that link Import CSV File Into MySQL Table I create the table structure , the problem that its values doesn't match with the csv file values for each row. what may be the problem ?

the table structure is :

CREATE TABLE products (

id_p int(11) NOT NULL,

nom varchar(255) NOT NULL,

ref int(11) NOT NULL, price decimal(10,2) NOT NULL,

active int(11) NOT NULL,

shopname varchar(255) NOT NULL,

id_shop_default int(11) NOT NULL,

id_image int(11) NOT NULL,

name_category varchar(255) NOT NULL,

price_final int(11) NOT NULL,

is_virtual int(11) NOT NULL,

downla int(11) NOT NULL,

sav_quantity int(11) NOT NULL,

badge_danger int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

and the csv structure is :

the rasult in the database was :

below are the 10 first rows from the csv when editing by text editor ( may the ,; for exemple in ligne 1 the problem?

id_product;name;reference;price;active;shopname;id_shop_default;id_image;name_category;price_final;is_virtual;nb_downloadable;sav_quantity;badge_danger
1;CORRECTEUR DE PROJECTEUR NEUF... CITROEN PEUGEOT ;14.750000;1;Tracauto1950;1;1; Ampoule,Platine et divers,;0;0 ;1;0
2;ECLAIRAGE DE PLAQUE AR SEIMA 856...PEUGEOT 204 ;29.750000;1;Tracauto1950;1;2; Feu,Cabochon int/ext divers,;0;0 ;1;0
3;RACCORD D'EAU METAL 4 SORTIES...POUR PEUGEOT CITROEN RENAULT SIMCA ;7.750000;1;Tracauto1950;1;3; Bocal, Pompe lave glace,;0;0 ;1;0
4;REFRIGERATEUR DE CIRCUIT D'HUILE...CITROEN 2CV AZAM ;69.750000;1;Tracauto1950;1;4; Radiateurs,Bouchons,Joints,;0;0 ;1;0
5;VERRE DE FEU AR CLIGNOTANT...DS HY GS BX 104 204 304 504 R4 R5 R8 R10 R12... ;12.750000;1;Tracauto1950;1;5; Feu,Cabochon int/ext divers,;0;0 ;1;0
6;ETRIER DE FREIN AVANT GAUCHE  BENDIX...RENAULT R18 ;59.750000;1;Tracauto1950;1;6; Disque,Etrier,Tambour;0;0 ;1;0
7;PARE CHOC ARRIERE COTE GAUCHE...PEUGEOT SIMCA CITROEN RENAULT ;39.750000;1;Tracauto1950;1;7; Parechoc, Butoirs, Butées;0;0 ;1;0
8;T DE RACCORD DE LAVE GLACE...POUR PEUGEOT CITROEN RENAULT SIMCA ;6.750000;1;Tracauto1950;1;8; Bocal, Pompe lave glace,;0;0 ;4;0
9;JOINT METALLO ...RENAULT PEUGEOT CITROEN SIMCA PANHARD ;9.750000;1;Tracauto1950;1;9; Joint,Silentbloc,Accessoires;0;0 ;1;0

)

It would help if you showed us the SQL that creates your table, then a portion of the CSV file and the stored values in the database. Tell us where you think there is a mismatch. Then perhaps we can find the reason. — KIKO Software
– KIKO Software, Commented Mar 29, 2018 at 12:36
i have updated the post by adding table strcuture and the csv file strcuture : it is devided into two parts — Rami bellili
– Rami bellili, Commented Mar 29, 2018 at 13:08

KIKO Software · Accepted Answer · 2018-03-29 13:57:51Z

1

The command to load the CSV in the tutorial is:

LOAD DATA INFILE 'c:/tmp/discounts.csv' 
INTO TABLE discounts 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

I've noticed that your fields are not enclosed by any quotes, so you should have changed the load command to something like:

LOAD DATA INFILE '/dir/dir/your_file.csv' 
INTO TABLE products
FIELDS TERMINATED BY ';' ENCLOSED BY ''  
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

The major change is this bit: FIELDS TERMINATED BY ';' ENCLOSED BY '' And you cannot use ; inside any field. Of course you should have read this:

https://dev.mysql.com/doc/refman/5.7/en/load-data.html

answered Mar 29, 2018 at 13:57

KIKO Software

17.5k3 gold badges33 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mysql create table from csv

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