# Working with CSV & Excel Files 1. CSV 2. Excel #### Reading CSV Files: Example 1: ```python # importing csv module import csv # csv file name filename = "C:/Users/Shalki/Desktop/python/Book1.csv" # initializing the titles and rows list fields = [] rows = [] # reading csv file with open(filename, 'r') as csvfile: # creating a csv reader object csvreader = csv.reader(csvfile) # extracting field names through first row fields = next(csvreader) # extracting each data row one by one for row in csvreader: rows.append(row) # get total number of rows print("Total no. of rows: %d"%(csvreader.line_num)) # printing the field names print('Field names are:' + ', '.join(field for field in fields)) # printing first 5 rows print('\nFirst 5 rows are:\n') #print(rows[1]) for row in rows[:5]: # parsing each column of a row for cell in row: print("%10s"%cell, end="\t") print('\n') ``` Output: ![](https://2666241650-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LSc84l98ozg0PCtzUMg%2F-M6BPvTn7cfkhCeFQORJ%2F-M6OVCpR0CdVDmbtHXQ8%2Fimage.png?alt=media\&token=dfcb0cc3-e4f3-45bb-8781-ea4dbc80f8b4) Example 2: ```python import csv file1 = "C:/Users/Shalki/Desktop/python/Book1.csv" with open(file1, 'r') as f: reader = csv.reader(f) for row in reader: rows.append(row) for row in rows[:5]: print(row) ``` \['Sonam', '23', 'gurgaon', 'haryana', '9999222200'] \['Arun', '65', 'patna', 'bihar', '7777222211'] \['Nitin', '76', 'faridabad', 'haryana', '6666000011'] \['Payal', '58', 'noida', 'up', '2222555599'] \['Jyoti', '99', 'delhi', 'delhi', '4444555577'] #### Writing CSV Files: ```python # importing the csv module import csv # field names fields = ['Name', 'Branch', 'Year', 'CGPA'] # data rows of csv file rows = [ ['Nikhil', 'COE', '2', '9.0'], ['Sanchit', 'COE', '2', '9.1'], ['Aditya', 'IT', '2', '9.3'], ['Sagar', 'SE', '1', '9.5'], ['Prateek', 'MCE', '3', '7.8'], ['Sahil', 'EP', '2', '9.1']] # name of csv file filename = "C:/Users/Shalki/Desktop/python/Book1.csv" # writing to csv file with open(filename, 'w') as csvfile: # creating a csv writer object csvwriter = csv.writer(csvfile) # writing the fields csvwriter.writerow(fields) # writing the data rows csvwriter.writerows(rows) ``` #### Writing Dictionary to csv file ```python # importing the csv module import csv # my data rows as dictionary objects mydict =[{'branch': 'COE', 'cgpa': '9.0', 'name': 'Nikhil', 'year': '2'}, {'branch': 'COE', 'cgpa': '9.1', 'name': 'Sanchit', 'year': '2'}, {'branch': 'IT', 'cgpa': '9.3', 'name': 'Aditya', 'year': '2'}, {'branch': 'SE', 'cgpa': '9.5', 'name': 'Sagar', 'year': '1'}, {'branch': 'MCE', 'cgpa': '7.8', 'name': 'Prateek', 'year': '3'}, {'branch': 'EP', 'cgpa': '9.1', 'name': 'Sahil', 'year': '2'}] # field names fields = ['name', 'branch', 'year', 'cgpa'] # name of csv file filename = "C:/Users/Shalki/Desktop/python/Book1.csv" # writing to csv file with open(filename, 'w') as csvfile: # creating a csv dict writer object writer = csv.DictWriter(csvfile, fieldnames = fields) # writing headers (field names) writer.writeheader() # writing data rows writer.writerows(mydict) ``` #### Reading an excel file using Python: ```python # Reading an excel file using Python import xlrd # Give the location of the file loc = "C:/Users/Shalki/Desktop/python/display.xlsx" # To open Workbook wb = xlrd.open_workbook(loc) sheet = wb.sheet_by_index(0) # For row 0 and column 0 print(sheet.cell_value(0,0)) ``` #### Output: ``` 'NAME' ``` #### Extract number of rows in Excel: ```python # Program to extract number # of rows using Python import xlrd # Give the location of the file loc = ("path of file") wb = xlrd.open_workbook(loc) sheet = wb.sheet_by_index(0) sheet.cell_value(0, 0) # Extracting number of rows print(sheet.nrows) ``` #### Output: 4 #### Extract number of columns: ```python #Program to extract number of # columns in Python import xlrd loc = ("path of file") wb = xlrd.open_workbook(loc) sheet = wb.sheet_by_index(0) # For row 0 and column 0 sheet.cell_value(0, 0) # Extracting number of columns print(sheet.ncols) ``` #### Output: 3 #### Extract a particular row value**:** ```python # Program to extract a particular row value import xlrd loc = ("path of file") wb = xlrd.open_workbook(loc) sheet = wb.sheet_by_index(0) sheet.cell_value(0, 0) print(sheet.row_values(1)) ``` **Output:** ``` ['ALEX', 4.0, 2011272.0]] ``` **Extract First Column** ```python # Program extracting first column import xlrd loc = ("C:/Users/Shalki/Desktop/python/display.xlsx") wb = xlrd.open_workbook(loc) sheet = wb.sheet_by_index(0) sheet.cell_value(0, 0) for i in range(sheet.nrows): print(sheet.cell_value(i, 0)) ``` Name Rahul Kapil **Creating/Writing in New Excel Workbook, Worksheet in Python** ```python from openpyxl import Workbook workbook = Workbook() sheet12 = workbook.active sheet12["A1"] = "hello" sheet12["B1"] = "world!" workbook.save(filename="C:/Users/Shalki/Desktop/python/display1.xlsx") ``` **Reading all worksheet names and cells data of existing workbook:** ```python from openpyxl import load_workbook workbook = load_workbook(filename="C:/Users/Shalki/Desktop/python/display1.xlsx") a = [] a = workbook.sheetnames for i in a: print(i) sheet = workbook.active print(sheet["A1"].value) print(sheet.cell(row=8, column=6).value) ``` Sheet Sheet1 Sheet2 London Australia **Iterating through Data** ```python from openpyxl import load_workbook workbook = load_workbook(filename="C:/Users/Shalki/Desktop/python/display1.xlsx") sheet = workbook.active for row in sheet.iter_rows(min_row=1,max_row=2,min_col=1,max_col=3,values_only=True): print(row) ``` ('gyansetu', 46, 976) (324, 45, 57) **Reading data from a particular worksheet** ```python from openpyxl import load_workbook workbook = load_workbook(filename="C:/Users/Shalki/Desktop/python/display1.xlsx") a = [] a = workbook.sheetnames for s in range(len(workbook.sheetnames)): if workbook.sheetnames[s] == 'Sheet1': break workbook.active = s sheet = workbook.active print(sheet["A1"].value) print(sheet.cell(row=8, column=6).value) ``` Sheet Sheet1 Sheet2 Hello Students **Putting data in a Dictionary:** ```python import json from openpyxl import load_workbook workbook = load_workbook(filename="C:/Users/Shalki/Desktop/python/display1.xlsx") sheet = workbook.active products = {} # Using the values_only because you want to return the cells' values for row in sheet.iter_rows(min_row=2, min_col=4, max_col=5, values_only=True): product_id = row[0] product = { "parent": row[1], } products[product_id] = product # Using json here to be able to format the output for displaying later print(products) print("The code returns json format ") print(json.dumps(products)) ``` {2: {'parent': 'Shalki'}, 3: {'parent': 'Sanchi'}, 4: {'parent': 'Milanz'}, 5: {'parent': 'Anuj'}} The code returns json format {"2": {"parent": "Shalki"}, "3": {"parent": "Sanchi"}, "4": {"parent": "Milanz"}, "5": {"parent": "Anuj"}} **Appending Excel/New data** ```python from openpyxl import load_workbook # Start by opening the spreadsheet and selecting the main sheet workbook = load_workbook(filename="C:/Users/Shalki/Desktop/python/display1.xlsx") sheet = workbook.active # Write what you want into a specific cell sheet["C1"] = "writing ;)" # Save the spreadsheet workbook.save(filename="C:/Users/Shalki/Desktop/python/display1_append.xlsx") ``` **Insert/Delete Row & Column** ```python from openpyxl import load_workbook workbook = load_workbook(filename="C:/Users/Shalki/Desktop/python/display1.xlsx") sheet = workbook.active def print_rows(): for row in sheet.iter_rows(values_only=True): print(row) print_rows() ('hello', 'world!') # Insert a column before the existing column 1 ("A") sheet.insert_cols(idx=1) print_rows() (None, 'hello', 'world!') workbook.save(filename="C:/Users/Shalki/Desktop/python/display2_append.xlsx") # Insert 5 columns between column 2 ("B") and 3 ("C") sheet.insert_cols(idx=3, amount=5) print_rows() (None, 'hello', None, None, None, None, None, 'world!') workbook.save(filename="C:/Users/Shalki/Desktop/python/display3_append.xlsx") # Delete the created columns sheet.delete_cols(idx=3, amount=5) sheet.delete_cols(idx=1) print_rows() ('hello', 'world!') workbook.save(filename="C:/Users/Shalki/Desktop/python/display4_append.xlsx") # Insert a new row in the beginning sheet.insert_rows(idx=1) print_rows() (None, None) ('hello', 'world!') # Insert 3 new rows in the beginning sheet.insert_rows(idx=1, amount=3) print_rows() (None, None) (None, None) (None, None) (None, None) ('hello', 'world!') workbook.save(filename="C:/Users/Shalki/Desktop/python/display5_append.xlsx") # Delete the first 4 rows sheet.delete_rows(idx=1, amount=4) print_rows() ('hello', 'world!') workbook.save(filename="C:/Users/Shalki/Desktop/python/display6_append.xlsx") ``` **Reading/Creating/Renaming Excel Worksheets** ```python from openpyxl import load_workbook workbook = load_workbook(filename="C:/Users/Shalki/Desktop/python/display1.xlsx") sheet = workbook.active workbook.sheetnames ['Products', 'Company Sales'] # You can select a sheet using its title products_sheet = workbook["Products"] sales_sheet = workbook["New Sales"] products_sheet["C3"] = "writing ;)" #renaming a worksheet sales_sheet.title = "New Sales1" # You can also define the position to create the sheet at hr_sheet = workbook.create_sheet("HR", 2) workbook.save(filename="C:/Users/Shalki/Desktop/python/display1.xlsx") ``` **Adding Filters:** Filters and sorts can only be configured by openpyxl but will need to be applied in applications like Excel. This is because they actually rearranges or format cells or rows in the range. ```python from openpyxl import load_workbook workbook = load_workbook(filename="C:/Users/Shalki/Desktop/python/display1.xlsx") sheet = workbook.active workbook.sheetnames ['Products', 'Company Sales'] # You can select a sheet using its title products_sheet = workbook["New Sales1"] #'A1:F10' products_sheet.auto_filter.ref = products_sheet.dimensions products_sheet.auto_filter.add_filter_column(1, ["Payal", "Kunal"]) workbook.save(filename="C:/Users/Shalki/Desktop/python/display3.xlsx") products_sheet.auto_filter.add_sort_condition("B2:B15") workbook.save(filename="C:/Users/Shalki/Desktop/python/display1.xlsx") ``` **Excel Formulas** ```python from openpyxl.utils import FORMULAE from openpyxl import load_workbook workbook = load_workbook(filename="C:/Users/Shalki/Desktop/python/display1.xlsx") HR_sheet = workbook["HR"] HR_sheet["B10"] = "=AVERAGE(B2:B6)" #HR_sheet["A7"] = '=SUMIF(A1:A6,"aa",B1:B6)' HR_sheet["A7"] = '=COUNTIF(B1:B5, ">100")' workbook.save(filename="C:/Users/Shalki/Desktop/python/display1.xlsx") ``` **Adding Charts** ```python from openpyxl import Workbook from openpyxl.chart import BarChart, Reference workbook = load_workbook(filename="C:/Users/Shalki/Desktop/python/display1.xlsx") HR_sheet = workbook["HR"] chart = BarChart() data = Reference(worksheet=HR_sheet, min_row=1, max_row=5, min_col=1, max_col=2) chart.add_data(data, titles_from_data=False) HR_sheet.add_chart(chart, "E2") workbook.save(filename="C:/Users/Shalki/Desktop/python/display1.xlsx") ``` Ref:- --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://gyansetu-python.gitbook.io/python-programming/intergation-with-various-datasources.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.