Jump to content

PHP CSV Upload - Some rows in CSV file aren't in database


KN1V3S
 Share

Recommended Posts

Hello everyone,

as described above, whenever I upload a CSV doc with more than 600 rows of data, some of those rows went missing.
I thought it was maybe the format of the data itself, but that was not the case. I couldn't identify the problem.

Unfortunately I can't share the CSV file since it contains sensitive information.

I need help with writing a specific PHP code that tells me which row of the CSV file couldn't be uploaded into the database.
My current php code is below.


Let me know if you need more information.

Kind regards

Knives

<?PHP
    $con = mysqli_connect("localhost", "root", "","csvupload");

        if(ISSET($_POST['import']))
        {
            $filename = $_FILES["file"]["tmp_name"];

            if(ISSET($_POST['checkbox']))
            {
                $skipRow = 0;
            }
            else
            {
                $skipRow = 1;    
            }
                if($_FILES["file"]["size"] > 0);
                {
                    $file = fopen($filename, "r");

                }
                    while(($column = fgetcsv($file, 10000, ";")) !== FALSE)
                    {
                        $skipRow++;
                            if ($skipRow == 1) { continue; }
                                $sqlInsert = "INSERT INTO `adressen_neu` (`KNr.`, `Anrede`, `Vorname`, `Name`, `Name2`, `Name3`, `Land`, `PLZ`, `Ort`, `Strasse`, `Telefon`, `Telefax`, `Email`) VALUES ('$column[0]', '$column[1]', '$column[2]', '$column[3]', '$column[4]', '$column[5]', '$column[6]', '$column[7]', '$column[8]', '$column[9]', '$column[10]', '$column[11]', '$column[12]')";
                                $result = mysqli_query($con, $sqlInsert);

                    }
                            if(!empty($result))
                            {
                                echo "Ein Fehler ist aufgetreten.";
                            }
                            else
                            {
                                echo "Erfolgreich in die Datenbank eingetragen.";
                            }
            }
?>

 

Link to comment
Share on other sites

14 minutes ago, Barand said:

You are inserting using the slowest method possible.

Are the missing rows always from the end of the CSV file, or is it skipping rows from the middle?

That's what I'm currently trying to find out. But since there are 623 lines, it's a bit difficult to see which ones are missing. I compared the amount of rows in the database and in the original CSV file. In the original CSV file, there are 623 rows of data. In the database, there are 613 entries. That means that 10 rows are gone. Unfortunately I can't find out which ones are not being added into the db.

Link to comment
Share on other sites

1 hour ago, KN1V3S said:

That's what I'm currently trying to find out. But since there are 623 lines, it's a bit difficult to see which ones are missing. I compared the amount of rows in the database and in the original CSV file. In the original CSV file, there are 623 rows of data. In the database, there are 613 entries. That means that 10 rows are gone. Unfortunately I can't find out which ones are not being added into the db.

Pull the data back out of the database and copy it into your favourite spreadsheet program (e.g. MS Excel). 

Then do the same with the data from the original file. 

Put the two lists side by side on a single worksheet, sort the two lists and compare visually. 
It shouldn't take more than a few minutes to find the ones that appear in one list and not the other. 

Regards, 
    Phill  W.

 

Link to comment
Share on other sites

22 minutes ago, Barand said:

What is your table's structure?

SHOW CREATE TABLE adressen_neu;

 

This is my table's structure (I'm still a beginner at working with databases so if there are major mistakes, please tell me!)

screenshot1.png

Edited by KN1V3S
Link to comment
Share on other sites

13 minutes ago, Phi11W said:

Pull the data back out of the database and copy it into your favourite spreadsheet program (e.g. MS Excel). 

Then do the same with the data from the original file. 

Put the two lists side by side on a single worksheet, sort the two lists and compare visually. 
It shouldn't take more than a few minutes to find the ones that appear in one list and not the other. 

Regards, 
    Phill  W.

 

Hi Phill,
thanks for your suggestion, could you please tell me how I can copy all the entries of the database into a worksheet?

Kind regards,
Knives

Link to comment
Share on other sites

I was hoping to see which column was the primary key and if you had any other keys defined on any columns. That was why I requested the the output from SHOW CREATE TABLE.

I can't see anything defined there, so is there anything in those records that uniquly identifies a record?

Link to comment
Share on other sites

4 minutes ago, Barand said:

I was hoping to see which column was the primary key and if you had any other keys defined on any columns. That was why I requested the the output from SHOW CREATE TABLE.

I can't see anything defined there, so is there anything in those records that uniquly identifies a record?

This is the output from SHOW CREATE TABLE adressen_neu

screenshot2.png

Link to comment
Share on other sites

I've just spotted the "key" icon next to KNr ( I was expecting a separated to column to say which columns were keys) so it looks like that is the primary key.

That makes your checking for missing records simple. You just need to see which KNr values are not in the db table but are in the csv file.

Edited by Barand
Link to comment
Share on other sites

1 minute ago, Barand said:

I've just spotted the "key" icon next to KNr ( I was expecting a separated to column to say which columns were keys) so it looks like that is the primary key.

That makes your checking for missing records simple. You just need to see which KNr values are not in the db table but are in the csv file.

Is there a fast method of checking that?

Link to comment
Share on other sites

I'd create and array of KNr's from the CSV and a second array of KNr's from your table then compare the arrays for differencess (array_diff() )

EDIT: Something like this

$array1 = [];
$array2 = [];

$file = fopen($filename, "r");
while (($column = fgetcsv($file, 10000, ";")) !== FALSE)  {
    $array1[] = $column[0];
}

$res = $con->query("SELECT KN2 from adressen_neu");
while ($row = $res->fetch_num() ) {
    $array2[] = $row[0];
}

echo '<pre>' . print_r(array_diff($array1, $array2), 1) . '</pre>';
                    

 

Edited by Barand
Link to comment
Share on other sites

24 minutes ago, Barand said:

I'd create and array of KNr's from the CSV and a second array of KNr's from your table then compare the arrays for differencess (array_diff() )

I know it looks silly, but I implemented "var_dump(count($column) < 1);" to check if there are some arrays that have a value of 0. But it turned out that there was not a single array that had the value 0. Doesn't that mean that there might be issues from the db? Correct me if I'm wrong!

array_values.png

Link to comment
Share on other sites

47 minutes ago, Barand said:

I'd create and array of KNr's from the CSV and a second array of KNr's from your table then compare the arrays for differencess (array_diff() )

EDIT: Something like this

$array1 = [];
$array2 = [];

$file = fopen($filename, "r");
while (($column = fgetcsv($file, 10000, ";")) !== FALSE)  {
    $array1[] = $column[0];
}

$res = $con->query("SELECT KN2 from adressen_neu");
while ($row = $res->fetch_num() ) {
    $array2[] = $row[0];
}

echo '<pre>' . print_r(array_diff($array1, $array2), 1) . '</pre>';
                    

 

It says that fetch_num isn't defined.

error.png

Link to comment
Share on other sites

16 minutes ago, Barand said:

Sorry. Not used to mysqli (I always use PDO). It should be fetch_row();

I'm really sorry for bothering you so much, but now I'm getting this error:
Fatal error: Uncaught Error: Call to a member function fetch_row() on bool in C:\xampp\htdocs\array_comparison.php:19 Stack trace: #0 {main} thrown in C:\xampp\htdocs\array_comparison.php on line 19

<?php
    $con = mysqli_connect("localhost", "root", "","csvupload");

        if(ISSET($_POST['import']))
        {
            $filename = $_FILES["file"]["tmp_name"];


            $array1 = [];
            $array2 = [];

            $file = fopen($filename, "r");
                while (($column = fgetcsv($file, 10000, ";")) !== FALSE)  
                {
                    $array1[] = $column[0];
                }

                        $res = $con->query("SELECT KNr. FROM adressen_neu");
                            while ($row = $res->fetch_row() ) 
                            {
                            $array2[] = $row[0];
                            }

                                echo '<pre>' . print_r(array_diff($array1, $array2), 1) . '</pre>';
        }
?>

<!DOCTYPE html>
<html>
    <head>
        <title>CSV Upload-Page</title>
    </head>
    <body>
        <form class="form" name="uploadCSV" action="array_comparison.php" method="post" enctype="multipart/form-data">
            <div>
                <label>Select CSV File</label>
                <input type="file" name="file" accept=".csv">
                <button type="submit" name="import">Import</button>
                <input type="checkbox" name="checkbox" value="checked">
            </div>
        </form>
    </body>
</html>

 

Link to comment
Share on other sites

If you want to use silly names like that with the "." at the end then you need the column name inside backticks. SELECT `KNr.` FROM ....

 

From MySQL manual

Quote

Permitted characters in unquoted identifiers:

ASCII: [0-9,a-z,A-Z$_] (basic Latin letters, digits 0-9, dollar, underscore)

 

Edited by Barand
  • Like 2
Link to comment
Share on other sites

18 hours ago, Barand said:

If you want to use silly names like that with the "." at the end then you need the column name inside backticks. SELECT `KNr.` FROM ....

 

From MySQL manual

 

Thanks for the advice. I've been able to find out which Arrays haven't been registered into the database. It turned out, every Array with '' in them couldn't be uploaded. The reason was sql query. It couldn't handle these characters since they were already implemented in the code. That's why I've turned the arrays into real escape strings with "mysqli_real_escape_string()". Now everything is working flawlessy. Thanks for your help!

Edited by KN1V3S
Link to comment
Share on other sites

2 hours ago, Barand said:

The correct fix would be to use prepared queries.

CSV files are expected to have "..." around some string values and fgetcsv() should allow for them. Perhaps there is something weird regarding their placement in your data.

True, I've heard about prepared queries but as I said, I really am a beginner and don't have the know-how yet. But I will look into that! Thank you :)

Oh and I meant '...' not "..." 😅

Edited by KN1V3S
Link to comment
Share on other sites

One of the several advantages to using prepared queries is that you don't have to escape special characters.

Since you are using mysqli_ * api:

Quick example from this Mysqli guide, that is well worth reading through...

# replace your php parameters in the VALUES section with '?' for each column you are inserting
$sql = "INSERT INTO users (name, email, password) VALUES (?,?,?)";

# Now prepare the statement.  It is ready for binding.
$stmt= $conn->prepare($sql);

#bind the values.  The first parameter is a type for each column.  's' is for a string.  'i' is for integer.  'd' is for double/float.  'b' is for binary/blobs
$stmt->bind_param("sss", $name, $email, $password);
$stmt->execute();

Another thing that is beneficial is that once you bind variables, you can simply set them to new values and do another execute.  

 

Link to comment
Share on other sites

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.

 Share

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