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