Working with CSV Files

Reading and Writting Rows

Use the csv module to stream rows as lists:

import csv

with open('sample.csv', newline='') as fh:
    rdr = csv.reader(fh, delimiter=',', quotechar='"')
    for rec in rdr:
        print('|'.join(rec))

with open('out.csv', 'w', newline='') as fh:
    wrt = csv.writer(fh, delimiter=',', quotechar='"', quoting=csv.QUOTE_MINIMAL)
    wrt.writerow(['red', 'green', 'blue'])
    wrt.writerows([['#FF0000', '#00FF00', '#0000FF']])

Dictionary-based Access

DictReader and DictWriter map rows to dictionaries keyed by column names:

import csv

# reading
with open('people.csv') as fh:
    for row in csv.DictReader(fh):
        print(row['first'], row['age'])

# writing
with open('people.csv', 'w', newline='') as fh:
    cols = ['first', 'last', 'age']
    wrt = csv.DictWriter(fh, fieldnames=cols)
    wrt.writeheader()
    wrt.writerow({'first': 'Ada', 'last': 'Lovelace', 'age': 36})

Dialect attributes:

• delimiter – single character separating fields (default ,)
• lineterminator – row terminator (default \r\n)
• skipinitialspace – ignore spaces after delimiter (default False)

Parsing XML

Loading and Inspecting

from xml.etree import ElementTree as ET

# from string
doc = ET.fromstring('<root><item id="1">text</item></root>')

# from file
tree = ET.parse('data.xml')
root = tree.getroot()

root.tag                 # 'root'
root.attrib              # {}
child = root[0]
child.get('id')          # '1'
child.text               # 'text'

Querying

# direct children
first = root.find('item')
all_items = root.findall('item')

# recursive search
all_nodes = list(tree.iter())
items = list(tree.iter('item'))

# XPath-like
filtered = root.findall('.//item[@id]')

# text extraction
raw = ''.join(root.itertext())
clean = ' '.join(t for t in root.itertext() if t.strip())

Building XML

Creating Elmeents

from xml.etree.ElementTree import Element, SubElement, tostring, ElementTree

root = Element('catalog')
root.set('version', '1.0')
root.text = '\n'

book = SubElement(root, 'book', isbn='978-0-123456-78-9')
title = SubElement(book, 'title')
title.text = 'Python Cookbook'

print(tostring(root, encoding='unicode'))

Removing Content

title.text = None  # clears inner text
book.remove(title) # removes child element

Persisting to Disk

tree = ElementTree(root)
tree.write('catalog.xml', encoding='utf-8', xml_declaration=True)

Excel Spreadsheets

Reading Data

import xlrd

wb = xlrd.open_workbook('scores.xlsx')
sheet = wb.sheet_by_index(0)

rows, cols = sheet.nrows, sheet.ncols
value = sheet.cell_value(1, 2)          # row 1, col 2
cell = sheet.cell(0, 0)
ctype = cell.ctype                      # 1 = text, 2 = number

row_vals = sheet.row_values(1, start_colx=1)  # skip first column

Writing Data

import xlwt

wb = xlwt.Workbook()
ws = wb.add_sheet('Results')

for r, row in enumerate(data):
    for c, val in enumerate(row):
        ws.write(r, c, val)

wb.save('output.xls')

Extending Existing Sheets

import xlrd, xlwt
from xlutils.copy import copy

rb = xlrd.open_workbook('demo.xlsx')
rs = rb.sheet_by_index(0)

wb = copy(rb)
ws = wb.get_sheet(0)

# add total column
last_col = rs.ncols
ws.write(0, last_col, 'Total')

for r in range(1, rs.nrows):
    total = sum(rs.row_values(r, start_colx=1))
    ws.write(r, last_col, total)

wb.save('demo_with_total.xls')