I work as an IT intern in a multinational and I was given a tedious task of combing through a 2500+ long multi-column excel report file in search of inactive servers.

Here's a sample row from that file:

enter image description here

Then, I got another excel file but this time with just the DB Codes (80+ of them).

My task was:

  • Go through the big report file
  • Find the company by its DB Code
  • Check if the server is active or not active, and if not active flag it for a decommission

Of course, as you might expect, I was told to return the results in a spreadsheet in the following format:

Full name: Acme Inc. | Code: ACM | Active?: no | Decomm?: yes

Fulln name:, Code:, etc. are column headers. Here, they are just for readability.

If I were to do it manually, I'd most probably die of boredom. But! There's Python, right?

So, I exported some of the columns from the report into a tab delimited file and drafted this:

def read_file_to_list(file_name):
    with open(file_name, 'r') as file_handler:
        stuff = file_handler.readlines()
    return [line[:-1] for line in stuff]

def make_dic(file_name):
    with open(file_name, 'r') as f:
        rows = (line.replace('"', "").strip().split("\t") for line in f)
        return {row[0]:row[1:] for row in rows}

def search(dic, ou_codes):
    counter = 1
    with open("output.txt", "w") as output:
        #writing a header
        output.write("Full name\tCode\tActive?\tDecomm?\n")
        for k, v in dic.items():
            for code in ou_codes:
                if v[0] == code:
                    #writing back line by line to a tab delimited file
                    outputline = "{}\t{}\t{}\t{}\n".format(k, *v,
                    "yes" if v[1] == "no" else "no")

                    print("{}. Full name: {} | Code: {} | Active?: {} | Decomm?: {}".format(counter, k, *v, "yes" if v[1] == "no" else "no"))
                    counter += 1

decomm_codes = read_file_to_list('t1.txt')
all_of_it = make_dic('t2.txt')

search(all_of_it, decomm_codes)

That spits out:

1. Full name: Random, Inc | Code: RNDM | Active?: yes | Decomm?: no
2. Full name: Acme Inc.| Code: ACM | Active?: no | Decomm?: yes
3. Full name: Fake Bank, Ltd.  | Code: FKBNK | Active?: yes | Decomm?: no

Is there a way refactor the search method?

Finally, here's the contents of the decomm_codes.txt and big_report.txt files.




"Random, Inc"   RNDM    yes
Acme Inc.   ACM no
"Fake Bank, Ltd. "  FKBNK   yes
  • 5
    \$\begingroup\$ "... but how do I write the results back into a tab-delimited text file?" is likely off-topic here, since only existing and working code can be reviewed. Apart from that it seems like a good question. \$\endgroup\$
    – AlexV
    Jun 14, 2019 at 13:24


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.