How to import an excel file in to a MySQL database

Can any one explain how to import a Microsoft Excel file in to a MySQL database? For example, my Excel table looks like this:

Country | Amount | Qty ---------------------------------- America | 93 | 0.60 Greece | 9377 | 0.80 Australia | 9375 | 0.80 

[You might take a look at this thread][1] on the MySQL forums. It explains how to do exactly what you want. [1]:forums.mysql.com/read.php?32,216343,216344#msg-216344

+1 for a new and different idea. The processing is insanely slow, it first reads each row in file and then upload anything. It took about 15 mins to import 5.2K rows

For some spreadsheets it can be slow, due to number of columns and rows. But, I blame microsofts com-object library which is what is used to read the spreadsheet. My program reads the spreadsheet as fast as the library will allow. The mysql inserts are very fast. If you can eliminate unnecessary columns before importing. that can help.

I had 14 columns on 5173 rows. I had already selected all the empty columns/rows and deleted them to avoid unnecessary processing. Sytem that I was using had 2.5G RAM and core2duo processor, didn't have many processes running, performance tab showed 65% usage in all and a lot of RAM still unused. So, I guess I wouldn't blame the hardware but like you said, MS com objects suck.. I don't know when MS will stop building crap that look like life savers for novices. I'm sick of doing extra crap for MS products.

15 Answers 15

There's a simple online tool that can do this called sqlizer.io.

You upload an XLSX file to it, enter a sheet name and cell range, and it will generate a CREATE TABLE statement and a bunch of INSERT statements to import all your data into a MySQL database.

(Disclaimer: I help run SQLizer)

Good to know this tool exists, but definitely think about if you should use it or not. If your spreadsheet contains sensitive data (e.g. user emails, passwords, cc info, medical info, etc) it may not be a good idea. This site may not store your data and it may be secure, but there's no way for you to know that for sure.

@DivyeshJesadiya It's free for anything up to 5000 rows. After that you have to pay $10 for a month of usage.

@ChrisSchmitz we are super-cautious with security and wrote a blog post recently about how we operate SQLizer blog.sqlizer.io/posts/privacy-at-sqlizer

Below is another method to import spreadsheet data into a MySQL database that doesn't rely on any extra software. Let's assume you want to import your Excel table into the sales table of a MySQL database named mydatabase .

1. Select the relevant cells:
2. Paste into Mr. Data Converter and select the output as MySQL:
3. Change the table name and column definitions to fit your requirements in the generated output:

CREATE TABLE sales ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, Country VARCHAR(255), Amount INT, Qty FLOAT ); INSERT INTO sales (Country,Amount,Qty) VALUES ('America',93,0.60), ('Greece',9377,0.80), ('Australia',9375,0.80); 

1. If you're using MySQL Workbench or already logged into mysql from the command line, then you can execute the generated SQL statements from step 3 directly. Otherwise, paste the code into a text file (e.g., import.sql ) and execute this command from a Unix shell: mysql mydatabase < import.sql Other ways to import from a SQL file can be found in this Stack Overflow answer.

This was the easiest way. Thanks! Just remember to remove the extra Primary Key 'ID' in the SQL, and place an AUTO increment of your choice.

1. Export it into some text format. The easiest will probably be a tab-delimited version, but CSV can work as well.
2. Use the load data capability. See http://dev.mysql.com/doc/refman/5.1/en/load-data.html
3. Look half way down the page, as it will gives a good example for tab separated data: FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\'
4. Check your data. Sometimes quoting or escaping has problems, and you need to adjust your source, import command-- or it may just be easier to post-process via SQL.

When exporting to CSV, [at least] excel 2013 actively attempts to poison data by using VBA-escaped doublequotes, using locale-dependent (based on OS regional settings) decimal separator for 0 values (e.g. ',', while using 'as defined in cell properties' separator for all other values. Best to stay away from CSV.

That's isn't the only problem. I am not sure if this procedure deals correctly with carriage return inside a cell in excel. Even the imports from csv to excel fails in that

@afk5min what do you mean by "poison data". All those tags and markup are very useful for the users that are going to obviously import that into another MS product.

There are actually several ways to import an excel file in to a MySQL database with varying degrees of complexity and success.

1. Excel2MySQL. Hands down, the easiest and fastest way to import Excel data into MySQL. It supports all verions of Excel and doesn't require Office install.
2. LOAD DATA INFILE: This popular option is perhaps the most technical and requires some understanding of MySQL command execution. You must manually create your table before loading and use appropriately sized VARCHAR field types. Therefore, your field data types are not optimized. LOAD DATA INFILE has trouble importing large files that exceed 'max_allowed_packet' size. Special attention is required to avoid problems importing special characters and foreign unicode characters. Here is a recent example I used to import a csv file named test.csv.
3. phpMyAdmin: Select your database first, then select the Import tab. phpMyAdmin will automatically create your table and size your VARCHAR fields, but it won't optimize the field types. phpMyAdmin has trouble importing large files that exceed 'max_allowed_packet' size.
4. MySQL for Excel: This is a free Excel Add-in from Oracle. This option is a bit tedious because it uses a wizard and the import is slow and buggy with large files, but this may be a good option for small files with VARCHAR data. Fields are not optimized.