multiple files import to DB

[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.

[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.

[54uydf]

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

[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.

[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..

[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).

[54uydf]

unfortunatly no..just xls or xlsx

[54uydf]

mission impossible guys?

[diafol]

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.

[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.. :)

[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?

[54uydf]

I sent you a PM

[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..

[diafol]

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.

[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