Member Avatar for 54uydf

here's the deal. I want to import data from xls file.at the moment I'm using phpMyAdmin's Import option to do it, but I'm wondering if there's another way to import the file to a new table in my db because-
-the first rows and cols in the file are empty, which means I need to delete them manually before each import.
-there are some cols that have the same name field, so I need to change it too (cuz there can't be 2 fields with the same name in the db) and to something specific each time so that the code I wrote won't give me problems
-I want to import few files with 1 click, and at the moment I'm importing each file separately.
-the name of the table that the import creates is set by the name of the excel sheet, which I also need to set correctly otherwise my code won't recognize the table.

so there's a lot of manual work to be done for a simple import. is there a way to make this process easier?

  • 3 Contributors
  • 2 Replies
  • 203 Views
  • 3 Weeks Discussion Span
  • Latest Post Latest Post by linuxaomi

Dani AI

Generated

A practical, low-effort workflow: write a small PHP import script that (1) reads each Excel file with a maintained PHP library, (2) detects the first non-empty row as the header, normalizes/uniquifies header names, (3) creates or maps the target table name programmatically, and (4) bulk-inserts rows. As suggested, a PHP script is the right approach; and while ’s mention of older readers points you toward PHP libraries, the PHPExcel project is archived and you should use the maintained PHPOffice/PhpSpreadsheet instead. (github.com)

Use PhpSpreadsheet readers to skip the blank leading rows/columns and to load just the sheet(s) you want. Example (core idea only — adapt to your environment):

require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\IOFactory;

$reader = IOFactory::createReader('Xls');
$reader->setReadEmptyCells(false);
$reader->setIgnoreRowsWithNoCells(true);
$reader->setLoadSheetsOnly($sheetName);
$spreadsheet = $reader->load($filePath);

// find first non-empty row and treat it as header, then normalize duplicates
// (trim, convert spaces to underscores, append _1, _2 for duplicates)

Those reader options and the ability to load specific sheets let you avoid manual trimming of empty rows/cols and control which sheet becomes the table source. After you have a clean header array, generate a safe table name (or map sheet name -> table name), create the table with TEXT/VARCHAR columns (or infer types), then insert rows with prepared statements or batched inserts. (phpspreadsheet.readthedocs.io)

For speed at scale, write each cleaned file to CSV and use MySQL’s native bulk loader (LOAD DATA [LOCAL] INFILE ... IGNORE 1 LINES) — it’s designed for high-speed imports. Note: LOCAL may be disabled by default for security and must be enabled on both client and server if you use it. Test on a copy first. Example SQL (adjust paths and FIELDS/LINES clauses as needed):

LOAD DATA LOCAL INFILE '/tmp/file.csv'
INTO TABLE target_table
FIELDS TERMINATED BY ',' ENCLOSED BY '"' 
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

Use chunked reads / read filters and setReadDataOnly(true) for large files to save memory; iterate rather than calling toArray() for huge sheets. Always validate header normalization, run inside a transaction for multi-file imports, and log warnings for skipped rows. (dev.mysql.com)

Recommended Answers

All 2 Replies

Member Avatar for urtrivedi

IF you have php installed on that server you can do this thing by using php script (need to be developed according to your requirements).

Member Avatar for linuxaomi

phpExcelReader.zip does the trick. I am using XAMPP under WINDOWS and LINUX as well.
Good luck, linuxaomi

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.