Converting Excel-to-Mysql

[phence]

How can i convert Excel to a Mysql Database.

I have searched around, and have found nothing but one 'EXCEL TO MYSQL' program.

How can i do this manually? or is their a free program which can do this?

[jburfield]

The simplest thing to do is to save your Excel sheet as a tabbed text file.

So, if you have an Excel sheet with the following items:
id name age
1 Jim 30

You will have a test file with these same elements, only with tabs between then.

You can then load that into your tables using something like the following:

load data local infile '/path/to/your/tabbed/file' into table YOUR_TABLE (id, name, age);

Assumes YOUR_TABLE has fields of 'id', 'name' and 'age.'

That will import the file, creating one record for each line in the tabbed file.

Hope this helps.

-- Jason

[DRTechie]

what happens if a there's a blank field in the excel file? for example, my excel file has 5 fields which are filled in for most of the records but some records are missing one or more fields. Will MySQL recognize this as a NULL value?

[jburfield]

Yes, it should see them as empty values. At least it always has for me.

To be REALLY safe, you could put in " characters at the start and end of every field and in your load data statement add a 'FIELDS TERMINATED BY' statement.

-- Jason

[vtb]

I recently needed to convert Excel into MySQL but I don't own Excel -- nor do I want to (-: -- and supposedly compatible products like Corel Quattro were not exporting to text properly. The Perl DBI::Excel module was a lifesaver.🆒