CODEWITHSUNDEEP
phpmysqlformscsv
leoarce
leoarce

Reputation: 549

How do I import a csv with or without headers and allow records to have empty column fields?

I looked at other question and answers already. That's how I got the base codes I'm working with. I modified it to my needs. Basically I'm working with 2 tables. First table creates one record, then second table is all the records of the csv file and it copies the id that was just created in table 1 into the job_id field of table 2.

The thing that doesn't work is if any of the csv rows are not completely filled in with all the columns. the code just inserts into the database the rows that are all filled in and when it hits the first row that has blank columns, it stops running. neither of the 2 versions below works.

Here is version one if the csv doesn't have headers.

if ($_FILES[csv][size] > 0) {

    include_once('db.php');
    //get the csv file
    $file = $_FILES[csv][tmp_name]; 
    $handle = fopen($file,"r");

    $job_name = $_POST['job_name'];
    $file_name = $_FILES[csv][name];
    $date_created = $_POST['date_created'];

    $sql = "INSERT INTO jobs (job_name, file_name, date_created) VALUES ('$job_name','$file_name','$date_created')";
    mysql_query($sql) or die(mysql_error());

    $job_id = mysql_insert_id();

    //loop through the csv file and insert into database
    do {
        if ($data[0]) {
            mysql_query("INSERT INTO people (job_id, company, first, last, address1, address2, city, state, zip, phone, email, tracking_number, year, make, model, image, misc1, misc2, misc3, misc4, misc5, misc6, list) VALUES ( '".$job_id."', '".addslashes($data[0])."', '".addslashes($data[1])."', '".addslashes($data[2])."', '".addslashes($data[3])."', '".addslashes($data[4])."', '".addslashes($data[5])."', '".addslashes($data[6])."', '".addslashes($data[7])."', '".addslashes($data[8])."', '".addslashes($data[9])."', '".addslashes($data[10])."', '".addslashes($data[11])."', '".addslashes($data[12])."', '".addslashes($data[13])."', '".addslashes($data[14])."', '".addslashes($data[15])."', '".addslashes($data[16])."', '".addslashes($data[17])."', '".addslashes($data[18])."', '".addslashes($data[19])."', '".addslashes($data[20])."', '".addslashes($data[21])."') ");
        }
    }
    while ($data = fgetcsv($handle,1000,",","'"));

    //redirect
    header('Location: upload.php?success=1&job_name='.$job_name.'&file_name='.$file_name.'&date_created='.$date_created.'&job_id='.$job_id.'');

    die;

}

Here is version two if the csv has a headers line at the top.

if ($_FILES[csv][size] > 0) {

    include_once('db.php');
    //get the csv file
    $file = $_FILES[csv][tmp_name]; 
    $handle = fopen($file,"r");

    // get headers
    $columns = fgetcsv($handle,1000,",","'");
    $column_list = implode(",",$columns);
    $column_list = "job_id,".$column_list;

    $job_name = $_POST['job_name'];
    $file_name = $_FILES[csv][name];
    $date_created = $_POST['date_created'];

    $sql = "INSERT INTO jobs (job_name, file_name, date_created) VALUES ('$job_name','$file_name','$date_created')";
    mysql_query($sql) or die(mysql_error());

    $job_id = mysql_insert_id();

    //loop through the csv file and insert into database
    while ($data = fgetcsv($handle,1000,",","'")) {
        if ($data[0]) {
            for ($i=0;$i<count($columns);$i++){
                $data[$i]="'".mysql_real_escape_string($data[$i])."'";
            }
            $values = implode(",",$data);
            $values = "'".$job_id."',".$values;
            mysql_query('INSERT INTO people ('.$column_list.') VALUES ('.$values.')');
        }
    }

    //redirect
    header('Location: upload.php?success=1&job_name='.$job_name.'&file_name='.$file_name.'&date_created='.$date_created.'&job_id='.$job_id.'&column_list='.$column_list.'&values='.$values.'');

    die;

}

Update:

still didn't work with that answer.

here is csv with headers line. only the first row after the headers line is making it into the database still. i need all the rows after headers line to make it into database.

company,first,last,address1,address2,city,state,zip,phone,email,tracking_number,year,make,model,image,misc1,misc2,misc3,misc4,misc5,misc6,list
abc corp,john,doe,123 whatever st,suite 2,whatevercity,whateverstate,33333,1234567890,[email protected],yes,2006,toyota,corolla,image.jpg,something,something,something,something,something,something,something
,Jim,Smith,address 1,,city 1,state 1,zip 1,,,,2007,honda,accord,,,,,,,,
,Joe,Tester,address 2,,city 2,state 2,zip 2,,,,2008,nissan,maxima,,,,,,,,

Upvotes: 0

Views: 1488

Answers (1)

Aleksei Matiushkin
Aleksei Matiushkin

Reputation: 121000

You try to access inexisting array key on “truncated” data. Look:

$data = fgetcsv($handle,1000,",","'")

Here the array of exactly N elements was built, where N is the count of values got from CSV. And then you call:

$data[$i]="'".mysql_real_escape_string($data[$i])."'";

for $i in the range [0..columns-1]. But there could be less items in data array. The following is a hot fix:

$data[$i]="'".mysql_real_escape_string( 
  ( array_key_exists( $i, $data ) ? $data[$i] : '' ) 
)."'";

UPD if( $data[0]) prevents rows with empty company name from being operated, because if ('') evaluates to false.

Upvotes: 1

Related Questions