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;

possible duplicate of [How I can Import data from CSV to MySQL?](http://stackoverflow.com/questions/9555370/how-i-can-import-data-from-csv-to-mysql) — randak, Feb 19 '14 at 10:16
Possible is almost everything. Please post your code so far. — Axel Amthor, Feb 19 '14 at 10:17
@randak - yeah this is exactly what i've been doing so far, but the difference is that, I wanted to put the headers (gender, age...) as values for 'label' column — Jayvz Olazo, Feb 19 '14 at 10:18
Why would you want a table like that? You can't get useful information from it. Normalize! — dar7yl, Feb 19 '14 at 11:08
@h.coates you're right, im working on a profiling system and no one can tell how many user defined fields will be there, they may include one or more or nothing — Jayvz Olazo, Feb 20 '14 at 05:26

score 2 · Answer 1 · answered Feb 19 '14 at 10:36

2

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);
}

answered Feb 19 '14 at 10:36

HarryFink

1,010
1
6
6

One insert for each lineis an approach that works for huge CSV files too, but if you that the CSV data will always be small enough to fit into memory, I would set up a values array and insert once after that loop. – feeela Feb 19 '14 at 11:22
I will use a very large amount of data for a csv file (eg. 500k upto 1m lines or more) – Jayvz Olazo Feb 20 '14 at 03:04
thanks @HarryFink for your idea. however I need to execute the algorithm without exhausting my memory – Jayvz Olazo Feb 20 '14 at 05:28

Digital Fu · Answer 2 · 2014-02-19T10:55:44.877

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.

score 0 · Answer 3 · answered Feb 20 '14 at 08:15

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. =)

Save CSV content into mysql database

3 Answers3