Import, analyze and extract data from a PHP CSV

Question

How can I import a csv file with PHP and take the data from a specific column and write that data to the database? You can do this analysis and only record specific data (like a column, or row)?

Answer 1

To perform the process of reading a csv file in php is quite simple, since it is understood as a simple txt file that performs its separation using the ;, then you could use functions like fopen to open it, go through and save in a variable.

Example Reading:

function getCSV($name) {
   $file = fopen($name, "r");
   $result = array();
   $i = 0;
   while (!feof($file)):
      if (substr(($result[$i] = fgets($file)), 0, 10) !== ';;;;;;;;') :
         $i++;
      endif;
   endwhile;
   fclose($file);
   return $result;
}

$foo = getCSV('foo.csv');

Normal reading right ? I just added a detail, that if inside the loop is to prevent a bugle that happens in some versions of excel, this and, when you scroll in the columns, these white columns receive the ;;;; without exactly having a value. At the end you end up saving an array with "garbage".

Now that you have an array with everything! you can pick up line by line by performing a simple explode.

Example Reading line by line:

function getLine($array, $index) {
   return explode(';', $array[$index]);
}

var_dump(getLine($foo, 0));

So if I have a spreadsheet like this:

Calling the function will have an array like this:

And to read field to field of this line you can save in a variable and access the specific index:

$bar = getLine($foo, 0);
var_dump($bar[0]);

In this case by taking the first column, you can automate by performing a loop to go through recording field by field.

An example of generic insertion could be:

$con = mysqli_connect("localhost", "my_user", "my_password", "my_db");
if (mysqli_connect_errno()) {
   echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
mysqli_query($con, 'INSERT INTO Persons (foo,bar) VALUES (' . $bar[0] . ',' . $bar[1] . ')');
mysqli_close($con);

There is also the possibility to read this file in javascript. if you want to make an initial validation of some field before uploading to your base and actually start importing, this can generate a lot of server performance savings and high user feedback.

Has a solution using Filereader.

Example:

var leitorDeCSV = new FileReader();

window.onload = function init() {
  leitorDeCSV.onload = leCSV;
}

function pegaCSV(inputFile) {
  var file = inputFile.files[0];
  leitorDeCSV.readAsText(file);
}

function leCSV(evt) {

  var fileArr = evt.target.result.split('\n');
  var strDiv = '<table>';

  for (var i = 0; i < fileArr.length; i++) {
    strDiv += '<tr>';
    var fileLine = fileArr[i].split(',');
    for (var j = 0; j < fileLine.length; j++) {
      strDiv += '<td>' + fileLine[j].trim() + '</td>';
    }
    strDiv += '</tr>';
  }

  strDiv += '</table>';

  var CSVsaida = document.getElementById('CSVsaida');
  CSVsaida.innerHTML = strDiv;
}

<input type="file" id="inputCSV" onchange="pegaCSV(this)">
<div id="CSVsaida"></div>