[Combine multiple CSV files into Excel sheets] Simple Python 3 script to combine multiple csv files, e.g. dumped from SQL into a single Excel file with one sheet per CSV #excel #csv #xlsxwriter

csv-to-xlsx.py

import os
import glob
import argparse
import csv
import xlsxwriter

def writeToXlsx(inputFiles, workbookFilename, encoding, delimiter, quotechar):
    print("Creating workbook {}".format(workbookFilename))
    workbook = xlsxwriter.Workbook(workbookFilename)

    for csvFile in inputFiles:
        (sheetName, _ext) = os.path.splitext(os.path.basename(csvFile))
        # Max length of a sheet name is 31 chars
        sheetName = (sheetName[:28] +
                     '..') if len(sheetName) > 28 else sheetName

        worksheet = workbook.add_worksheet(sheetName)

        rowCount = 0
        with open(csvFile, 'rt', encoding=encoding) as f:
            reader = csv.reader(f, delimiter=delimiter, quotechar=quotechar, doublequote=True)
            for r, row in enumerate(reader):
                rowCount+=1
                for c, col in enumerate(row):
                    worksheet.write(r, c, col)

        print("Added sheet \"{}\" with {} row(s)".format(sheetName, rowCount))

    workbook.close()


def main():
    parser = argparse.ArgumentParser(
        description='Combine multiple CSV files into Excel workbook as sheets.'
    )
    parser.add_argument('--input-dir', dest='input', required=True,
                    help='Path to directory containing csv files')
    parser.add_argument('--out', dest='output', default='Combined.xlsx',
                    help='Name of output file (default: Combined.xlsx)')
    parser.add_argument('--encoding', dest='encoding', default='utf-8',
                    help='Encoding of csv files (default: utf-8')
    parser.add_argument('--delimiter', dest='delimiter', default=',',
                        help='Delimiter in csv files (default: ,')
    parser.add_argument('--quote-char', dest='quotechar', default='"',
                        help='Quote character in csv files (default: "')
    args = parser.parse_args()

    inputDir = args.input
    outFile = args.output

    print("Joining CSV files from: \n" + inputDir + "\ninto: " + outFile)

    inputFiles = sorted(glob.glob(inputDir + '/' + "*.csv"))
    print("{} CSV files found:\n {}".format(len(inputFiles), inputFiles))

    writeToXlsx(inputFiles, outFile, args.encoding, args.delimiter, args.quotechar)

    print("Done!")

if __name__ == "__main__":
    main()