Python: CSV files

A CSV (Comma Separated Values) file is often used to store table values in a somewhat portable text format.

CSV files vary as to exact format but some parameters are fairly standard.

A CSV file typical represents a table - a flat database of cells organized into rows and columns. The following are important for a CSV file.

The field delimiter such as tab, comma, etc.
The quote character such as the double quote, singe quote, etc.
The escape character such as the backslash, etc.

The usual structure of a CSV file is as follows.

Each line or row is a record of the table.
Each record has fields delimited by a special character.
The first record contain the field headers or field names.

Many programs, such as Excel, have options to import and/or export data in CSV format.

Excel has a Wizard to help with the import. Whenever custom handling of CSV files is required, a language such as Python can be used.

The csv module is required.

import csv

Here is the table used for example purposes.

tableList1 = [
	["r0c1", "r0c2", "r0c3"],
	["r1c1", "r1c2", "r1c3"],
	["r2c1", "r2c2", "r2c3"],
	["r3c1", "r3c2", "r3c3"],
	]

Some required parameters can be assigned as variables.

csvFs1 = "./data1.csv"
delimiter1 = ","
quote1 = "\""

Some others are omitted. Defaults used.

It is best to have prepared a complete table (list of list of cells). If the table is too large, one can construct and write one row at a time.

f1 = open(csvFs1,"w",newline="")
writer1 = csv.writer(f1,delimiter=delimiter1,quotechar=quote1)
writer1.writerows(tableList1)
f1.close()

To see what is written, the created csv file can be read as text.

f1 = open(csvFs1,"r")
text1 = f1.read()
print("\"{0:s}\"".format(text1))

The output of the text using quotes is to see what was added by the csv call. In this case, a newline at the end.

The csv file can also be read and processing using the csv module. This is preferred as the delimiters, quotes, etc., are handled automatically.

f1 = open(csvFs1,"r")
reader1 = csv.reader(f1,delimiter=delimiter1,quotechar=quote1)
# eager evaluation of reader1 to list
tableList1 = list(reader1)
f1.close()

In this case, the complete table is forced (eager evaluation) so that the file can be closed and processing done elsewhere.

The CSV file that has been read can now be read as a 2D table - a list of lists of values.

for rpos1,rowList1 in enumerate(tableList1):
	print("Row {0:d}:".format(rpos1))
	for cpos1,value1 in enumerate(rowList1):
		print("\tCol {0:d}: \"{1:s}\"".format(cpos1,value1))

Here is the Python code [#8]

import csv
tableList1 = [
	["r0c1", "r0c2", "r0c3"],
	["r1c1", "r1c2", "r1c3"],
	["r2c1", "r2c2", "r2c3"],
	["r3c1", "r3c2", "r3c3"],
	]
# parameters
csvFs1 = "./data1.csv"
delimiter1 = ","
quote1 = "\""
# write new CSV file
print("Write \"{0:s}\" as csv.".format(csvFs1))
f1 = open(csvFs1,"w",newline="")
writer1 = csv.writer(f1,delimiter=delimiter1,quotechar=quote1)
writer1.writerows(tableList1)
f1.close()
# read created CSV file as text
print("")
print("Read \"{0:s}\". Text:".format(csvFs1))
print("")
f1 = open(csvFs1,"r")
text1 = f1.read()
print("\"{0:s}\"".format(text1))
# read created CSV file as CSV
print("")
print("Read \"{0:s}\" as csv.".format(csvFs1))
print("")
f1 = open(csvFs1,"r")
reader1 = csv.reader(f1,delimiter=delimiter1,quotechar=quote1)
# eager evaluation of reader1 to list
tableList1 = list(reader1)
f1.close()
# show the CSV table
for rpos1,rowList1 in enumerate(tableList1):
	print("Row {0:d}:".format(rpos1))
	for cpos1,value1 in enumerate(rowList1):
		print("\tCol {0:d}: \"{1:s}\"".format(cpos1,value1))

Here is the output of the Python code.

Write "./data1.csv" as csv.
Read "./data1.csv". Text:
"r0c1,r0c2,r0c3
r1c1,r1c2,r1c3
r2c1,r2c2,r2c3
r3c1,r3c2,r3c3
"
Read "./data1.csv" as csv.
Row 0:
	Col 0: "r0c1"
	Col 1: "r0c2"
	Col 2: "r0c3"
Row 1:
	Col 0: "r1c1"
	Col 1: "r1c2"
	Col 2: "r1c3"
Row 2:
	Col 0: "r2c1"
	Col 1: "r2c2"
	Col 2: "r2c3"
Row 3:
	Col 0: "r3c1"
	Col 1: "r3c2"
	Col 2: "r3c3"

Note that csv files are stored and read as text. When it is important that certain data be numeric fields, explicit conversion is necessary.

In relational database terms, a not-flat file or table is a table that has references to other tables.

One way a csv file might represent a pair of tables is as follows.