Print Dictionary to CSV file with Python

Question

I would like to print my dictionary TimeSheet to my CSV file. However it is only writing the last row into my CSV file. How can I fix this? I am able to print all the contents from my TimeSheet in console but not all of the dictionary prints to CSV.

import glob
import openpyxl
import csv
#loops through .xlsx files in folder path
path = 'C:/ExcelFolder/*.xlsx'
files = glob.glob(path)
for file in files:
    #selects specific cells in title sheet.
    wb = openpyxl.load_workbook(file)
    sheet = wb.get_sheet_by_name('Sheet2')
    Week = sheet.cell(row=1, column=1).value
    Date = sheet.cell(row=2, column=1).value
    Name = sheet.cell(row=4, column=2).value
    Title = sheet.cell(row=5, column=2).value
    Site = sheet.cell(row=6, column=2).value
    LocID = sheet.cell(row=7, column=2).value
    for n in range(2, 9):
        sheets = wb.worksheets[n]
        Days = wb.worksheets[n]
        for i in range(1, 57):
            From = sheets.cell(row=i, column=1).value
            To = sheets.cell(row=i, column=2).value
            Activity = sheets.cell(row=i, column=3).value
            TimeSheet = {'Week': Week, 'Date': Date, 'Name': Name, 'Title': Title, 'Site': Site, 'LocID': LocID, 'Days': Days, 'From': From, 'To': To, 'Activity': Activity}
            with open('TestOutput.csv', 'w') as csvfile:
                TimeSheet = {'Week': Week, 'Date': Date, 'Name': Name, 'Title': Title, 'Site': Site, 'LocID': LocID,
                             'Days': Days, 'From': From, 'To': To, 'Activity': Activity}
                fieldnames = ['Week', 'Date', 'Name', 'Title', 'Site', 'LocID', 'Days', 'From', 'To', 'Activity']
                writer = csv.DictWriter(csvfile, fieldnames=fieldnames)
                writer.writeheader()
                writer.writerow(
                    {'Week': Week, 'Date': Date, 'Name': Name, 'Title': Title, 'Site': Site, 'LocID': LocID, 'Days': Days, 'From': From, 'To': To, 'Activity': Activity})

                print(TimeSheet)

Console output:

{'Site': 'moon LV-426', 'Activity': None, 'From': datetime.time(18, 45), 'Title': 'Private Hudson', 'Week': 'Week 3', 'To': datetime.time(19, 0), 'Days': <Worksheet "Saturday">, 'Name': 'Bill Paxton', 'Date': '2016/5/22-2016/5/28', 'LocID': '4220A'}
{'Site': 'moon LV-426', 'Activity': None, 'From': datetime.time(19, 0), 'Title': 'Private Hudson', 'Week': 'Week 3', 'To': datetime.time(19, 15), 'Days': <Worksheet "Saturday">, 'Name': 'Bill Paxton', 'Date': '2016/5/22-2016/5/28', 'LocID': '4220A'}
{'Site': 'moon LV-426', 'Activity': None, 'From': datetime.time(19, 15), 'Title': 'Private Hudson', 'Week': 'Week 3', 'To': datetime.time(19, 30), 'Days': <Worksheet "Saturday">, 'Name': 'Bill Paxton', 'Date': '2016/5/22-2016/5/28', 'LocID': '4220A'}
{'Site': 'moon LV-426', 'Activity': None, 'From': datetime.time(19, 30), 'Title': 'Private Hudson', 'Week': 'Week 3', 'To': datetime.time(19, 45), 'Days': <Worksheet "Saturday">, 'Name': 'Bill Paxton', 'Date': '2016/5/22-2016/5/28', 'LocID': '4220A'}

CSV output:

Answer 1

As stated in earlier responses, create the CSV file before hand.

If you wish for a single csv to consolidate all the data from the excel file(s), the solution from DAXaholic should work.

If you wish to have a csv file for each excel file, the following may help:

import glob
import openpyxl
import csv
# loops through .xlsx files in folder path
path = 'C:/ExcelFolder/*.xlsx'
files = glob.glob(path)
fieldnames = ['Week', 'Date', 'Name', 'Title', 'Site', 'LocID', 'Days', 'From', 'To', 'Activity']
for file in files:
    # selects specific cells in title sheet.
    wb = openpyxl.load_workbook(file)
    sheet = wb.get_sheet_by_name('Sheet2')
    Week = sheet.cell(row=1, column=1).value
    Date = sheet.cell(row=2, column=1).value
    Name = sheet.cell(row=4, column=2).value
    Title = sheet.cell(row=5, column=2).value
    Site = sheet.cell(row=6, column=2).value
    LocID = sheet.cell(row=7, column=2).value

    # append the extension .csv to the current filename
    csvfilename = "{}.csv".format(file)
    with open(csvfilename, 'w') as csvfile:
        writer = csv.DictWriter(csvfile, fieldnames=fieldnames)
        writer.writeheader()
        for n in range(2, 9):
            sheets = wb.worksheets[n]
            Days = wb.worksheets[n]
            for i in range(1, 57):
                From = sheets.cell(row=i, column=1).value
                To = sheets.cell(row=i, column=2).value
                Activity = sheets.cell(row=i, column=3).value
                TimeSheet = {'Week': Week, 'Date': Date, 'Name': Name, 'Title': Title, 'Site': Site, 'LocID': LocID, 'Days': Days, 'From': From, 'To': To, 'Activity': Activity}
                writer.writerow(TimeSheet)
                print(TimeSheet)

Answer 2

The issue is 'TestOutput.csv' is opened for each row with 'w' mode, which will truncate the file (see https://docs.python.org/3/library/functions.html#open). It only writes the last row because all the others were deleted.

At a glance, you need to move the call to open() and writeheader() before iterating over the list of files.

Answer 3

The problem is probably that you recreate the CSV file for each iteration.
It should work when you move the creation of the CSV file incl. heading line out of the inner loop like so:

import glob
import openpyxl
import csv

#loops through .xlsx files in folder path

with open('TestOutput.csv', 'w') as csvfile:
    fieldnames = ['Week', 'Date', 'Name', 'Title', 'Site', 'LocID', 'Days', 'From', 'To', 'Activity']
    writer = csv.DictWriter(csvfile, fieldnames=fieldnames)
    writer.writeheader()

    path = 'C:/ExcelFolder/*.xlsx'
    files = glob.glob(path)    
    for file in files:
        #selects specific cells in title sheet.
        wb = openpyxl.load_workbook(file)
        sheet = wb.get_sheet_by_name('Sheet2')
        Week = sheet.cell(row=1, column=1).value
        Date = sheet.cell(row=2, column=1).value
        Name = sheet.cell(row=4, column=2).value
        Title = sheet.cell(row=5, column=2).value
        Site = sheet.cell(row=6, column=2).value
        LocID = sheet.cell(row=7, column=2).value
        for n in range(2, 9):
            sheets = wb.worksheets[n]
            Days = wb.worksheets[n]
            for i in range(1, 57):
                From = sheets.cell(row=i, column=1).value
                To = sheets.cell(row=i, column=2).value
                Activity = sheets.cell(row=i, column=3).value
                TimeSheet = {'Week': Week, 'Date': Date, 'Name': Name, 'Title': Title, 'Site': Site, 'LocID': LocID, 'Days': Days, 'From': From, 'To': To, 'Activity': Activity}                                                     
                writer.writerow(
                    {'Week': Week, 'Date': Date, 'Name': Name, 'Title': Title, 'Site': Site, 'LocID': LocID, 'Days': Days, 'From': From, 'To': To, 'Activity': Activity})
                print(TimeSheet)