DEV Community 👩‍💻👨‍💻

Cover image for Collecting Data into Database Automatically
Anuoluwapo Balogun
Anuoluwapo Balogun

Posted on

Collecting Data into Database Automatically

Import Necessary Libraries

import pandas as pd
import sqlite3
import os
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

Top comments (0)

🌚 Friends don't let friends browse without dark mode.

Sorry, it's true.