Collecting Data into Database Automatically

Import Necessary Libraries

import pandas as pd
import sqlite3
import os

Get Data From Excel File

def GetData(filename):
    # excel settings
    try:
        df = pd.read_excel(filename)
        return df
    except:
        print('File Not Readed Filename: ' , filename)
        pass
    # csv settings
    try:
        df = pd.read_csv(filename, delimiter=',')
        return df
    except:
        print('File Not Readed Filename: ' , filename)
        pass
    else:
        df = pd.read_csv(filename, delimiter=';')
        return df
    finally:
        print('CSV File Added!: ', filename)

Write Database Code

def RecordSql(dataframe, filename):
    filename = str(filename).lower().strip().replace(' ', '_')
    conn = sqlite3.connect('collector_database.db')
    try:
        dataframe.to_sql(filename, conn)
        print('Table Added: ', filename)
    except:
        pass
    conn.close()
    return

Run Code

if __name__ == '__main__':
    currdir = os.getcwd()
    for filepath in os.listdir(currdir):
        filename, ext = os.path.splitext(filepath)
        if ext in ['.xlsx', '.xls', '.csv']:
            print(filepath)
            data = GetData(filepath)
            RecordSql(data, filename)