Board index   FAQ   Search  
Register  Login
Board index php forum :: Database mySQL & php coding

Upload a CSV file - very strange behavior

Codes here !

Moderators: macek, egami, gesf

Upload a CSV file - very strange behavior

Postby daswunder » Wed Dec 26, 2012 9:30 am

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
Attachments
UPLOAD_CSV.zip
AAA.csv
AAA_NEW.csv
Calc/OO BBB.csv
csv.png image
(61.85 KiB) Downloaded 35 times
daswunder
New php-forum User
New php-forum User
 
Posts: 2
Joined: Wed Dec 26, 2012 9:16 am

Return to mySQL & php coding

Who is online

Users browsing this forum: No registered users and 1 guest

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.