CODEWITHSUNDEEP
phpmysqlcsv
Manasa
Manasa

Reputation: 187

mysql Load data local infile for one csv fields terminated by , and for another csv field terminated by ;

I already written a query i have posted here but that is working with similar kinds of csv.I need a load data local infile query which will work with all kinds of csv file.Please look into it.

The below query works with all kinds of csv files in which fields separated by ,. example csv

1,114300,1790,2,2,2,No,East
2,114200,2030,4,2,3,No,East
3,114800,1740,3,2,1,No,East
4,94700,1980,3,2,3,No,East

Query looks like this: 

 LOAD DATA    LOCAL  INFILE '$pathname'
              IGNORE INTO TABLE $name
              FIELDS TERMINATED BY ','
              OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '\"'
              LINES TERMINATED BY '\\n'
              IGNORE 1 LINES"

Another csv is like this

Agfa ePhoto 1280;1997;1024.0;640.0;0.0;38.0;114.0;70.0;40.0;4.0;420.0;95.0;179.0
Agfa ePhoto 1680;1998;1280.0;640.0;1.0;38.0;114.0;50.0;0.0;4.0;420.0;158.0;179.0
Agfa ePhoto CL18;2000;640.0;0.0;0.0;45.0;45.0;0.0;0.0;2.0;0.0;0.0;179.0
Agfa ePhoto CL30;1999;1152.0;640.0;0.0;35.0;35.0;0.0;0.0;4.0;0.0;0.0;269.0
Agfa ePhoto CL30 Clik!;1999;1152.0;640.0;0.0;43.0;43.0;50.0;0.0;40.0;300.0;128.0;1299.0

In the query i just changed Fields terminated by ',' to ';'.It works for me.I need everything to be done by in a single query.I am looking for help.

Upvotes: 2

Views: 428

Answers (1)

Manasa
Manasa

Reputation: 187

I extracted the csv filename and its path and stored it in a variable named $filename.

$file = new SplFileObject($filename);//it holds the name and location means path to csv
$file->seek(0);//It holds the first line of csv
if (strpos($file, ';') == true){

$field = "FIELDS TERMINATED BY ';'";
 }
if (strpos($file, ',') == true){
 $field = "FIELDS TERMINATED BY ','";
 }

Then edit the query like this

LOAD DATA    LOCAL  INFILE '$pathname'
              IGNORE INTO TABLE $name
              $field
              OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '\"'
              LINES TERMINATED BY '\\n'
              IGNORE 1 LINES"

It works for all the csv files which fields end by , and ;.

Upvotes: 1

Related Questions