pyExcelerator: Python exportar Excel to MySQL

Hi all… here is the code to import the xls data into mysql using pyexcelerator and python.. About the pyexcelarator installation and configuration i have discussed an article… click this link to view that if you dont have any idea what is pyexclerator.. https://tareqalam.wordpress.com/2008/05/03/python-exportar-mysql-to-excel/

Now here is the python script which takes input a xls file and inserts the comment field it into the mysql table. basically it is helpful if you want to add comment to a mysql table.. I know there is some other easy way to do that.. but this code by making some customization you can insert data also.. Maybe my coding is not that rich.. but i am trying to help people.. if my code helps people a little i will be happy.. so hav e a look on the code…

import pyExcelerator
import sys
from pprint import pprint as pp

#put the table name here
table_name = 'test_table'
#put the xls file name here after modifying the commet fields
xls_file = 'test_table.xls'


sql_array = []
try:
workbook = pyExcelerator.parse_xls(xls_file)
except:
sys.exit('cant load %s '%xls_file)
worksheet = workbook[0]
# print cells A1 and B1
rowvalue= worksheet[1]
#pp(worksheet)
#sys.exit(1)
err_flag=0
#print t[(10,0)]
#print len(row_value.keys())
i = 1
for row_idx, col_idx in sorted(row_value.keys()):
if row_idx !=0:
err_flag=0
try:
MODIFY_FIELD_COMMENT = row_value[(i,0)]
except:
err_flag=1
try:
MODIFY_FIELD_NAME = row_value[(i,4)]
except:
err_flag=1
try:
MODIFY_FIELD_TYPE = row_value[(i,8)]
except:
err_flag=1

if err_flag == 0:
sql_for_insert_data_into_table="alter table %s modify %s %s COMMENT '%s'"%(table_name,MODIFY_FIELD_NAME,MODIFY_FIELD_TYPE,MODIFY_FIELD_COMMENT)
sql_array.append(sql_for_insert_data_into_table)
i=i+1


#print sql_array

#now inserting comments are done here

import MySQLdb

conn = MySQLdb.connect(host='localhost',user='root',passwd='',db='test_db')
cu = conn.cursor()
for insert_row_sql in sql_array:
try:
cu.execute(insert_row_sql)
except MySQLdb.Error, e:
errInsertSql = "Insert Sql ERROR!! sql is==>%s" %(insert_row_sql)
print errInsertSql
#sys.exit(errInsertSql)
continue
print 'done!! see in the database the %s table field\'s comment'%table_name
#pp(worksheet)

Pleas contact me if you are unable to understand any thing.. i ll try to help you ..

Thanks…

Advertisements
About

Work is fun!

Tagged with: , , ,
Posted in ploneCMS, python
4 comments on “pyExcelerator: Python exportar Excel to MySQL
  1. Martin says:

    Hi i need help with a program.. maybe you can help me.. you get paid for it.. see you on msn ?
    msn: martindarken@gmail.com

  2. arjun says:

    hey can u help me to convert Excel to mysql.?

  3. tareqalam says:

    yes bro .. but as i am busy a lot .. spending time here will have little cost. feel free to contact tareq.mist@gmail.com

  4. Thank you! Thank you! Thank you!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Disclaimer
All content provided on this "tareqalam.com" blog is for informational purposes only. The owner of this blog makes no representations as to the accuracy or completeness of any information on this site or found by following any link on this site. The owner of [tareqalam.wordpress.com] will not be liable for any errors or omissions in this information nor for the availability of this information. The owner will not be liable for any losses, injuries, or damages from the display or use of this information.
June 2008
M T W T F S S
« May   Jul »
 1
2345678
9101112131415
16171819202122
23242526272829
30  

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 23 other followers

%d bloggers like this: