Read Excel content for database: Read data from Excel file and insert in database

All requests

>

Read Excel content for database

>

Request new recommendation

>

Featured requests

>

No recommendations

Read Excel content for database #excel

Edit

by Eweck - 3 months ago (2024-12-07)

Read data from Excel file and insert in database

+1	Read data from excel file (template) and insert them in database. Should contain error management for required column, data format and control of empty lines

• 2 Clarification requests

• 2. by ASCOOS CMS - 3 months ago (2024-12-09) Reply

  Use the best library available, PhpSpreadsheet.

  Here's a sample of use (one of the basic examples), a little tailored to your own requirements.

  <?php require 'vendor/autoload.php';

  use PhpOffice\PhpSpreadsheet\IOFactory;

  $inputFileName = 'path_to_excel_file.xlsx';

  $servername = "localhost"; $username = "username"; $password = "password"; $dbname = "database";

  $conn = new mysqli($servername, $username, $password, $dbname);

  if ($conn->connect_error) {

  die("Connection failed: " . $conn->connect_error);
  

  }

  try {

  $spreadsheet = IOFactory::load($inputFileName);
  

  } catch (Exception $e) {

  die('Error loading file: '.$e->getMessage());
  

  }

  $sheet = $spreadsheet->getActiveSheet(); $highestRow = $sheet->getHighestRow(); $highestColumn = $sheet->getHighestColumn();

  // Read data from Excel, check for errors and import into the database for ($row = 2; $row <= $highestRow; $row++) {

  $data1 = $sheet->getCell('A'.$row)->getValue();
  $data2 = $sheet->getCell('B'.$row)->getValue();
  $data3 = $sheet->getCell('C'.$row)->getValue();
  
  // Check empty lines
  if (empty($data1) && empty($data2) && empty($data3)) {
      continue;
  }
  

  // Check data format (e.g., if a number is required)
  if (!is_numeric($data1)) {
      die("Error: Data in column A, row $row is not numeric.");
  }
  

  // Add to the database
  $sql = "INSERT INTO table_name (column1, column2, column3) VALUES ('$data1', '$data2', '$data3')";
  
  if (!$conn->query($sql)) {
      die("Error inserting data: " . $conn->error);
  }
  

  }

  echo "Data imported successfully.";

  $conn->close(); ?>

• 1. by Cedric Maenetja - 3 months ago (2024-12-09) Reply

  I am not sure if a package for this requirement is needed. I think this is a very specific request, for a specific purpose. Instead of requesting a package for this, maybe have a look at PHP-ExcelReader or PHPExcel to read the documents and do the inserts.

Ask clarification