PHP - Skip the first row of csv when importing to MySQL Database

Question

These codes import the contents of a CSV file to the database.

$databasehost = "localhost";
$databasename = "test";
$databasetable = "sample";
$databaseusername ="test";
$databasepassword = "";
$fieldseparator = ",";
$lineseparator = "\n";
$csvfile = "filename.csv";
$addauto = 0;
$save = 1;
$outputfile = "output.sql";

if(!file_exists($csvfile)) {
    echo "File not found. Make sure you specified the correct path.\n";
    exit;
}

$file = fopen($csvfile,"r");

if(!$file) {
    echo "Error opening data file.\n";
    exit;
}

$size = filesize($csvfile);

if(!$size) {
    echo "File is empty.\n";
    exit;
}

$csvcontent = fread($file,$size);

fclose($file);

$con = @mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());
@mysql_select_db($databasename) or die(mysql_error());

$lines = 0;
$queries = "";
$linearray = array();

foreach(split($lineseparator,$csvcontent) as $line) {

    $lines++;

    $line = trim($line," \t");

    $line = str_replace("\r","",$line);

    $line = str_replace("'","\'",$line);

    $linearray = explode($fieldseparator,$line);

    $linemysql = implode("','",$linearray);

    if($addauto)
        $query = "insert into $databasetable values('','$linemysql');";
    else
        $query = "insert into $databasetable values('$linemysql');";

    $queries .= $query . "\n";

    @mysql_query($query);
}

@mysql_close($con);

if($save) {

    if(!is_writable($outputfile)) {
        echo "File is not writable, check permissions.\n";
    }
    else {
        $file2 = fopen($outputfile,"w");

        if(!$file2) {
            echo "Error writing to the output file.\n";
        }
        else {
            fwrite($file2,$queries);
            fclose($file2);
        }
    }

}

echo "Found a total of $lines records in this csv file.\n";

This is how it works.
The administrator will receive an excel spreadsheet (.xls) file and will save it as .csv file. And the first row of the file will be the 'headers' of the excel spreadsheet.
Example: Name, Email, Contact number, Country, Address headers.

So before importing the contents of the CSV file to the database, i want the first row to be skipped. Which means that the importing of contents will start at the second row of the CSV file.

May i know which part of codes should i edit and what should i edit it to?
Sorry, as i'm new to programming PHP.

Thanks in advance!

Please stop using the outdated mysql_ family of functions when writing new code. Look into mysqli or PDO. Also, please use the @ error silencing operator sparingly. It may be hiding errors in your code. — Charles
– Charles, Commented Jan 20, 2012 at 9:10

DerVO · Accepted Answer · 2012-01-20 09:09:02Z

4

A little change will to the job. Before your foreach loop split your $csvcontent and safe the resulting array to $allLines. Then use array_shift to remove the 1st element of this array (the function also returns this element, but we don’t need it here):

$allLines = split($lineseparator,$csvcontent);
array_shift($allLines); // removes the 1st element

foreach($allLines as $line) {
    [...]

edited Jan 20, 2012 at 9:09

answered Jan 20, 2012 at 9:03

DerVO

3,6791 gold badge27 silver badges27 bronze badges

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

2 Comments

zrvan Over a year ago

+1, nice solution, but you can with good conscience shorten it to (using the original code): foreach(array_shift(split($lineseparator,$csvcontent)) as $line) {

DerVO Over a year ago

@zrvan: Your suggestion will not work, because array_shift returns the 1st element, not the shortened array.

Leonard · Accepted Answer · 2012-01-20 09:25:22Z

3

You are already working with a loop for each line. Just skip the first iteration:

$lines = 0;

foreach(split($lineseparator,$csvcontent) as $line) {
    $lines++;

    if($lines > 1){
        $line = trim($line," \t");
        $line = str_replace("\r","",$line);
        $line = str_replace("'","\'",$line);

        $linearray = explode($fieldseparator,$line);

        $linemysql = implode("','",$linearray);

        if($addauto)
            $query = "insert into $databasetable values('','$linemysql');";
        else
            $query = "insert into $databasetable values('$linemysql');";

        $queries .= $query . "\n";

        @mysql_query($query);
    }
}

edited Jan 20, 2012 at 9:25

answered Jan 20, 2012 at 9:06

Leonard

4501 gold badge7 silver badges20 bronze badges

3 Comments

zrvan Over a year ago

I spot a slight problem; if($lines > 0){ will, in the current form, always evaluate to true.

Leonard Over a year ago

You're absolutely right. I changed it into if($lines > 1){, because with the first row, the variable will be incremented exactly one time. Thanks.

Andy Pieters Over a year ago

Hmm i edited at the same time, but i moved the lines++; to after the if statement.

Dau · Accepted Answer · 2012-01-20 09:11:36Z

1

use this

$line_array = explode($csvcontent,$lineseparator);
$total_line = count($line_array);
for ($i=1;$i<$total_line;$i++){
    echo $line_array[$i] //it is your line play with this
}

answered Jan 20, 2012 at 9:11

Dau

8,9204 gold badges25 silver badges49 bronze badges

Collectives™ on Stack Overflow

PHP - Skip the first row of csv when importing to MySQL Database

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related