benpay benpay - 18 days ago 6
Python Question

Read excel svc and convert the resultat list to dictionary [Python]

I'm several days searching how can I do this and I can't solve it. I see too much information about what I would do, but I can't solve my problem (sorry for my bad comprension about it).

I would read the values from an excel and then inserted it into a dictionary.

For do it, I read from a csv excel and insert it into a dictionary, I read rows and insert that into a list, when all are readed I put the list into a dictionary, I have this file (excel):

action key
turn on TO001
turn off TO000
conect CO001
disconect DI000
plug PO001
unplug UP000


My code for read the csv file is:

def __call__(self, fileToRead):
print("\n Now the output from a dictionary created from the csv file")

try:
with open(Read.ROUTE+fileToRead+Read.EXT, 'rt') as mycsvfile:
dictioData = csv.DictReader(mycsvfile, dialect='excel')
for row in dictioData:
Read.list.append(row)
print(row)
except FileNotFoundError as error:
if error.errno == errno.ENOENT:
print ("File not found, please check the name and try again")
Dicctionary.setVerbs(self,Read.list)


On this part I have a problem, i would it read the values:

'turn on' : 'TO001'
'turn off' : 'TO000'
...


But it read:

{'action;key': 'turn on ;TO001'}
{'action;key': 'turn off ;TO000'}
...


How can I do that?

Thank you.

Answer

Well, you over-complicated this one. If you're able to convert your excel file into a .csv one:

action;key
turn on;TO001
turn off;TO000
conect;CO001
disconect;DI000
plug;PO001
unplug;UP000

You might be able to do something like this:

def csv_to_dict(filename):
    with open(filename) as f:    
        d = {}  # create an empty dictionary where we'll add the data
        for row in csv.DictReader(f, delimiter=';'):  # iterate over DictReader object
            d[row['action']] = row['key']  # add data to the dictionary the way we want
        return d  # return the data

if __name__ == '__main__':
    filename = 'path_to_file_here'  # here complete the path 
    pprint(csv_to_dict(filename))  # I've used pprint to have the dictionary nicely printed 

Which will print exactly what you want:

{'conect': 'CO001',
 'disconect': 'DI000',
 'plug': 'PO001',
 'turn off': 'TO000',
 'turn on': 'TO001',
 'unplug': 'UP000'}