How to import an MS Excel file into MySQL via PHP?
Hi Experts,
I'm in need of a PHP solution that will emulate the import function of PHPMyAdmin.
The import should parse an Excel file, then use the first row within the Excel file as the column names of the DB table, then insert each row of the Excel document into the table.
I've seen many solutions out there which allow PHP to import an Excel file but, the catch with these solutions is that you must create the data table first, then use PHP to parse and insert the data. In my circumstance, I won't know how many columns exist within a spreadsheet that is being imported. I need PHP to figure how many columns should exist in the table, and what data types they are, then create the columns and insert each row of data into the table (this may not be correct programming logic).
Does anyone know of any snippets of code or, third party libraries that may accomplish this task?
Cheers
I'm in need of a PHP solution that will emulate the import function of PHPMyAdmin.
The import should parse an Excel file, then use the first row within the Excel file as the column names of the DB table, then insert each row of the Excel document into the table.
I've seen many solutions out there which allow PHP to import an Excel file but, the catch with these solutions is that you must create the data table first, then use PHP to parse and insert the data. In my circumstance, I won't know how many columns exist within a spreadsheet that is being imported. I need PHP to figure how many columns should exist in the table, and what data types they are, then create the columns and insert each row of data into the table (this may not be correct programming logic).
Does anyone know of any snippets of code or, third party libraries that may accomplish this task?
Cheers
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Of course you can. As I know, you have at least 3 options:
Just notice one thing:
Excel: Every cell independently can contain any data type
Database table: Columns have a specifc data type. Might be NULL, might contain "anything" with binary or string data types.
It's bad enough the universality of string data types is often used to store dates in string columns introducing the need to convert this to datetime times to enable calculating with them.
I don't know whether you look for a simple data import option for any user, but you shouldn't invite anyone to upload any file and not even checking whether it has the columns and values valid for the tarbet MySQL table.
If that's for admins, just let them do data imports more directly, such a PHP frontend is doing no good for them.
Excel: Every cell independently can contain any data type
Database table: Columns have a specifc data type. Might be NULL, might contain "anything" with binary or string data types.
It's bad enough the universality of string data types is often used to store dates in string columns introducing the need to convert this to datetime times to enable calculating with them.
I don't know whether you look for a simple data import option for any user, but you shouldn't invite anyone to upload any file and not even checking whether it has the columns and values valid for the tarbet MySQL table.
If that's for admins, just let them do data imports more directly, such a PHP frontend is doing no good for them.
Hi,
You can do it in two ways.
1) If you want to make it dynamic for user then you have to write php code for that to upload excel sheet in predefined format. Then you can fetch details from excel sheet and insert into the database.
2) if it is one time then convert it to csv as per you database table column structure and import it.
Thanks.
You can do it in two ways.
1) If you want to make it dynamic for user then you have to write php code for that to upload excel sheet in predefined format. Then you can fetch details from excel sheet and insert into the database.
2) if it is one time then convert it to csv as per you database table column structure and import it.
Thanks.
ASKER
Hi Experts,
Thank you for your responses.
Despite the real issues/concerns Olaf raises in his post, my business needs require me to create a dynamic process.
I'm working on my code and will post the final result shortly.
Cheers!
Thank you for your responses.
Despite the real issues/concerns Olaf raises in his post, my business needs require me to create a dynamic process.
I'm working on my code and will post the final result shortly.
Cheers!
ASKER
Thank you for your reply.
I've not used the Load Data SQL syntax before so excuse my ignorance. If you wouldn't mind confirming my understanding of how I'd use the Load Data syntax, I'd appreciate it.
Example Usage:
1) Create simple form which accepts user file input
2) POST the form data to the web server via PHP
3) Store the file location within a PHP variable
4) Substitute the PHP variable for the 'file_name' syntax within the Load Data statement
5) Execute the Load Data statement via PHP
Am I thinking of this correctly?
Something like the accepted solution here: https://stackoverflow.com/
Thanks!