Member Avatar for 54uydf

is it possible to import say 10 excel files into DB at once?
like in PhpMyAdmin, I go and import each file one by one and they are added as new tables.
is there some kind of php code I can write to take all files in X folder and import them? without selecting them one at a time.

  • 5 Contributors
  • 14 Replies
  • 429 Views
  • 4 Days Discussion Span
  • Latest Post Latest Post by 54uydf

Dani AI

Generated

Yes — you can automate importing many Excel files. A small PHP script (run from CLI, via cron, or from a protected admin page) can scan a folder, parse each .xls/.xlsx and create/populate a table for each file. For maintained, full-featured support for both XLS and XLSX use PhpSpreadsheet (successor to PHPExcel); if you only need XLSX and want something lighter, SimpleXLSX is an alternative. (github.com)

Practical workflow: drop the spreadsheets into a folder, use PHP to gather filenames, then for each file load it with PhpSpreadsheet (IOFactory will auto-detect), treat row 1 as the header, sanitize headers into SQL-safe column names, CREATE TABLE (TEXT columns is the simplest safe default) and INSERT the remaining rows using prepared statements inside a transaction. For many or very large rows, convert each sheet to CSV and use MySQL’s LOAD DATA INFILE for much faster bulk import (watch secure_file_priv / LOCAL settings). Use batched reads or iterators rather than toArray() for big files to keep memory under control. (php.net)

Example skeleton (very small — adapt and harden before running):

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

$dir = '/path/to/excels';
$pdo = new PDO('mysql:host=127.0.0.1;dbname=yourdb;charset=utf8mb4', 'user', 'pass', [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);

foreach (glob("$dir/*.{xls,xlsx}", GLOB_BRACE) as $file) {
    $sheet = IOFactory::load($file)->getActiveSheet();
    $rows = $sheet->toArray(null, true, true, true); // OK for small files
    $headers = array_shift($rows);
    $table = preg_replace('/[^A-Za-z0-9_]/','_', pathinfo($file, PATHINFO_FILENAME));
    // build CREATE TABLE with sanitized $headers, prepare INSERT with placeholders,
    // then loop rows with $pdo->prepare() and execute() inside a transaction.
}

See PhpSpreadsheet docs for IOFactory/load and reader options. (phpspreadsheet.readthedocs.io)

Do not try to “fake” .frm files — those are internal MySQL table-definition artifacts (and have been removed/changed in modern MySQL versions); instead use proper CREATE TABLE SQL. Back up the database, test on a local copy, and protect the import script (or run it from CLI/cron). The loop approach suggested by is the right pattern; schedule it as suggested if you need regular updates, and consider / ’s CSV route if exporting to CSV is an option. (dev.mysql.com)

Recommended Answers

All 14 Replies

Member Avatar for sv3tli0

You can't import them at once.


What is the structure of your files?
Your data must be stored at one DATABASE and at one table? or at one database in 10tables I didn't understand you about that.

Member Avatar for 54uydf

1 db 10 tables
the files would have in the 1st row the feild names and rest of the rows are records..

Edited by 54uydf because: n/a
Member Avatar for sv3tli0

Try this:
Create database.
Click on this database at your phpmyadmin.
Then click at the Import (after you have selected the database) and import.
It should create each new table at this database.

Member Avatar for 54uydf

that's what I'm doing now, but then I have to import each file separately ..and the more files there are the more uncomfortable it is... and the DB has to be updated all the time.. so u see the problem with that..

Member Avatar for eXpertPHP

Excel export files are .csv?
If yes, you can use fgetcsv() php function (erelsgl at gmail dot com 09-May-2011 12:57 post example).

Edited by eXpertPHP because: n/a
Member Avatar for 54uydf

unfortunatly no..just xls or xlsx

Member Avatar for 54uydf

mission impossible guys?

Member Avatar for Member #120589

When you say all at once, I expect you mean one routine which may contain multiple queries. If so, yes of course it can be done with a loop:

PSEUDOCODE:

$files = array(file1,file2,file3....);
foreach($files as $file){
//strip file extension from $file into var $table
//read file and get headers and records into array of arrays (row|fields) 
//check table with this not does not exist + create (mysql query)
//populate table with data
}

the files array can be built dynamically from a form with filename dropdowns or even file upload widgets.

Edited by Member #120589 because: n/a
Member Avatar for sv3tli0

In addition to the previous user, if you want to make this process automated you can set CRON JOB task which will run this file once upon a day or the time you want it.

For security reasons I think it will be good not to include this file to your site script, especially if you use DROP function at your queries!
You can put it on your localhost and/or lock it with password for little more security.. :)

Member Avatar for ddymacek

perhaps I could help but would need a sample xls file, I have only worked with csv's to do what you want. It would only have to be 3 or 4 rows long with the header fields and some garbage data. Is it possible to create the xls files as csv?

commented: can't thank you enough!! +3
Member Avatar for 54uydf

I sent you a PM

Member Avatar for 54uydf

I have this idea, it's a long shot but still. I'm thinking of maybe writing a code that would change the xls or csv file into a frm file and then move it to the folder that has the rest of the tables in my db..
anybody knows how the frm file actually looks so we can 'fake' one?

or maybe there is another way to make phpmyadmin turn the files into tables without actually opening phpmyadmin and importing each file..

Member Avatar for Member #120589

Why frm?
Have you tried any of the suggestions?
You don't need phpmyadmin.
You can change all your xls files into csv and import them easily. You just need to research and code. You've been given all the hints you need to do this - well pretty much.
Good luck with it.

Member Avatar for 54uydf

frm is the file type of the tables.
I researched and tried but it's above my skill level at the moment, so I'm looking for other options besides coding the whole thing from scratch.. I mean, someone very smart already did it perfectly so I'd like to use what is already out there

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.