Jump to content

Importing .csv row errors


steeve_84
Go to solution Solved by steeve_84,

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!
Link to comment
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.

 

Link to comment
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?

Link to comment
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 :/
Edited by steeve_84
Link to comment
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'. :(

Link to comment
Share on other sites

  • 2 weeks later...

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

Link to comment
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.
Link to comment
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. 

Link to comment
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) . ")";
Link to comment
Share on other sites

  • Solution

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);
}
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • 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.