Importing .csv row errors

[steeve_84]

Hi,

I have a problem with importing .csv file.

The content of file have some rows with invalid culomn number. Can I skip rows with this error?

My code is:

<?php

 

$addauto = 1;

$delimiter = ';';

 

$csoport_kod = new mysqli('localhost', 'root', '', '2012');

$csoport_kod->query("DELETE FROM gf1");

$i=1;

if (($handle = fopen("ftp://2012.com/gf1.csv", "r")) !== FALSE) {

while (($data = fgetcsv($handle, 10000, $delimiter)) !== FALSE) {

foreach($data as $i => $content) {

$data[$i] = $csoport_kod->real_escape_string($content);

}

$csoport_kod->query ("INSERT ignore INTO gf1 VALUES('" . implode("','", $data) . "');");

}

 

fclose($handle);

}

 

echo 'OK' ?>

 

THX!

[objnoob]

Yerp. And, you do it here:

 

while (($data = fgetcsv($handle, 10000, $delimiter)) !== FALSE) {
    if( count($data) !== 10 ) continue;  # if the column count is not 10, continue to the next row;  change 10 to whatever
    foreach($data as $i => $content) {
            $data[$i] = $csoport_kod->real_escape_string($content);
    }
}

If a row doesn't have the same number of columns..... the CSV is not properly formatted.

You should get CSV fixed... fix the problem at the source, if you can.

 

[steeve_84]

 

Yerp. And, you do it here:

 

while (($data = fgetcsv($handle, 10000, $delimiter)) !== FALSE) {
    if( count($data) !== 10 ) continue;  # if the column count is not 10, continue to the next row;  change 10 to whatever
    foreach($data as $i => $content) {
            $data[$i] = $csoport_kod->real_escape_string($content);
    }
}

If a row doesn't have the same number of columns..... the CSV is not properly formatted.

You should get CSV fixed... fix the problem at the source, if you can.

 

 

Thx for your reply,

The source file I can't change...

I make this change, but isnt working... Any other ideas?

[Barand]

Steeve - My car isn't working, can you tell me how to fix it? Or would you need a bit more information on exactly how it isn't working?

[steeve_84]

Steeve - My car isn't working, can you tell me how to fix it? Or would you need a bit more information on exactly how it isn't working?

So.. I changed the code for continue when the column number different from 35, but the data dont writed to table, only the delete query works.

<?php

 

$addauto = 1;

$delimiter = ';';

 

$csoport_kod = new mysqli('localhost', 'root', '', '2012');

$csoport_kod->query("DELETE FROM gf1");

$i=1;

if (($handle = fopen("ftp://2012.com/gf1.csv", "r")) !== FALSE) {

while (($data = fgetcsv($handle, 10000, $delimiter)) !== FALSE) {

if (count($data) !== 35) continue;

foreach($data as $i => $content) {

$data[$i] = $csoport_kod->real_escape_string($content);

}

$csoport_kod->query ("INSERT INTO gf1 VALUES('" . implode("','", $data) . "');");

}

 

fclose($handle);

}

 

echo 'OK' ?>

 

At the source file the first and 4637. rows have different columns. If I delete this rows the script works, but its not automatic :/

Edited November 25, 2013 by steeve_84

[Barand]

You don't want the terminating semi-colon when executing queries from PHP.

 

Also, try error checking on the insert query

$csoport_kod->query ("INSERT INTO gf1 VALUES('" . implode("','", $data) . "')") or die($csoport_kod->error);

[steeve_84]

 

You don't want the terminating semi-colon when executing queries from PHP.

 

Also, try error checking on the insert query

$csoport_kod->query ("INSERT INTO gf1 VALUES('" . implode("','", $data) . "')") or die($csoport_kod->error);

Thanks for reply. I make this change, but nothing happens. No errors, simply writes 'OK'.

[steeve_84]

Hi! I make an another script to import data to my db, but its too wrong...

I don't have any idea to solve this. I can't import the fields because at source file are some rows with invalid number of columns, but the scipt don't skip this rows, only write data to the screen but the insert script isn't work. Help Pls!

<?php
$row = 1;
 $host = "localhost";
 $user = "root";
 $pass = "pass";
 $mydb = "database";
 $table = "gumiflex";
       $link = new mysqli($host,$user,$pass,$mydb);
	   $link->query ("DELETE FROM " .$table. "");
if (($handle = fopen("ftp://user:pass@ftp.hu/data.csv", "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 10000, ";")) !== FALSE) {
        if( count($data) !== 36 ) continue;
		$num = count($data);
        $row++;
        for ($c=2; $c <= $num; $c++) {
			echo $data[$c].";";
            $link->query ("INSERT IGNORE INTO " .$table. "VALUES" .$data[$c]);
        }
    }
    fclose($handle);
}
?>

[Barand]

The secret is to create an INSERT statement with the correct syntax.

[steeve_84]

The secret is to create an INSERT statement with the correct syntax.

I'm so sorry. Changed:

<?php
$row = 1;
 $host = "localhost";
 $user = "root";
 $pass = "pass";
 $mydb = "database";
 $table = "gumiflex";
       $link = new mysqli($host,$user,$pass,$mydb);
	   $link->query ("DELETE FROM " .$table. "");
if (($handle = fopen("ftp://user:pass@ftp.hu/data.csv", "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 10000, ";")) !== FALSE) {
        if( count($data) !== 36 ) continue;
		$num = count($data);
        $row++;
        for ($c=0; $c <= $num; $c++) {
			  $link->query ("INSERT IGNORE INTO " .$table. "VALUES (" . implode("';'", $data) . "") or die ($link->error);
	  }
    }
    fclose($handle);
}
?>

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 '0';'';'';'';'';'';'0';'0';'0';'0';'Engedményekhez';'';'';'LOR';'';'0';'';'0';'';' at line 1

[Barand]

Best to put the query string into variable so you can echo the submitted query when there is an error

 

$sql = "INSERT IGNORE INTO " .$table. "VALUES (" . implode("';'", $data) . "";

 

But it looks like the ) at the end of the VALUES ( is missing

[steeve_84]

Best to put the query string into variable so you can echo the submitted query when there is an error

 

$sql = "INSERT IGNORE INTO " .$table. "VALUES (" . implode("';'", $data) . "";

 

But it looks like the ) at the end of the VALUES ( is missing

 

OK, changed to:

 

$sql="INSERT IGNORE INTO " .$table. "VALUES (" . implode("';'", $data) . ");";

 $link->query ($sql) or die ($link->error);

 

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 '0';'';'';'';'';'';'0';'0';'0';'0';'Engedményekhez';'';'';'LOR';'';'0';'';'0';'';' at line 1

 

If I import the source file at phpmyadmin sql says invalid number of columns.

[Barand]

Then the number of columns in the table doesn't match the number of values being inserted. Specify the columns receiving the data in same order as the values

[steeve_84]

Then the number of columns in the table doesn't match the number of values being inserted. Specify the columns receiving the data in same order as the values

I know it, but I want to skip rows, where doesn't match column numbers. Now this rows are 1. and 6434, but the at next time can it be the 6328. or other. 

[DavidAM]

 

 for ($c=0; $c <= $num; $c++) {
$link->query ("INSERT IGNORE INTO " .$table. "VALUES (" . implode("';'", $data) . "") or die ($link->error);
}

 

You are looping through the 36 columns and trying to insert the entire row each time. If you get the INSERT working, you will be inserting the same data 36 times for every (valid) row in the file.

 

As Barand has said: you are missing the closing parenthesis for the VALUES clause, and you are using a semi-colon instead of a comma between the data elements.

 

If you are going to use IMPLODE (which IS the recommended way to do this), you do NOT need the FOR loop.

 

$sql = "INSERT IGNORE INTO " .$table. "VALUES (" . implode("','", $data) . ")";

[objnoob]

A leading and trailing single quote would help too. See in red below.

 

$sql = "INSERT IGNORE INTO " .$table. "VALUES ('" . implode("','", $data) . "')";

[steeve_84]

Hi!

Thanks for all reply, I solved it.

$link = new mysqli($host,$user,$pass,$mydb);
	   $link->query ("DELETE FROM " .$table. "");
if (($handle = fopen($file, "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 10000, ";")) !== FALSE) {
        if( count($data) !== 36 ) continue;
	$i=0;
	foreach($data as $elem){
	$data[$i] = str_replace("'","",$elem);
	$i++;
	}

$sql = "INSERT IGNORE INTO " .$table. " VALUES ('" . implode("','", $data) . "');";
 // echo $sql."<br \>";
			  $link->query ($sql) or die ($link->error);
    }
    fclose($handle);
}