Get a file name and insert in a variable dynamically

  pandas, python

I have many files in a folder. Ex: Brazil 02-02-2021.xlsx; USA 04-01-2020; so on…
I need to get the country name and save it in a variable and show in a Database dynamically.

path = r'C:Userscountries'
files = os.listdir(path)

AllData = pd.DataFrame()
for f in files:
    info = pd.read_excel(f, "Template")
    AllData = AllData.append(info)

for name in files:
    k = name.rfind(" ")

for row in AllData.itertuples():
    cursor.execute('''
                  INSERT INTO TestDB.dbo.Counties (Index, CountyName, Date, Region)
                  Values(?,?,?,?)
                  ''',
                  row.Index
                  name[:k],
                  row.Date
                  row.Region
                  )
conn.commit ()

This store the files data; however, it just saves the last country name. I need it to save according to the file used. Such as:

Index   Country(from filename)   Date         Region
1          Brazil               02-02-2021    South 
2          Brazil               01-02-2021    South
3          Brazil               10-02-2021    South  
4          USA                  20-03-2020    North
5          USA                  10-03-2020    North
6          CANADA               20-03-2020    North
                   .
                   .
                   So on

      

Source: Python Questions

LEAVE A COMMENT