generate CSV files from OASIS tables

generate_csv.py

#!/usr/local/bin/python


from collections import OrderedDict
import csv
import re


csv_file_names = {
    'oasis/Pool 1.csv': 'oasis/OASIS_Unrestricted_Contractors_by_Pool - POOL 1.csv',
    'oasis/Pool 2.csv': 'oasis/OASIS_Unrestricted_Contractors_by_Pool - POOL 2.csv',
    'oasis/Pool 3.csv': 'oasis/OASIS_Unrestricted_Contractors_by_Pool - POOL 3.csv',
    'oasis/Pool 4.csv': 'oasis/OASIS_Unrestricted_Contractors_by_Pool - POOL 4.csv',
    'oasis/Pool 5A.csv': 'oasis/OASIS_Unrestricted_Contractors_by_Pool - POOL 5A.csv',
    'oasis/Pool 5B.csv': 'oasis/OASIS_Unrestricted_Contractors_by_Pool - POOL 5B.csv',
    'oasis/Pool 6.csv': 'oasis/OASIS_Unrestricted_Contractors_by_Pool - POOL 6.csv',
    'oasissb/Pool 1.csv': 'oasissb/OASIS_SB_Contractors_by_Pool - POOL 1.csv',
    'oasissb/Pool 2.csv': 'oasissb/OASIS_SB_Contractors_by_Pool - POOL 2.csv',
    'oasissb/Pool 3.csv': 'oasissb/OASIS_SB_Contractors_by_Pool - POOL 3.csv',
    'oasissb/Pool 4.csv': 'oasissb/OASIS_SB_Contractors_by_Pool - POOL 4.csv',
    'oasissb/Pool 5A.csv': 'oasissb/OASIS_SB_Contractors_by_Pool - POOL 5A.csv',
    'oasissb/Pool 5B.csv': 'oasissb/OASIS_SB_Contractors_by_Pool - POOL 5B.csv',
    'oasissb/Pool 6.csv': 'oasissb/OASIS_SB_Contractors_by_Pool - POOL 6.csv',
}

def createFieldsDict(pool_number):
    fields = OrderedDict()

    if is_oasis:
        for field in [
            'OASIS Pool ' + pool_number + ' Contractor Name',
            'OASIS Pool ' + pool_number + ' Contract Number'
        ]:
            fields[field] = ''
    elif is_oasissb:
        for field in [
            'OASIS SB Pool ' + pool_number + ' Contractor Name',
            'OASIS SB Pool ' + pool_number + ' Contract Number'
        ]:
            fields[field] = ''

    for field in [
        'DUNS Number',
        # 'FSS Contract Number', # not present in OASIS data
        'COCM Name',
        'COCM Phone',
        'COCM Email',
        'COPM Name',
        'COPM Phone',
        'COPM Email'
    ]:
        fields[field] = ''

    return fields

for output_file_name, input_file_name in csv_file_names.iteritems():
    input_file = open(input_file_name, 'r')
    input_file_reader = csv.reader(input_file)

    output_file = open(output_file_name, 'w')
    output_file_writer = csv.writer(output_file)

    pool_number = re.search(r'Pool (?P<pool_number>.*)\.csv$', output_file_name).group('pool_number')

    is_oasis = re.match('oasis/', output_file_name)
    is_oasissb = re.match('oasissb/', output_file_name)

    fields = createFieldsDict(pool_number)

    # Write the header.
    output_file_writer.writerow(fields.keys())

    # Skip the header.
    input_file_reader.next()

    row_index = 0

    for row in input_file_reader:
        if row_index % 3 == 0:
            if is_oasis:
                fields['OASIS Pool ' + pool_number + ' Contractor Name'] = row[1]
                fields['DUNS Number'] = row[4]
            elif is_oasissb:
                fields['OASIS SB Pool ' + pool_number + ' Contractor Name'] = row[1]

                master_file = open('oasissb/OASIS_SB_Contractors_by_Pool - OASIS SB Contractors.csv', 'r')
                master_file_reader = csv.reader(master_file)

                for master_row in master_file_reader:
                    if fields['OASIS SB Pool ' + pool_number + ' Contractor Name'] in master_row:
                        fields['DUNS Number'] = master_row[10]

                master_file.close()

            fields['COCM Name'] = row[2]
            fields['COPM Name'] = row[3]
        elif row_index % 3 == 1:
            if is_oasis:
                fields['OASIS Pool ' + pool_number + ' Contract Number'] = row[0]
            elif is_oasissb:
                fields['OASIS SB Pool ' + pool_number + ' Contract Number'] = row[0]

            fields['COCM Phone'] = row[2]
            fields['COPM Phone'] = row[3]
        elif row_index % 3 == 2:
            fields['COCM Email'] = row[2]
            fields['COPM Email'] = row[3]

            output_file_writer.writerow(fields.values())

            fields = createFieldsDict(pool_number)

        row_index += 1

    input_file.close()
    output_file.close()