pyProcess-CSV-files-example

# Read two CSV files and combine

dvd_dict = {}

f1 = open("File1.csv")
hdr = f1.readline()     # read the header line

# We could identify the various columns here
idx_id, idx_prod, idx_title, idx_genre = 99, 99, 99, 99
hdr_upper = hdr.upper()
split_hdr = hdr_upper.split(",")
line_ctr = 1
for idx, field in enumerate(split_hdr):
    if "ID" in field: idx_id = idx
    if "PROD" in field: idx_prod = idx
    if "TITLE" in field: idx_title = idx
    if "GENRE" in field: idx_genre = idx
# -------------------------------------------------

# Now read the data lines
for line in f1:
    line = line.strip()
    print(line)
    line_ctr += 1
    split_line = line.split(",")
    dvd_id = split_line[idx_id]
    dvd_title = split_line[idx_title]
    dvd_prod = split_line[idx_prod]
    dvd_genre = split_line[idx_genre]
    if dvd_id in dvd_dict:
        print("Duplicate DVD ID on line: ", line_ctr, line)
        continue
    dvd_dict[dvd_id] = [dvd_title, dvd_prod, dvd_genre]

f1.close()
print("File1 Finished reading", line_ctr, "lines. Dictionary created")
f2 = open("File2.csv")

line_ctr = 0
for line in f2:
    line = line.strip()
    line_ctr += 1
    split_line = line.split(",")
    # No header on this file, so we have to presume the column identities
    dvd_id = split_line[0]
    dvd_sales_id = split_line[1]
    dvd_date_stocked = split_line[2]
    dvd_date_sold = split_line[3]

    if dvd_id not in dvd_dict:
        print("Unknown DVD ID on 2nd file, line: ", line_ctr, line)
        continue

    dvd_dict[dvd_id].extend([dvd_sales_id, dvd_date_stocked, dvd_date_sold])

f2.close()
print("File2 Finished reading", line_ctr, "lines. Dictionary updated")
f3 = open("File3.csv", 'w')
line_ctr = 0
dvd_keys = list(dvd_dict.keys())
dvd_keys.sort()
for key in dvd_keys:
    line_ctr += 1
    dvd_data = dvd_dict[key]
    lineout = key + "," + ",".join(dvd_data)
    f3.write(lineout + "\n")

f3.close()
print("File3 Finished writing: ", line_ctr, "lines. ")