Is there a way to append an existing xlsx worksheet to add data from a text file ?
I have an excel file for which I need to manipulate the first worksheet based on a text file.
I match the text file to the xlsx and write the 'Scores' column in the xlsx sheet and save the workbook.
For those 'Pos' not present in the the text file, the excel sheet would have to print 'Unknown' with current date for it.
The tab-text file looks like this
Pos Ref Alt Score
44 a bb 1
57 c ab 4
64 d d 5
and the excel sheet has several columns
Col1.. Col2.. Pos Ref Alt ... Score... Col26
id2.. 57 c ab... ...
id3.. 64 d d... ...
id4.. 103 e g ... ...
So the output will look like
Col1.. Col2.. Pos Ref Alt ... Score... Col26
id2.. 57 c ab... 4 ...
id3.. 64 d d... 5 ...
id4.. 103 e g ... Unknown_June2017 ...
What I am currently doing is converting the worksheet to text file, comparing the two text files and then writing the result text file back to excel workbook. As there are several excel files , this is a bit inefficient. Any help is appreciated. Thank you.
The code is very long, I have attached the relevant part where I am trying to copy the contents of the text file into the excel worksheet. But using this as well, I end up with a blank worksheet.
#!/usr/bin/python
import os
from openpyxl.reader.excel import load_workbook
import csv
from openpyxl.drawing.image import Image
import PIL
xl_directory = r'/home/test'
txt_directory = r'/home/test'
for xl_root, xl_dirs, xl_files in os.walk(xl_directory):
for xl_file in xl_files:
if xl_file.endswith('.xlsx'):
xl_abs_file = os.path.join(xl_root, xl_file)
wb = load_workbook(xl_abs_file, data_only=True)
ws = wb.get_sheet_by_name('Unannotated')
##clear the contents of the file
for row in ws['A4:U1000']:
for cell in row:
cell.value = None
image = Image('/home/logo3.jpg')
ws.add_image(image, 'A1')
## go through text file and write data on worksheet
for txt_root, txt_dirs, txt_files in os.walk(txt_directory):
for txt_file in txt_files:
if txt_file == xl_file.replace('xlsx', 'txt'):
with open(os.path.join(txt_root, txt_file)) as fh:
reader = csv.reader(fh, delimiter='\t')
[next(reader) for skip in range(1)]
for row in reader:
ws.append(row)
wb.save(xl_abs_file)
#!python
import os
from openpyxl import load_workbook
from datetime import datetime
# Variables
sheet_directory = r'<path_of_Excel_files>'
text_directory = r'<path_of_text_files>'
# Subroutines
def get_text_data(txt_filename):
dict_pos = {}
first_line = True
for text_root, text_dirs, text_files in os.walk(text_directory):
for text_file in text_files:
if text_file == txt_filename:
# A matching text file was found
fh = open(os.path.join(text_root, text_file))
for line in fh:
# Skip the header; read the data into the dictionary
if first_line:
first_line = False
continue
line = line.rstrip('\n')
x = line.split('\t')
dict_pos[x[0]] = x[3]
return dict_pos
def process_xl_sheets():
for sheet_root, sheet_dirs, sheet_files in os.walk(sheet_directory):
for sheet_file in sheet_files:
if sheet_file.endswith('.xlsx'):
# Read the corresponding text file from the text_directory and
# populate a dictionary of "Pos" values.
dpos = get_text_data(sheet_file.replace('.xlsx', '.txt'))
sheet_xl_file = os.path.join(sheet_root, sheet_file)
wb = load_workbook(sheet_xl_file, data_only=True)
ws = wb.get_sheet_by_name('rawdata')
# If you already know the columns that have the headers "Pos" and
# "Score", set them here. Otherwise, iterate through the first row
# to determine those columns.
pos_col_no = 'C'
score_col_no = 'F'
row_no = 2
cell = ws[pos_col_no + str(row_no)]
while cell.value:
if str(cell.value) in dpos:
ws[score_col_no + str(row_no)] = dpos[str(cell.value)]
else:
ws[score_col_no + str(row_no)] = 'Unknown_' + datetime.now().strftime("%B") + datetime.now().strftime("%Y")
row_no += 1
cell = ws[pos_col_no + str(row_no)]
wb.save(sheet_xl_file)
# Main section
process_xl_sheets()
A few things that come to mind as I look at the code again:
1) After "dpos" is assigned, you may want to do further processing only if dpos is not empty. Notice that dpos could if empty if a text file corresponding to an Excel file is not found. For such cases, it would be inefficient to work on the Excel spreadsheet at all.
2) In the "get_text_data" subroutine, you may want to process the first row and see if x[0] is "Pos" and x[3] is "Score". If not, then you can avoid processing the text file entirely.
3) If there is no worksheet called "rawdata", then continue to the next iteration of the loop.
3) If there are way too many Excels and text files (say hundreds or thousands or more), then you may want to first create a dictionary of Excel => text files and then iterate through the key/value pairs, processing them one-by-one. The existence of a file can be quickly checked using "os.path.isfile(<filename>)" - this avoids the unnecessary looping through the directory. In fact, coming to think of it, you can refactor the posted code and implement this concept to see if it improves the run time.
But I think i've not explained myself properly. the code I provided was just a more complicated way which did not seem efficient.
There is only one text file for scores with 4 columns (scores.txt) that is constant to compare several excel files. Those columns need to be compared with the 'raw data' worksheet (that also have constant headers and format) and add the scores accordingly.
I tried the code with a bit of manipulation but that also generates a blank workbook. I have attached a small snippet of the input files and expected output.
Just a quick observation: the name of the text file inside your zip file is "scores.txt". The name of the Excel file inside your zip file is "S12.xlsx".
Now, the line in bold red color in your Python code:
#!/usr/bin/python
...
...
xl_directory = r'/home/test'
txt_directory = r'/home/test'
...
...
for txt_root, txt_dirs, txt_files in os.walk(txt_directory):
for txt_file in txt_files:
if txt_file == xl_file.replace('xlsx', 'txt'):
with open(os.path.join(txt_root, txt_file)) as fh:
...
...
looks for a text file that is named the same as an Excel file.
So, if your code finds an Excel file: "/home/test/myfile.xlsx", then it will look for a text file called "/home/test/myfile.txt" and process it.
A similar check in my Python code is below in bold red:
...
...
sheet_directory = r'<path_of_Excel_files>'
text_directory = r'<path_of_text_files>'
# Subroutines
def get_text_data(txt_filename):
dict_pos = {}
first_line = True
for text_root, text_dirs, text_files in os.walk(text_directory):
for text_file in text_files:
if text_file == txt_filename:
# A matching text file was found
...
...
def process_xl_sheets():
...
...
# Main section
process_xl_sheets()
As you can see, neither of the two pieces of code above will work if the Excel and text files are named differently.
1) When you tried my code with a bit of manipulation, did you ensure that it reads the file "scores.txt" and not "S12.txt" for the Excel file "S12.xlsx" ?
2) If you print dict_pos right before it is returned from the function "get_text_data", what do you see?
1) When you tried my code with a bit of manipulation, did you ensure that it reads the file "scores.txt" and not "S12.txt" for the Excel file "S12.xlsx" ?
Yes. its only one file 'scores.txt' that is being used as a reference to get the scores into all excel sheets.
2) If you print dict_pos right before it is returned from the function "get_text_data", what do you see?
It does not return anything
I have pasted the code I used below
import os
from openpyxl import load_workbook
from datetime import datetime
import csv
# Variables
sheet_directory = r'/home/test'
text_directory = r'/home/test'
# Subroutines
def get_text_data(txt_filename):
dict_pos = {}
first_line = True
with open('scores.txt') as txt_filename:
tab_reader = csv.reader(txt_filename, delimiter='\t')
for line in tab_reader:
if first_line:
first_line = False
continue
line = line.rstrip('\n')
x = line.split('\t')
dict_pos[x[0]] = x[3]
#print dict_pos
return dict_pos
def process_xl_sheets():
for sheet_root, sheet_dirs, sheet_files in os.walk(sheet_directory):
for sheet_file in sheet_files:
if sheet_file.endswith('.xlsx'):
dpos = get_text_data(sheet_file.replace('.xlsx', '.txt'))
sheet_xl_file = os.path.join(sheet_root, sheet_file)
wb = load_workbook(sheet_xl_file, data_only=True)
ws = wb.get_sheet_by_name('raw_data')
pos_col_no = 'F'
score_col_no = 'V'
row_no = 4
cell = ws[pos_col_no + str(row_no)]
while cell.value:
if str(cell.value) in dpos:
ws[score_col_no + str(row_no)] = dpos[str(cell.value)]
else:
ws[score_col_no + str(row_no)] = 'Unknown_' + datetime.now().strftime("%B") + datetime.now().strftime("%Y")
row_no += 1
cell = ws[pos_col_no + str(row_no)]
wb.save(sheet_xl_file)
# Main section
process_xl_sheets()
Here's my code for reference. Check the indentation level and the comments:
# Subroutines
def get_text_data(txt_filename):
dict_pos = {}
first_line = True
for text_root, text_dirs, text_files in os.walk(text_directory):
for text_file in text_files:
if text_file == txt_filename:
# A matching text file was found
fh = open(os.path.join(text_root, text_file))
for line in fh:
# Skip the header; read the data into the dictionary
if first_line: # One level inside "for line" loop
first_line = False # One level inside "for line" loop, one level inside "if first_line" branch
continue # One level inside "for line" loop, one level inside "if first_line" branch
line = line.rstrip('\n') # One level inside "for line" loop
x = line.split('\t') # One level inside "for line" loop
dict_pos[x[0]] = x[3] # One level inside "for line" loop
return dict_pos # One level inside "def"; this is at subroutine level
In your code, the value of "first_line" is True the first time it enters the subroutine and subsequently the first line of "tab_reader".
You then set it to False and then everything else is done inside that "if" branch.
From the second line of "tab_reader" onwards, the control will never go inside the "if" branch because it was set to False in the first line.
---------- Post updated at 12:13 PM ---------- Previous update was at 12:08 PM ----------
Also check the level of "wb.save()" in your code. It should be at the same level as "while cell.value".
My hunch is that:
(a) either there are no ".xlsx" files in "/home/test" or
(b) there are ".xlsx" files in "/home/test" but the Python script does not have the privilege to write to them
Print the value of "sheet_file" right after the "if sheet_file.endswith()" condition, run the Python program from the command line and post the result here (select, copy, paste from your terminal window.)
Following your suggestion, now I get these errors
All the files and codes are running in the same directory. I've also made sure the xlsx has permissions.
Traceback (most recent call last):
File "/home/test/annotate.py", line 53, in <module>
process_xl_sheets()
File "/home/test/annotate.py", line 35, in process_xl_sheets
dpos = get_text_data(sheet_file.replace('.xlsx', '.txt'))
File "/home/test/annotate.py", line 23, in get_text_data
line = line.rstrip('\n')
AttributeError: 'list' object has no attribute 'rstrip'
Notice that "line" in your code is a list (or array). But "line" in my code is a string. That's because you are reading your file using csv.reader which returns a reader object. And when you iterate through that reader object, each iterator variable is a list (or array).
It is documented here: 13.1. csv � CSV File Reading and Writing � Python 2.7.13 documentation
To give a concrete example, if a tab-separated file looks like this:
$
$ cat -n countries.txt
1 Continent Country Capital
2 Europe Germany Berlin
3 North America Canada Ottawa
4 Africa Namibia Windhoek
5 Asia Japan Tokyo
$
$
then my code does something like this (check the comments):
>>>
>>>
>>> fh = open('countries.txt')
>>> for line in fh:
... print 'line is a string: ==>|', line, '|<==' # 'line' is a string with a newline character at the end
... line = line.rstrip('\n') # Strip the newline character at the end of the string 'line'
... x = line.split('\t') # Now split the string 'line' on the Tab character ('\t') to obtain the list (or array) 'x'
... print 'x is an array: ==>|', x, '|<==\n\n' # Print the list (or array) 'x'
...
line is a string: ==>| Continent Country Capital
|<==
x is an array: ==>| ['Continent', 'Country', 'Capital'] |<==
line is a string: ==>| Europe Germany Berlin
|<==
x is an array: ==>| ['Europe', 'Germany', 'Berlin'] |<==
line is a string: ==>| North America Canada Ottawa
|<==
x is an array: ==>| ['North America', 'Canada', 'Ottawa'] |<==
line is a string: ==>| Africa Namibia Windhoek
|<==
x is an array: ==>| ['Africa', 'Namibia', 'Windhoek'] |<==
line is a string: ==>| Asia Japan Tokyo
|<==
x is an array: ==>| ['Asia', 'Japan', 'Tokyo'] |<==
>>>
>>>
>>>
And your code does something like this (check the comments):
>>>
>>> import csv
>>> with open('countries.txt') as txt_filename:
... tab_reader = csv.reader(txt_filename, delimiter='\t') # tab_reader is a reader object
... print 'tab_reader is: ==>|', tab_reader, '|<=='
... for line in tab_reader:
... print 'line is an array: ==>|', line, '|<==' # 'line' is a list (or array)
...
tab_reader is: ==>| <_csv.reader object at 0x000000000250D8E8> |<==
line is an array: ==>| ['Continent', 'Country', 'Capital'] |<==
line is an array: ==>| ['Europe', 'Germany', 'Berlin'] |<==
line is an array: ==>| ['North America', 'Canada', 'Ottawa'] |<==
line is an array: ==>| ['Africa', 'Namibia', 'Windhoek'] |<==
line is an array: ==>| ['Asia', 'Japan', 'Tokyo'] |<==
>>>
>>>
So, the inference is that "x" in my code is the same as "line" in your code.
I hope you know how to proceed from here.
Try to form the dictionary "dict_pos" and post your attempt if you cannot make it to work.
But when writing to the excel worksheet, instead of writing the score, all values end up as 'Unknown_June2017'
Also, is it possible to form a dictionary with multiple keys ? For example, I need the first three columns in the 'score.txt' to be associated with the score value and that needs to be compared with column 5,6,7 from the worksheet
My hunch is that you are looking at the wrong column.
If your "pos_col_no" is "F" and "row_no" is 4, then the code will look at cells F4, F5, F6, F7, F8, .... and check if they are keys of dictionary "dpos".
Since you see 'Unknown_June2017' in cells V4, V5, V6, V7, V8, ... it means that the keys are not in column F but in some other column.
Yes, it's possible to form a dictionary with multiple keys.
You can use a special Python data structure called a "tuple" for that.
Elements of tuples have parentheses around them e.g.
('a', 'b', 'c')
is a tuple.
The code can work without parentheses (for the most part) but it's better to specify them in order to avoid ambiguity.
Like so:
>>>
>>>
>>> color_mix = {}
>>>
>>> color_mix['red', 'blue'] = 'purple' # works without parentheses
>>>
>>> color_mix[('blue', 'yellow')] = 'green' # although it's customary to use them
>>>
>>> color_mix['yellow', 'red'] = 'orange'
>>>
>>> for k in color_mix.keys():
... print k
...
('blue', 'yellow')
('red', 'blue')
('yellow', 'red')
>>>
>>>
Thank you.
yes, i was using the wrong column! Now this is my final code
#!/usr/bin/python
import sys
import os
from openpyxl import load_workbook
from datetime import datetime
from pandas import read_table
import csv
from collections import namedtuple
# Variables
sheet_directory = r'/home/test'
# Subroutines
def get_text_data(txt_filename):
dict_pos = {}
Scores = namedtuple("Scores", ["POS", "ALT", "REF"])
first_line = True
with open('/home/test/scores.txt') as txt_filename:
for line in txt_filename:
if first_line:
first_line = False
continue
line = line.rstrip('\n')
x = line.split('\t')
cpos = Scores(POS=x[0], ALT=x[2], REF=x[1])
dict_pos[cpos] = x[3]
print dict_pos
return dict_pos
def process_xl_sheets():
for sheet_root, sheet_dirs, sheet_files in os.walk(sheet_directory):
for sheet_file in sheet_files:
if sheet_file.endswith('.xlsx'):
print(sheet_file)
dpos = get_text_data(sheet_file.replace('.xlsx', '.txt')) ##what exactly is this part doing ? There is only one text file 'score.txt' to be referenced against several xlsx files named S12.xlsx , S13.xlsx etc
sheet_xl_file = os.path.join(sheet_root, sheet_file)
wb = load_workbook(sheet_xl_file, data_only=True)
ws = wb.get_sheet_by_name('raw_data')
pos_col_no = 'E'
alt_col_no = 'G'
ref_col_no = 'F'
score_col_no = 'V'
row_no = 4
#compare = Scores(POS=pos_col_no, ALT=alt_col_no, REF=ref_col_no)
#cell = ws[compare + str(row_no) ]
cell = ws[pos_col_no + alt_col_no + ref_col_no + str(row_no)]
print cell.value ##doesn't print
while cell.value:
if str(cell.value) in dpos:
ws[score_col_no + str(row_no)] = dpos[str(cell.value)]
else:
ws[score_col_no + str(row_no)] = 'Unknown_' + datetime.now().strftime("%B") + datetime.now().strftime("%Y")
row_no += 1
cell = ws[pos_col_no + str(row_no)]
wb.save(sheet_xl_file)
# Main section
process_xl_sheets()
when I run it now, it doesnt print the cell.value. So when I'm attempting to do is making the code to compare the threee columns in the excel file to the three columns in the text file so that it can output its corresponding Score
I haven't gone through the entire code yet, and it might be tricky to test your code since I don't have pandas, but let me answer your question about the function call.
The replace() function replaces the value '.xlsx' to '.txt' in the string variable sheet_file.
The string variable sheet_file holds the name of your Excel file.
So, let's say while looping through the sheet_directory, your Python program finds an Excel file called "S12.xlsx". Then sheet_file will equal "S12.xlsx".
Thereafter, this expression:
sheet_file.replace('.xlsx', '.txt')
replaces '.xlsx' to '.txt' and thereby returns 'S12.txt'.
And then this value 'S12.txt' is passed to the function get_text_data().
That is, the value of the string parameter txt_filename is 'S12.txt'.
You can see this very quickly by printing txt_filename the moment you enter the function.
In the "with" statement inside the function "get_text_data", however, you use the same name txt_filename. That converts the string parameter txt_filename to a file object.
Thereafter, till the end of the function "get_text_data", txt_filename remains a file object.
So essentially, you are not using the txt_filename parameter in your function at all.
My suggestion: don't pass a parameter to a function if you are not using it at all. You anyway have the text file name ("scores.txt") and location hard-coded.
If something is not needed, discard it. Keep it simple.
1) Remove the parameter, not the function call. In other words, do call the function, but don't pass any parameter to it.
2) Change the signature of the function so that it does not accept any parameter.
3) Call the function only once. Calling the same function that reads the same text file and returns the same dictionary every time you find an Excel spreadsheet is extremely inefficient.
There is no cell called "EGF4" in any Excel spreadsheet.
Hence Python cannot print it.
>>> Ok, that's a wrong statement. My bad, sorry. Looks like newer versions of Microsoft Excel do have cell "EGF4".
>>> That may not be the cell you want to print. My guess is that your program is printing "None" instead of nothing.
>>> Your spreadsheet's cell "EGF4" is empty, most likely.