-1

I need to import excel to a dictionary and then using the dictionary to find and replace strings in all SQL files in a folder.

I tried the below Python code to import from excel to the dictionary.

Excel input

enter image description here

import pandas as pd
d = pd.read_excel(r"findrep.xls",index_col=[0]).to_dict(orient='index')
print(d)

output for the code is coming as

{'art': {'rep': 'artist'}, 'car': {'rep': 'cart'}, 'dar': {'rep': 'dart'}}

but I need as follows

{'src':'rep','art':'artist','car':'cart','dar':'dart'}

then Also I need to replace all the files in a folder, I am trying to use the following code, but I am getting 'dict' object has no attribute 'iteritems' error.

please help..

import os
os.chdir("C:\\Users\\Magesh\\Documents\\Python Scripts\\sql")
replacements = {'src':'rep','art':'artist','car':'cart','dar':'dart'}

for files in os.listdir("."):
    fo = open(files, "rU")
    text = fo.read()
    for src, target in replacements.iteritems():
        text = text.replace(src, target)
        fo.seek(0)
        fo.write(text)
        fo.truncate()
        fo.close()
MageshJ
  • 33
  • 6
  • 2
    Please ask one question only... – U13-Forward Sep 08 '21 at 09:22
  • this will help https://stackoverflow.com/questions/17426292/what-is-the-most-efficient-way-to-create-a-dictionary-of-two-pandas-dataframe-co – deadshot Sep 08 '21 at 09:25
  • First question: Try `df = pd.read_excel("findrep.xls", index_col=0); replacements = {df.index.name: df.columns[0], **df[df.columns[0]].to_dict()}`. Second question: Try `src, target in replacements.items()`. – Timus Sep 08 '21 at 11:45

1 Answers1

0

It worked thanks to Timus

import os
import pandas as pd
df = pd.read_excel("findrep.xls", index_col=0); 
replacements = {df.index.name: df.columns[0], **df[df.columns[0]].to_dict()}
print(replacements)

os.chdir("C:\\Users\\Magesh\\Documents\\Python Scripts\\sql")
for files in os.listdir("."):
    fo = open(files, "r+")
    text = fo.read()
    for src, target in replacements.items():
       text = text.replace(src, target)
    fo.seek(0)
    fo.write(text)
    fo.truncate()
    fo.close()
MageshJ
  • 33
  • 6