Excel to mysql?

Question

I've seen a lot of questions here about how to export a sql database to excel, but I have a ton of information from an excel file that I would like to export to sql database. The file is extensive and it could take me years to transfer the info manually... years! Is there a faster easier way to do this?

The answer depends on the kinds of scripting/programming you are familiar with. — nurettin, Jun 01 '12 at 07:16

score 2 · Accepted Answer · edited May 23 '17 at 12:33

2

You don't actually need to write code to do this.

Save the Excel file to CSV format and then see one of the many related questions:

The friendliest way is to use a graphical tool like HeidiSQL to match up the excel columns to the columns in your database.

HeidiSQL

edited May 23 '17 at 12:33

Community

1
1

answered Jun 01 '12 at 07:19

Colin Pickard

45,724
13
98
148

score 0 · Answer 2 · answered Jun 01 '12 at 07:19

You can use apache poi library to read the excel file line by line and jdbc to create connection to mysql database and execute insert statements.

In my project I used jruby with nurettin-jruby-poi fork of the jruby-poi gem to read the excel file and activerecord to insert the data into mysql.

score 0 · Answer 3 · answered Jun 01 '12 at 07:21

0

Write a .NET Program (Console is simpler)
Access the Excel Worksheet/Workbook
Copy the Attributes and create appropriate Tables for the sheets in your Excel
Now copy the contents to SQL Tables appropriately

Since it is automated it will be faster

answered Jun 01 '12 at 07:21

Rahul Reddy

128
10

score 0 · Answer 4 · answered Jun 01 '12 at 07:23

0

As Colin Pickard said, you can use CSV format that can be directly imported.

If you're using PHP, you can use the PHPExcel library here which can do the job !
Btw, you should tell us if you're using a certain language..

answered Jun 01 '12 at 07:23

Val

762
8
32

score 0 · Answer 5 · answered Jun 01 '12 at 07:53

0

You can use a GUI Data Import tool, it supports direct import from *.XLS and *.XLSX (Excel 2007) files.

answered Jun 01 '12 at 07:53

Devart

119,203
23
166
186

score 0 · Answer 6 · answered Jun 01 '12 at 08:12

0

Use Talend Open Studio to visually design and then automate any kind of data integration job.

http://www.talend.com/index.php

answered Jun 01 '12 at 08:12

Laszlo T

1,165
10
22

panofish · Answer 7 · 2014-10-14T13:20:07.807

0

My favorite tool for converting excel sheets into mysql database... Full disclosure, I am the author.

http://excel2mysql.net

edited Oct 14 '14 at 13:20

answered Dec 20 '12 at 16:00

panofish

7,578
13
55
96

score 0 · Answer 8 · answered Nov 30 '14 at 12:12

creating a CSV is better. if we use CSV method we can export any character sets(unicode) also ones we create a CSV file then, just use LOAD method available in mySql. load method is used to import data to mysql directly(using sql query). LOAD local permit you to import data from any location in your local machine

Excel to mysql?

8 Answers8