Convert Excel Dataset into SQL insert statement

Utilizing Python makes converting Excel files to SQL databases a straightforward process.

To begin, export the Excel data to a CSV file by following these steps:

1. Open your Excel file.


2. Navigate to File > Save As.


3. Select CSV (Comma delimited) (*.csv) as the file type and save the file.

By following these simple instructions, you can seamlessly transition your Excel data into a format that is compatible with SQL databases.

FIRST_NAME LAST_NAME EMAIL USER_ID USER_LOGIN_NAME

First01 Last01 firstlastname01 ID001 loginname01

First02 Last02 firstlastname02 ID002 loginname02

First03 Last03 firstlastname03 ID003 loginname03

First04 Last04 firstlastname04 ID004 loginname04

First05 Last05 firstlastname05 ID005 loginname05

First06 Last06 firstlastname06 ID006 loginname06

First07 Last07 firstlastname07 ID007 loginname07

First08 Last08 firstlastname08 ID008 loginname08

Utilize a script or tool to convert CSV files to SQL format. For instance, you can employ a Python script to parse the CSV file and create SQL insert statements. Below is a basic Python script to help you begin the conversion process:

import pandas as pd



# Read the CSV file into a DataFrame

df = pd.read_csv('D:/temp/test/TestExcel.csv') # Add the path to your CSV file



# Generate SQL insert statements

table_name = 'Test_Table_Name' # Replace with your desired table name

sql_statements = []



for index, row in df.iterrows():

    columns = ', '.join(row.index)

    values = ', '.join([f"'{str(value)}'" for value in row.values])

    sql_statements.append(f"INSERT INTO {table_name} ({columns}) VALUES ({values});")



# Save to a file

with open('D:/temp/test/insert_statements.sql', 'w') as f:

    for statement in sql_statements:

        f.write(statement + '\n')

The following are the results of the scripts generated by the code above.

INSERT INTO Test_Table_Name (FIRST_NAME, LAST_NAME, EMAIL, USER_ID, USER_LOGIN_NAME) VALUES ('First01', 'Last01', 'firstlastname01', 'ID001', 'loginname01');

INSERT INTO Test_Table_Name (FIRST_NAME, LAST_NAME, EMAIL, USER_ID, USER_LOGIN_NAME) VALUES ('First02', 'Last02', 'firstlastname02', 'ID002', 'loginname02');

INSERT INTO Test_Table_Name (FIRST_NAME, LAST_NAME, EMAIL, USER_ID, USER_LOGIN_NAME) VALUES ('First03', 'Last03', 'firstlastname03', 'ID003', 'loginname03');

INSERT INTO Test_Table_Name (FIRST_NAME, LAST_NAME, EMAIL, USER_ID, USER_LOGIN_NAME) VALUES ('First04', 'Last04', 'firstlastname04', 'ID004', 'loginname04');

INSERT INTO Test_Table_Name (FIRST_NAME, LAST_NAME, EMAIL, USER_ID, USER_LOGIN_NAME) VALUES ('First05', 'Last05', 'firstlastname05', 'ID005', 'loginname05');

INSERT INTO Test_Table_Name (FIRST_NAME, LAST_NAME, EMAIL, USER_ID, USER_LOGIN_NAME) VALUES ('First06', 'Last06', 'firstlastname06', 'ID006', 'loginname06');

INSERT INTO Test_Table_Name (FIRST_NAME, LAST_NAME, EMAIL, USER_ID, USER_LOGIN_NAME) VALUES ('First07', 'Last07', 'firstlastname07', 'ID007', 'loginname07');

INSERT INTO Test_Table_Name (FIRST_NAME, LAST_NAME, EMAIL, USER_ID, USER_LOGIN_NAME) VALUES ('First08', 'Last08', 'firstlastname08', 'ID008', 'loginname08');

Please note that there are online tools available to Convert CSV files to SQL Insert Statements. It is important to exercise caution when using these tools to avoid exposing sensitive data. In some cases, the company may have blocked access to certain websites for security reasons.