0

I want to upload a CSV file into my SQL database but I am getting an Undefined offset error. (Offset: 1-20). I followed the following tutorial in combination with W3schools but I don't know how to solve the error. I hope you guys can point me in the right direction. Thanks in advance.

<?php
// Load the database configuration file
include_once 'dbConfig.php';

if(isset($_POST['importSubmit'])){
    
    // Allowed mime types
    $csvMimes = array('text/x-comma-separated-values', 'text/comma-separated-values', 'application/octet-stream', 'application/vnd.ms-excel', 'application/x-csv', 'text/x-csv', 'text/csv', 'application/csv', 'application/excel', 'application/vnd.msexcel', 'text/plain');
    
    // Validate whether selected file is a CSV file
    if(!empty($_FILES['file']['name']) && in_array($_FILES['file']['type'], $csvMimes)){
        
        // If the file is uploaded
        if(is_uploaded_file($_FILES['file']['tmp_name'])){
            
            // Open uploaded CSV file with read-only mode
            $csvFile = fopen($_FILES['file']['tmp_name'], 'r');
            
            // Skip the first line
            fgetcsv($csvFile);
            
            // Parse data from CSV file line by line
            while(($line = fgetcsv($csvFile)) !== FALSE){
                // Get row data
                $postcode   = $line[0];
                $week1  = $line[1];
                $week2  = $line[2];
                $week3  = $line[3];
                $week4  = $line[4];
                $week5  = $line[5];
                $week6  = $line[6];
                $week7  = $line[7];
                $week8  = $line[8];
                $week9  = $line[19];
                $week10  = $line[10];
                $week11  = $line[11];
                $week12  = $line[12];
                $week13  = $line[13];
                $week14  = $line[14];
                $week15  = $line[15];
                $week16  = $line[16];
                $week17  = $line[17];
                $week18  = $line[18];
                $week19  = $line[19];
                $week20  = $line[20];
                
                $db->query("INSERT INTO nlbelevering 
                        (Postcode, Week1, Week2, Week3, Week4, Week5, Week6, 
                        Week7, Week8, Week9, Week10, Week11, Week12, Week13, 
                        Week14, Week15, Week16, Week17, Week18, Week19, 
                        Week20) 
                VALUES ('".$postcode."', '".$week1."', '".$week2."', 
                        '".$week3."', '".$week4."', '".$week5."', 
                        '".$week6."', '".$week7."', '".$week8."', 
                        '".$week9."', '".$week10."', '".$week11."', 
                        '".$week12."', '".$week13."', '".$week14."', 
                        '".$week15."', '".$week16."', '".$week17."', 
                        '".$week18."', '".$week19."', '".$week20."'");
            }
        }
            
        // Close opened CSV file
        fclose($csvFile);

        $qstring = '?status=succ';
    }else{
        $qstring = '?status=err';
    }
}else{
    $qstring = '?status=invalid_file';
}

// Redirect to the listing page
header("Location: index.php".$qstring);

The error message:

error

EDIT: After using the solution of RiggsFolly I can import the CSV to SQL. But it only imports the first column.

PHPMyAdmin PHPMyAdmin

CSV File CSV File

6
  • Your script is open to SQL Injection Attack. Even if you are escaping inputs, its not safe! You should consider using prepared parameterized statements in either the MYSQLI_ or PDO API's instead of concatenated values Commented Jul 22, 2020 at 10:27
  • PLEASE Always show us the complete error message if you have one and not a summary which misses out relevant information Commented Jul 22, 2020 at 10:28
  • Sometimes a blank line at the end of the file can give a row with 0 data. So you can check if $line has the right number of elements before processing. Commented Jul 22, 2020 at 10:30
  • Do you get these errors on every line, or a specific line of the CSV, which woudl indicate a blank line or a line that is breaking the fgetcsv() Commented Jul 22, 2020 at 10:35
  • @RiggsFolly Thank you for your tip. It's a csv with over 18k rows so I didn't check them all but from what I saw it was randomly. It starts with offset 1-20 then to 8,9 etc. Commented Jul 22, 2020 at 11:46

2 Answers 2

1

Sounds like the input file has some lines that dont quite fit.

You may need to do some checks before the insert like

$line_no = 1;

// Parse data from CSV file line by line
while(($line = fgetcsv($csvFile)) !== FALSE){
    $line_no++;
    if ( count($line) != 21 ) {  
        echo "$line_no needs to be looked at";
        continue;       // go to next iteration of the loop
    }
    // Get row data
    $postcode   = $line[0];
    $week1  = $line[1];
    $week2  = $line[2];
    $week3  = $line[3];
    $week4  = $line[4];
    $week5  = $line[5];
    $week6  = $line[6];
    $week7  = $line[7];
    $week8  = $line[8];
    $week9  = $line[19];
    $week10  = $line[10];
    $week11  = $line[11];
    $week12  = $line[12];
    $week13  = $line[13];
    $week14  = $line[14];
    $week15  = $line[15];
    $week16  = $line[16];
    $week17  = $line[17];
    $week18  = $line[18];
    $week19  = $line[19];
    $week20  = $line[20];
    
    $db->query("INSERT INTO nlbelevering 
            (Postcode, Week1, Week2, Week3, Week4, Week5, Week6, 
            Week7, Week8, Week9, Week10, Week11, Week12, Week13, 
            Week14, Week15, Week16, Week17, Week18, Week19, 
            Week20) 
    VALUES ('".$postcode."', '".$week1."', '".$week2."', 
            '".$week3."', '".$week4."', '".$week5."', 
            '".$week6."', '".$week7."', '".$week8."', 
            '".$week9."', '".$week10."', '".$week11."', 
            '".$week12."', '".$week13."', '".$week14."', 
            '".$week15."', '".$week16."', '".$week17."', 
            '".$week18."', '".$week19."', '".$week20."'");
    }
}

Of course you may consider checking all the lines in a pre parse of the file, and rejecting the whole file if any line turns out to have less than 21 columns. This all depends on the data, and if it makes sense to have a few lines missing from your database, or not.

BIG NOTE Your script is open to SQL Injection Attack. Even if you are escaping inputs, its not safe! You should consider using prepared parameterized statements in either the MYSQLI_ or PDO API's instead of concatenated values

Not only would prepared statements avoid the SQL Injections issues you could speed up the processing with a prepared statement as you only have to prepare (send to the database, compile and optimise) the query once instead of once per row of your input.

// prepare once outside the loop
$stmt = $db->prepare(
            "INSERT INTO nlbelevering 
                    (Postcode, Week1, Week2, Week3, Week4, Week5, Week6, 
                    Week7, Week8, Week9, Week10, Week11, Week12, Week13, 
                    Week14, Week15, Week16, Week17, Week18, Week19, Week20) 
            VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
            );

while(($line = fgetcsv($csvFile)) !== FALSE){

    // bind new values each time round the loop and execute the query
    $stmt->bind_param('sssssssssssssssssssss',
                    $line[0], $line[1], $line[2], $line[3],
                    $line[4], $line[5], $line[6], $line[7],
                    $line[8], $line[19], $line[10], $line[11],
                    $line[12], $line[13], $line[14], $line[15],
                    $line[16], $line[17], $line[18], $line[19],line[20]
                );
    $stmt->execute();
}

UPDATE After seeing the CSV file.

CSV stands for "Comma Seperated Values" and therefore fgetcsv() assumes a file like

aaa,bbb,ccc,ddd,.......

If you have a "Semi Colon Seperated Value" file, you have to tell fgetcsv() to expect a different selerator, so from seeing you file change your code to use

// parmeter 2 need to be there to use param 3, 
// pick a number larger than any of the line, lenght or Newline will 
// denote a line.

// parameter 3 says to expect `;` as the seperator instead of a `,`

fgetcsv($csvFile, 1000, ";");
Sign up to request clarification or add additional context in comments.

4 Comments

Thank you for answer. Only the first column (Postcode) gets imported completely. A part of the second column will be placed in the first column aswell and all the other colums stays empty. I placed some screenshots of how it looks into the post.
Ok, your field seperator is not a comma which is what fgetcsv() expects by default, see UPDATE in answer
Thank you for your answer! It resolved the issue. I posted my updated code in the question for those who got the same issue.
Hi, dont post answers in questions. Thats what the Answers are for
1

When accessing array elements via indexes in php you should always check if that index actually exists. Otherwise you will get Undefined offset notice. You should do that for every element:

$postcode = isset($line[0]) ? strval($line[0]) : '';
$week1 = isset($line[1]) ? strval($line[1]) : '';
...

This way you also define default value if key does not exists (in my example it's empty string '').

2 Comments

Thank you for your answer. I tried this and the error did not show up but nothing got imported to SQL.
update question then, the quesiton was regarding undefined offset in array.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.