How to Convert Excel File into MySQL Database

How to import an excel file in to a MySQL database

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.

How to convert excel file into mysql database?

If you have a web server up and running with PHP support, I highly recommend phpMyAdmin.

1. Configure it to connect to MySQL
2. Create the Database and table
3. Click the import tab and you can import a CSV.

If this is a simple one-time import this probably isn't worth the effort. If, on the other hand, you will ever have to work with MySQL again, you will never regret the time to install and configure phpMyAdmin.

How to export excel data into Mysql tables

Convert your Excel to CSV first then import in phpmyadmin. The number of columns in csv should be equal to number of columns in table, If you have auto increment for first column then leave first column of CSV blank.

IF you dont use phpmyadmin this or this will help you out with import.

importing from excel to mysql

You code is currently only storing the last pair, and writing that to the database. You need to call fname and lname inside the loop and write each pair seperately to the database.

You can ammend your code to this:

import pymysql
import xlrd

book = xlrd.open_workbook('C:\SqlExcel\Backup.xlsx')
sheet = book.sheet_by_index(0)

# Connect to the database
connection = pymysql.connect(host='localhost',
                             user='root',
                             password='',
                             db='test',
                             autocommit=True)

cursor = connection.cursor()
query = """INSERT INTO report_table (FirstName, LastName) VALUES (%s, %s)"""

# loop over each row
for r in range(1, sheet.nrows):
    # extract each cell
    fname   = sheet.cell(r,1).value
    lname   = sheet.cell(r,2).value

    # extract cells into pair
    values = fname, lname

    # write pair to db
    cursor.execute(query, values)

# close everything
cursor.close()
connection.close()

Note: You can set autocommit=True in the connect phase. PyMySQL disables autocommit by default. This means you dont have to call cursor.commit() after your query.

importing excel table into database

You have two ways to realize that :

First method :

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.

Second method :

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.

Import Data from Excel Spreadsheet or CVS into MySQL

Have a look at LOAD DATA INFILE statement. It will help you to import data from the CSV file into table.

---

Related Topics