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

[KN1V3S]

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.";
                            }
            }
?>

 

[Barand]

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?

[KN1V3S]

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.

[Barand]

What is your table's structure?

SHOW CREATE TABLE adressen_neu;

 

Edited September 15, 2021 by Barand

[Phi11W]

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.

 

[KN1V3S]

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

Edited September 15, 2021 by KN1V3S

[KN1V3S]

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

[Barand]

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?

[KN1V3S]

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

[Barand]

Those ... tell us sweet FA (Why is phpmyadmin so useless?).

What about my other question about unique identifiers>

[KN1V3S]

Just now, Barand said:

Those ... tell us sweet FA (Why is phpmyadmin so useless?).

What about my other question about unique identifiers>

I dont understand your question

[Barand]

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 September 15, 2021 by Barand

[KN1V3S]

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?

[Barand]

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 September 15, 2021 by Barand

[KN1V3S]

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!

[KN1V3S]

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.

[Barand]

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

[KN1V3S]

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>

 

[dodgeitorelse3]

Is it really knr. in query?

[KN1V3S]

13 minutes ago, dodgeitorelse3 said:

Is it really knr. in query?

KNr. is the name of the column in the db

[Barand]

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 September 15, 2021 by Barand

[KN1V3S]

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 September 16, 2021 by KN1V3S

[Barand]

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.

[KN1V3S]

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 September 16, 2021 by KN1V3S

[gizmola]

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.