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:

DAXaholic · Accepted Answer · 2016-06-15 04:43:02Z

1

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)

answered Jun 15, 2016 at 4:43

DAXaholic

35.9k6 gold badges84 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

wisenhiemer Over a year ago

I see your point, however when I moved the open call to the inner loop nothing prints or writes to csv file.

DAXaholic Over a year ago

I am not talking about moving it to the inner loop but out of the loop at all

wisenhiemer Over a year ago

Ah I see now, Thank you.

Chris Ferguson · Accepted Answer · 2016-06-15 04:43:39Z

1

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.

answered Jun 15, 2016 at 4:43

Chris Ferguson

1111 bronze badge

Comments

ABC · Accepted Answer · 2016-06-15 04:55:53Z

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)

Collectives™ on Stack Overflow

Print Dictionary to CSV file with Python

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related