0

hi:) i am trying to import data from 3 excel files, each containing 6 sheets into one sqlite3 database. this is the code i used to import data from one sheet:

import sqlite3

df = pd.read_excel(r'C:\Users\julia\OneDrive\Documents\python assignment\2016 data -EU values.xlsx')
conn = sqlite3.connect('FinalProjectDatabase.sqlite3')
Malta = conn.cursor()
Malta.execute('''CREATE TABLE MaltaData
               (Country TEXT, City TEXT, AirQualityStationEolCode TEXT, AQStationName TEXT, 
       AirPollutant TEXT, AirPollutionLevel REAL, UnitofAirPollutionLevel TEXT, AirQualityStationType 
      TEXT, AirQualityStationArea TEXT, Longitude REAL, Latitude REAL, Altitude REAL)''')
for row in df.iterrows():
    sql = "INSERT INTO MaltaData (Country, City, AirQualityStationEolCode, AQStationName, 
       AirPollutant, AirPollutionLevel, UnitofAirPollutionLevel, AirQualityStationType, 
       AirQualityStationArea, Longitude, Latitude, Altitude) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)"
    Malta.execute(sql, tuple(row[1]))
conn.commit()
Malta.execute("DELETE FROM MaltaData WHERE COUNTRY != 'Malta'") #only require data about Malta

this code works fine and i am able to see my data in my db browser, however, i need to import data from all sheets and all files. with regards to the different sheets, i tried using sheet_name = None after the path of one of the excel files, however, that just returned errors. how may i modify my code so that i will be able to display data from all files and sheets please?

any help would be greatly appreciated!

Julia Micallef Filletti
  • 49
  • 6
  • https://stackoverflow.com/questions/26521266/using-pandas-to-pd-read-excel-for-multiple-worksheets-of-the-same-workbook This link may help – ankit Feb 11 '21 at 20:24
  • You can read each sheet one at a time and create tables for them using the same approach you are using – ankit Feb 11 '21 at 20:26
  • @ankit, i get what you are saying, however, i need all the data in one table in my database, so i cannot go through each sheet and make a new table for each sheet – Julia Micallef Filletti Feb 11 '21 at 20:34
  • if the schema of all the sheet is same then you can read all the sheets in multiple dataframes and concat them before writing all of them to one table. Same can be done using loop also – ankit Feb 11 '21 at 20:43

0 Answers0