import an excel sheet in mysql

[developer]

I want to import an excel sheet data into mysql database. Can anybody help me in doing this. Any suggestion

[guelphdad]

save the excel sheet as comma separated values (CSV) and use LOAD DATA INFILE to import it into mysql.

[hcamelion]

Good Luck...I have had alot of problems with importing excel into mysql. I had alot of data. I think I ended up importing it into access and moving it into mysql using my MyODBC.

[Daemonspyre]

The easiest way I have found to do this is a 3 step process, although Guelph's is probably the quickest.

1) Go into Excel and make sure your date fields are set to European Date/time (I use Afrikaans because it is exactly what MySQL wants and it's first on the list.)

2) Make sure all single quotes are set to \' so that there isn't an issue with inserting data.

3) Create a formula that says ="('"&A1&"','"&B1&"','"&C1&"')," until all your cells are captured and do a copy paste until all rows are captured. Select all your rows and do a COPY again. Open up MySQL Query Browser, write out the first half of your INSERT statement and then do a bulk PASTE. Correct any errors.

Done. It's not the fastest, nor the easiest, but it doesn't take too long once you get the hang of it. I can insert 50K+ rows in a matter of minutes, rather than trying to figure out why it didn't load from the CSV.

The other option is to use DBTools, MySQL Migration Toolkit, or some other graphical importer to take the data from Excel and put it into MySQL.

HTH!