Dear all,
I've got a problem trying to import a csv file (AAA.csv).
If I open it using CALC/OO and then save it again (BBB.csv) I'm able to upload normally to mySQL.
If I try to manipulate first the file using PHP and SQL and then try to upload it (AAA_NEW.csv) to mySQL no errors appears but no records are uploaded.
1. Original file AAA.csv has some field that use as encloser =" instead of ". (this is for excel leading 0 problem).
2. I substitute =" with " and write a AAA_NEW.csv
3. Try to upload it and nothing happens.
Attached also the BBB.csv file that works correctly
See also the attached picture that show differences between three files.
Substitution code:
$filename = "D:\Z_ASSOCIAZIONI\UPLOAD CSV\AAA.csv";
$filename1 = explode( "." , $filename ) ;
$filename_new = $filename1[0] . "_NEW.CSV" ;
echo $filename_new;
$data = file_get_contents($filename);
$data = str_replace("=\"","\"", $data);
file_put_contents($filename_new, $data);
MYSQL Upload
load data local infile '$filename'
INTO TABLE members_file_fmi_input FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(Cod_Meccanografico ,
Tessera , Nuo_Rin , Cognome , Nome , Sesso , Data_Nascita , Luogo_Nascita , Indirizzo , Cap , Citta , Provincia ,
Data_Rilascio , Cod_MC , Motoclub , Telefono , Cellulare , Email , Cittadinanza , Residenza , Dirigente,
year_card_actual)
SET Data_Nascita = str_to_date(Data_Nascita, '%d%m%Y'),
Data_Rilascio = str_to_date(Data_Rilascio, '%d%m%Y'),
Motoclub = trim(Motoclub),
Dirigente = IF (Dirigente <> '', Dirigente, NULL) ,
year_card_actual = LEFT (Data_Rilascio, 4);
Attached:
AAA.csv
AAA_NEW.csv
Calc/OO BBB.csv
csv.png image
