PHP, MySQL - Insert CSV file with header

Question

I have CSV file with header and data fields. How can I insert this via SQL query and remove headers. At the moment code that I wrote is working, but it is inserting header as a data into DB.

The code:

   $files  = directory_map('./assets/csv/');
    foreach ($files as $file) :
        $filefile = './assets/csv/' . $file;
        $q = "LOAD DATA LOCAL INFILE '$filefile' INTO TABLE person FIELDS TERMINATED BY ','  
              LINES TERMINATED BY '\n'  (personal_name, personal_lastname, 
              personal_country,personal_address,contact_email,dateadded);";
        $this->db->query($q);           
    endforeach;

Fluffeh · Accepted Answer · 2014-09-29 12:12:43Z

2

You can use the ignore xx lines clause in load data (the doc on this)[http://dev.mysql.com/doc/refman/5.1/en/load-data.html] skip down about a third of the way:

    $q = "LOAD DATA LOCAL INFILE '$filefile' INTO TABLE person FIELDS TERMINATED BY ','  
          LINES TERMINATED BY '\n' ignore 1 lines (personal_name, personal_lastname, 
          personal_country,personal_address,contact_email,dateadded);";

This will cause the input to skip 1 line of the CSV - which I am assuming is your headers. Skip however many lines are needed to get to the data itself.

edited Sep 29, 2014 at 12:12

answered Sep 29, 2014 at 11:54

Fluffeh

33.6k16 gold badges69 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sasha Over a year ago

I am getting this error: Error Number: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ignore 1 lines' at line 3

Sasha Over a year ago

Found the error - ignore 1 lines need to go after LINES TERMINATED BY '\n'. Thanks for help :)

DeDevelopers · Accepted Answer · 2014-09-29 11:53:49Z

0

Try this: Hope so this will work for you..

$c = 1;
$files  = directory_map('./assets/csv/');

        foreach ($files as $file) :
            if($c==2;) continue;
            $filefile = './assets/csv/' . $file;

            $q = "LOAD DATA LOCAL INFILE '$filefile' INTO TABLE person FIELDS TERMINATED BY ','  LINES TERMINATED BY '\n'  (personal_name, personal_lastname, personal_country,personal_address,contact_email,dateadded);";
            $this->db->query($q);           
$c++;
        endforeach;

answered Sep 29, 2014 at 11:53

DeDevelopers

5911 gold badge7 silver badges26 bronze badges

Collectives™ on Stack Overflow

PHP, MySQL - Insert CSV file with header

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related