Use a Dictionary generated from CSV to update values in .xlsx file using python
<p>Every day I am given an old Excel 95 file of data. It has columns up to the letter 'L' and hundreds of rows. 
Column 'B' is a customer reference number, it's unique to each customer.
Column 'I' is a dollar value. </p>

<p>Each morning I need to use this file to update another Excel file. I search for the customer reference number, and enter the dollar value. The columns for the Customer reference and Dollar value in the Destination file are 'D' and 'E' respectively. </p>

<p>As I'm new to python I thought this would be a good project to automate. After research and installation of a few other modules (and reading the Automate the Boring stuff with python book) I haven't yet had any success. </p>

<p>I'm saving the source file as a csv, then importing csv, then turning it into a dictionary in python. This works fine and I'm happy with it.</p>

<p>My problem is trying to write the values to the destination file. I get no errors, the file saves at the end, but when I open it the values are not there. I'm not sure why. Any help is appreciated. </p>

<pre><code>import os, csv, openpyxl

os.chdir('nameOfMyDirectory')

print('What is the name of the source file?')
sourceFileName = input()

print('What is the key column?')
keyColumn = int(input())

print('What is the value column?')
valueColumn = int(input())

source = csv.reader(open(sourceFileName + '.csv'))
sourceDictionary = {}
for row in source:
    key = row[keyColumn]
    sourceDictionary[key] = row[8]
</code></pre>

<p>Everthing above here seems to work, I can use the interactive shell to print the dollar values using the key column. I've included it incase I'm wrong and this needs to change too. </p>

<p>Below is where the problem seems to be (I think)</p>

<pre><code>print('What is the destination file name?')
destinationFileName = input()

wb = openpyxl.load_workbook(destinationFileName + '.xlsx')
sheet = wb.get_sheet_by_name('nameOfMySheet')

allRows = sheet.max_row

for rowNum in range(2, allRows): #Start from row 2 and go to the end
    membershipID = sheet.cell(row=rowNum, column=4).value
    if membershipID in sourceDictionary:
        sheet.cell(row=rowNum, column=5).value = sourceDictionary[membershipID]


wb.save('newFileName.xlsx')
</code></pre>

<p>The program runs and there are no errors. The new file is saved with the filename but the values are not updated, they are still blank.</p>

<p>I wasn't sure if the openpyxl column = 4 value was one of those ones that started from 1 or 0, so I've tried counting from both, and both don't work.</p>

<p>Any help is super appreciated. </p>

<hr>

<p>I've been asked to show some example data so here is a link to 2 google sheets that are identical to the source file csv and destination file xlsx. Hope this helps. <a href="https://drive.google.com/drive/folders/0Bz9zCfS5szC4RVFvdEg3VGF6T1U?usp=sharing" rel="nofollow">Link to sheets</a></p>

<p>I'm trying to update the 'Destination File's Column E', with the 'Source Files Column I'. I use the customer ID column as the reference. </p>

<p>Simply the problem is a type mismatch. The keys read in from csv are in <code>string</code> format and the values read in from the Excel sheet are <code>float</code> values. So the <code>if</code> statement to match the two always return <em>false</em>.</p>

<p>To align the data types, adjust following line in second <code>for</code> loop where first you cast to integer and then to string:</p>

<pre><code>membershipID = str(int(sheet.cell(row=rowNum, column=4).value))
</code></pre>

python python-3.x csv dictionary openpyxl