Populating mysql database with data from excel

[qwickmalik]

salam everyone,

can anyone assist me populate a mysql database with data from excel? the excel sheet has similar structure to the mysql. any php script will be appreciated.

Jazak Allah khayr

[GuCcI]

hehehe... no...

your avatar is scary :nervous: it reminds me of guvna`s :smack:

[Basil al-Mamluk]

I am interested in this as well...

[qwickmalik]

hehehe... no...

your avatar is scary :nervous: it reminds me of guvna`s :smack:

gee .... thanx :nuts:

[MMS]

you could save the excel as .csv file and use 'Load data Infile' query in sql

[Love&Peace]

http://support.microsoft.com/kb/321686

This gives VB code, I will search for php and get back.

[irony]

As someone already mentioned, you can use 'load data infile' SQL query.

More info:

http://dev.mysql.com/doc/refman/5.0/en/load-data.html

http://www.tech-recipes.com/rx/2345/import_csv_file_directly_into_mysql

[qwickmalik]

salam everyone,

thanks for all the help. I guess the most popular way is to save your file as a .csv and load. I didnt use the Load_data_infile method, but rather used explode() - implode() found the solution on this site: http://www.modwest.com/help/kb6-253.html

I used a comma delimited .csv

[qwickmalik]

.... a little hint:

if you want to use a comma delimited .csv, make sure your data do not contain commas, else, each comma will be counted as a new column and those rows will not be inserted since their column number will not match that of your database.

a useful tip is to replace all commas in your data with semi-colons

[Mace]

a useful tip is to replace all commas in your data with semi-colons

Or you can wrap your fields in quotes and keep the commas.

[nami]

Or you can connect to your excel file like this:

Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=C:\MyExcel.xls;DefaultDi r=c:\mypath;

Extract the data like this:

SELECT * FROM [sheet1$]

Then connect to your mysql database and do a standard sql insert statement.