Save CSV content into mysql database

Question

I'm having a problem with insert csv into database. here's my csv content sample:

gender,age,name,location
male,21,jayvz,spl
female,21,jacyn,srl

and here's the table structure of the table:

• id (auto increment)
• contact_id
• label
• value

what I need to do is insert the data from csv to that table like this:
expected output:

id | contact_id | label    | value
1  |     1      | gender   | male
2  |     1      | age      | 21
3  |     1      | name     | jayvz
4  |     1      | location | spl
5  |     2      | gender   | female
6  |     2      | age      | 21
7  |     2      | name     | jacyn
8  |     2      | location | srl

(sorry for the expected output)

any ideas? or is this even possible?

CODE:

$headers = exec("head -n 1 {$paramfile}");
$param_names = array_filter(explode(",", $headers));
$param_count = count($param_names);

$contact_count = count($contacts);

$contactsfile = getcwd()."/uploads/".date('ymdhis').".cntctid";
$row = '';
$str_csv = array();

$tmp_handler = fopen($datafile, 'r');
$param_value = '';

while(($upload_str = fgets($tmp_handler, 4096)) !== FALSE){
    $param_value = explode(",", $upload_str);
    array_shift($param_value); // msisdn bb
    $str_line = implode(",", $param_value);
    //$age = array_shift($param_value);

    //$row.= implode(",", $param_value);
    //for($x = 0; $x < count($param_value); ++$x){
    //    $row.= $param_value[$x].",";
    //}

    $str_csv[] = str_getcsv($str_line, ",");
}
for($a = 0; $a < $param_count; ++$a){
for($i = 0; $i < $contact_count; ++$i){
$row = $contacts[$i].",".$param_names[$a].",'<the values should be here>'";
exec("echo '".$row."' >> ".$contactsfile);
}
}

$load_query = "LOAD DATA LOCAL INFILE '{$contactsfile}' INTO TABLE {$this->contact_details} ";
$load_query .= "FIELDS TERMINATED BY ',' LINES TERMINATED BY '\\n' ";
$load_query .= "(contact_id, label_name, label_value)";

$this->db->query($load_query);

return $this->db->affected_rows() > 0;

Answer 1

this problem is solved, I wrote the data into a csv file in this format

1,age,21 1,name,jayvz 1,location,spl 2,age,21 2,name,jacyn 2,location,srl

then I used the mysql's load local infile query to prevent the memory from exhausting and as for very large files I divided it into multiple files as possible using bash script's 'split'

thanks guys for the ideas. =)

Answer 2

Why not use php to parse it for you? More control, less worries IMO.

To do so you would split it into lines, and then the lines into fields. The following assumes that you have the CSV available in a string variable, and that the CSV was created on the same system that you want to parse it with (due to my use of the PHP_EOL constant)

$csv 

$lines = explode(PHP_EOL, $csv);

$load_query = "INSERT INTO table_name (contact, label, value) VALUES ";

for($i = 1 ; $i < count($lines) ; $i++){
    // Split the line into fields
    $fields = $explode(",", $lines[$i]);
    // Concatenate the insert values
    $load_query .= "({$field[0]},{$field[1]},{$field[2]})"; 
    // If there are more lines add a comma
    if($i < (count($lines)-1)){
        $load_query .= ",";
    }
}
// Run the query and return
$this->db->query($load_query);
return $this->db->affected_rows() > 0;

These fields should really be sanitized or something though.

Answer 3

Try this:

$row = 1;
$labels = array();
if (($handle = fopen("your.csv", "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        if($row==1){
           $labels = $data;
        } else {
           foreach ($data as $key=>$value) {
              $query = "insert into table values (null,$row,'{$labels[$key]}','$value')";
              $this->db->query($query)
           }
        }
        $row++;
    }
    fclose($handle);
}