CODEWITHSUNDEEP
mysqlcsv
Rami bellili
Rami bellili

Reputation: 67

Mysql create table from csv

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 :

part1 part2of csv

the rasult in the database was : enter image description here

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

)

Upvotes: 0

Views: 5460

Answers (1)

KIKO Software
KIKO Software

Reputation: 16751

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

Upvotes: 1

Related Questions