Read Excel from Python

-

First we need to import xlrd module

then open the workbook and read cell by cell 

Complete code given below-


import xlrd

#loc = (r'C:\Users\xyz\Python_Local\MyExcel.xls')

#print(loc)

wb = xlrd.open_workbook(r'C:\Users\xyz\Python_Local\File1.xls')

sheet = wb.sheet_by_index(0)

# For row 0 and column 0

sheet.cell_value(0, 0)

# Extracting number of columns

print(sheet.ncols)

# Extracting number of rows

print(sheet.nrows)

Comments

Post a Comment

Popular posts from this blog

[Solved]SyntaxError: (unicode error) 'unicodeescape' codec can't decode bytes in position 2-3: truncated \UXXXXXXXX escape

-
 Solution - CASE1- If you are working with file path location for example- xlrd.open_workbook('C:\Users\xyz\Python_Local\File1.xls') Then it will throw the "Unicode"  error as  \U represents the Unicode escape because this is normal string not raw string. So use raw string to avoid this error by following methods- (r'C:\Users\xyz\Python_Local\File1.xls') ('C:\\Users\\xyz\\Python_Local\\File1.xls') ('C:/Users/xyz/Python_Local/File1.xls') CASE2- If we use normal string to print for example- print('Hello World") then it is fine If we want special character as normal character for example- print('It's world') # will throw error  so we have use '\'  escape  character to make ' normal character. print('It\'s world')
Read more

Read the excel and insert data into My SQL using Python

-
Install xlrd package Code- import xlrd import mysql.connector from mysql import connector # Open the workbook and define the worksheet book = xlrd.open_workbook(r'C:\Users\xyz\Python_Local\File1.xls') sheet = book.sheet_by_name("Sheet1") # Establish a MySQL connection conn = mysql.connector.connect(host='127.0.0.1',database='firstschema',user='root',password='2511') # Get the cursor, which is used to traverse the database, line by line cursor = conn.cursor() # Create the INSERT INTO sql query query = """INSERT INTO firstschema.tbl1 (name, mobile, location) VALUES (%s, %s, %s)""" # Create a For loop to iterate through each row in the XLS file, starting at row 2 to skip the headers for r in range(1, sheet.nrows): name = sheet.cell(r,0).value mobile     = sheet.cell(r,1).value location    = sheet.cell(r,2).value # Assign values from each row values = (name, mobile, location) # Execute...
Read more