CODEWITHSUNDEEP
phpcsv
Lloydworth
Lloydworth

Reputation: 763

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

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!

Upvotes: 0

Views: 6971

Answers (3)

Leonard
Leonard

Reputation: 458

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

Upvotes: 3

Dau
Dau

Reputation: 8858

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
}

Upvotes: 1

DerVO
DerVO

Reputation: 3679

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) {
    [...]

Upvotes: 4

Related Questions