script to help users format their CSV transactions for quickbooks

bank2qb.py

import csv
import re
import datetime
from collections import defaultdict

# Ask user for input
input_file = input("Enter the input file name (including .csv extension): ")
output_file_base = input("Enter the base of the output file name (without extension): ")
account_type_header = input("Enter the header for the account type column: ")
date_header = input("Enter the header for the date column: ")
amount_header = input("Enter the header for the amount column: ")

# Ask user for the number of description fields
num_description_fields = int(input("Enter the number of description fields: "))

# Ask user for each description header
description_headers = []
for i in range(num_description_fields):
    description_headers.append(input(f"Enter the header for description column {i+1}: "))

# Open the input CSV file
with open(input_file, 'r') as csv_file:
    reader = csv.DictReader(csv_file, delimiter=',')
    
    # Segregate data based on account type
    data_by_account_type = defaultdict(list)
    for row in reader:
        data_by_account_type[row[account_type_header]].append(row)

# Process each account type separately
for account_type, data in data_by_account_type.items():
    output_file = f"{output_file_base}_{account_type}.csv"
    with open(output_file, 'w', newline='') as output:
        writer = csv.writer(output, delimiter=',')

        # Write the header row for the new CSV file
        writer.writerow(['Date', 'Description', 'Amount'])

        # Loop through each row in the account type data
        for row in data:
            # Get the values for the date and amount columns
            date = row[date_header]
            amount = row[amount_header]

            # Get the values for the description columns and join them
            descriptions = []
            for header in description_headers:
                description = row[header]
                # Remove any characters that are not letters or spaces
                description = re.sub('[^A-Za-z ]+', '', description)
                descriptions.append(description)
            description = " ".join(descriptions).strip()  # strip leading/trailing spaces

            # Determine the original date format
            formats = ['%d/%m/%Y', '%d-%m-%Y', '%d.%m.%Y', '%Y/%m/%d', '%Y-%m-%d', '%Y.%m.%d']
            for fmt in formats:
                try:
                    parsed_date = datetime.datetime.strptime(date, fmt)
                    if parsed_date.day > 12:
                        date_format = fmt
                    else:
                        date_format = '%m/%d/%Y'
                    break
                except ValueError:
                    continue
            else:
                # default date format if none of the formats match
                date_format = '%m/%d/%Y'

            # Convert the date format to dd/mm/yyyy
            date = datetime.datetime.strptime(date, date_format).strftime('%d/%m/%Y')

            # Write the formatted values to the new CSV file
            writer.writerow([date, description, amount])