Jump to content

Archived

This topic is now archived and is closed to further replies.

steeve_84

Importing .csv row errors

Recommended Posts

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!

Share this post


Link to post
Share on other sites

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.

 

Share this post


Link to post
Share on other sites

 

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?

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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 :/

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

 

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'. :(

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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. 

Share this post


Link to post
Share on other sites

 

 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) . ")";

Share this post


Link to post
Share on other sites

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

 

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.