markdario12 February 2016

Use Python to search and pull data from Excel

import csv

subject = ['emergency*', 'new ticket*', 'problem with*']
from_to = ['chris*', 'timothy*', 'daniel*', 'david*', 'jason*']

a = open('D:\testfile.csv', 'w')

New to python. So, here's what I'd like to do.

1) Open an excel csv file

2) Search for specific keywords that are in a list

3) If the keywords are found, pull the data that is in the D,E,F columns only. (Since that is where the keywords will be)

4) Write this data to a new file

Example. Search testfile.csv for any of the keywords in the from_to list. If these keywords appear ONLY in the D or E columns of excel AND if the corresponding column F is not equal to the subject list, then write a new file that has the columns of D,E,F and the associated lines, however many there are, with it

Also, I put the stars next to the names/items in the list to denote a wildcard, eg if the from_to contains chris.gmail.com or daniel@yahoo.

Answers


Joseph James February 2016

This solution has a list of keywords, an input file that you read line by line, tokenize each line into a list of strings, then iterate your keyword list to check if any of them is in the tokenized line. If any keyword is found it writes the line to the output file.

keywrds = ["word1", "word2", "etc"]
with open ("myfile.csv") as fin:
    with open ("outfile.txt") as fout:
        for line in fin:
            line_tokens = line.split(",")
            for word in keywrds:
                if word in line_tokens:
                    fout.write(line_tokens[3:6].join(" ") + "\n")

Post Status

Asked in February 2016
Viewed 2,770 times
Voted 6
Answered 1 times

Search




Leave an answer