3

Here's the code I've frankensteined together from other posts,

import xlrd
import os.path
wb = xlrd.open_workbook(os.path.join('D:\Data','SPS1 demo data.xlsx'))
wb.sheet_names()
sh = #?
Strings=#variables
i = 1
file = open("Output.txt", "w")
while sh.cell(i,3).value = (Strings):
   file.write(#row)
   i = i + 1
file.close

It's not complete, but what I'm trying to accomplish is a search in column 3(or entire sheet, doesn't matter) for 5 specific strings and output those rows line by line to a text file, if possible csv formatted i.e. commas between each value.

How can I set a variable to 5 possible strings? Would this need to be an array?
I think the way that I have it written here will overwrite the text file each time rather than append it, is that correct? And if so what's the correct function, "file.append(#stuff)"?

Jukie
  • 35
  • 1
  • 1
  • 5
  • [Open](https://docs.python.org/3/library/functions.html#open) the file for appending instead of writing. You should consider using the ```with``` keyword when [working with files](https://docs.python.org/3/tutorial/inputoutput.html#reading-and-writing-files). If you are looking for *complete* strings: put your strings in a container like a [set](https://docs.python.org/3/library/stdtypes.html#set-types-set-frozenset) and use the [in *operator*](https://docs.python.org/3/reference/expressions.html#comparisons). – wwii Jun 27 '16 at 14:42

1 Answers1

3

This should work. You can't assign 5 strings to a single variable, without using a list or some other data type. You can however check to see if the third cell's value (i[2] - here) is equal to any of the strings you're looking for ("string1" - "string5" - here).

import xlrd
sheet_data = []   
wb = xlrd.open_workbook(Path_to_xlsx)
p = wb.sheet_names()
for y in p:
   sh = wb.sheet_by_name(y)
   for rownum in xrange(sh.nrows):
      sheet_data.append((sh.row_values(rownum)))

found_list = []
rows_to_be_saved = []
for i in sheet_data:
  if i[2] == "string1" or i[2] == "string2" or i[2] == "string3" or i[2] == "string4" or i[2] == "string5":
    found_list.append(i)
  else:
      rows_to_be_saved.append(i)

text_file = open("Output.txt", "w")
text_file.write(found_list)
text_file.close()

Your output written to the text file "Output.txt" will be comma separated as the rows in your excel are read into python as tuples in a list.

SAMO
  • 458
  • 1
  • 3
  • 20
  • Thanks, could you please explain the funciton of "w" in this line? text_file = open("Output.txt", "w") – Jukie Jun 27 '16 at 14:50
  • Yeah, "w" just means the file is writable: https://docs.python.org/2/tutorial/inputoutput.html#reading-and-writing-files – SAMO Jun 27 '16 at 14:57
  • Thank you very much for the help! – Jukie Jun 27 '16 at 15:29
  • Would panda be the best way to go about deleting the rows from the spreadsheet as well? something similar to this, dfs = dfs[dfs['Name'] != ''] found here http://stackoverflow.com/questions/16616563/in-python-removing-rows-from-a-excel-file-using-xlrd-xlwt-and-xlutils – Jukie Jun 28 '16 at 14:12
  • You can definitely do that, but you could also just only read in the columns you want initially or only append those columns to a list later. So: for column in all_columns: good_columns.append((column[0], column[1], column[3])) – SAMO Jun 28 '16 at 14:22
  • I assume I can do the same with rows? I'm not sure if that was a misunderstanding but I want to keep every column, just delete the rows that were extracted to my text file from the spreadsheet – Jukie Jun 28 '16 at 14:27
  • Oh yeah sorry I misunderstood, but yeah the easiest way would be just to append the rows that don't match to a list. I'll edit the code above, one second – SAMO Jun 28 '16 at 14:34
  • So rows_to_be_saved will be a list that contains all the rows in which those strings weren't found. So in essence you have deleted those other rows – SAMO Jun 28 '16 at 14:36
  • Okay I see, I was going to attempt to figure that part out on my own haha but I appreciate the amazing assistance kind sir/madame. Hopefully I'll be able to help others here one day as you have. – Jukie Jun 28 '16 at 14:55
  • No problem, I'm happy to help and I'm sure you will – SAMO Jun 28 '16 at 14:57
  • For anyone on Python 3, xrange is not an available function. You will need to use the normal range function instead. – Pants Feb 05 '20 at 17:57