Data transfer from EXCEL file to an another database by Python

In our previous article, we explained how to combine records from other databases and transfer them to our database that we keep in Sql. We did this process through Python. In this article, we will transfer data into the SQL database again through Python, but this time the place where we will get the data will be the Excel program.

sample excel file to export;

Here is the Python code we will use ;

# Python libraries we will use :

import pandas as pd
#Pandas is a frequently used tool for retrieving data from various sources.

##sqlalchemy is a library in Python that provides access to relational databases (e.g. PostgreSQL, MySQL, SQLite).
from sqlalchemy import create_engine
from sqlalchemy.types import Integer, DECIMAL, DateTime, VARCHAR


# The csv Excel file name to upload
excel_file_name = 'data_transfer_from_excel.csv'

# Full path to the Excel file to upload
excel_file_path = r'C:\{}'.format(excel_file_name)


# SQL Server connection string
sql_server_connection_string =pyodbc.connect('DRIVER={SQL Server};SERVER=host;DATABASE=database_name;UID=user_NAME;PWD=PASSWORD')


# Connecting to SQL Server
engine = create_engine(mssql_connection_string)

# Reading data from Excel
df = pd.read_csv(excel_file_path, engine="python", sep=";")

# Set the date format
date_format = "%d.%m.%Y"

# Convert date column
df["Insert_Date"] = pd.to_datetime(df["Insert_Date"], format=date_format)

# Column data types in the excel file
column_types = {
    "Id": Integer(),                  
    "Balance_Amount": DECIMAL(18, 2),
    "Insert_Date": DateTime(),      
    "Customer_Name": VARCHAR(255)
}

# To be transferred Table name
table_name = "data_transfer_from_excel"


# Exporting data to SQL Server
with engine.connect() as conn:
    df.to_sql(
        table_name,
        conn,
        index=False,
        if_exists="replace",
        dtype=column_types  # column_types sözlüğünü dtype parametresine geçirme
    )
# Message to be displayed
print("Data transfer completed successfully.")

**View of transferred records on the database screen ; We see that the column names are automatically generated according to the data types written in the “Column data types” section in the code # Column data types in the excel file column_types = { “Id”: Integer(), “Balance_Amount”: DECIMAL(18, 2), “Insert_Date”: DateTime(), “Customer_Name”: VARCHAR(255) }

In our next article, we will start on to the Dashboard preparation process in the Apache Superset program using the records transferred to the SQL databese program.

AHMET GÜNOĞLU

ERP Consultant and Business Intelligence Expert

https://www.ahmetgunoglu.com

https://www.ahmetgunoglu.com/blog

https://medium.com/@ahmetgunnnnn