I have nearly 3000 lines of data that is needed to be inserted into a Database every day. It is taking me nearly 3.5 hours to finish the bot. Is there any way I can do it faster? I am using Insert action within loop for worksheet as data to be entered is in excel. Please help as this is very urgent.
Hi
To load data into a SQL database, looping through the data is essential. Here are some options you can consider:
Option 1:
- Connect the input source file (Excel) as a database.
- Use a loop to read and insert the data into the database.
Option 2:
- Connect the input source file using Excel Basic or Excel Advanced.
- Use a loop to read and insert the data into the database.
Option 3:
- Use Python.
Python Code
-
pip install pandas sqlalchemy openpyxl
-
import pandas as pd
from sqlalchemy import create_engine# Read the Excel file
df = pd.read_excel('your_file.xlsx', engine='openpyxl')# Create a database connection
engine = create_engine('sqlite:///your_database.db')# Insert data into the database
df.to_sql('your_table_name', con=engine, if_exists='replace', index=False)print("Data inserted successfully!")
- Read the Excel file: The
pd.read_excel
function reads the Excel file into a DataFrame. - Create a database connection: The
create_engine
function from SQLAlchemy creates a connection to the database. In this example, we’re using SQLite, but you can replace the connection string with one for your database (e.g., PostgreSQL, MySQL). - Insert data into the database: The
df.to_sql
function inserts the DataFrame into the specified table in the database. Theif_exists='replace'
parameter ensures that the table is replaced if it already exists.
Ensure that the connection to the destination database is established and closed outside of the loop.
Does this help?
Hi
To load data into a SQL database, looping through the data is essential. Here are some options you can consider:
Option 1:
- Connect the input source file (Excel) as a database.
- Use a loop to read and insert the data into the database.
Option 2:
- Connect the input source file using Excel Basic or Excel Advanced.
- Use a loop to read and insert the data into the database.
Option 3:
- Use Python.
Python Code
-
pip install pandas sqlalchemy openpyxl
-
import pandas as pd
from sqlalchemy import create_engine# Read the Excel file
df = pd.read_excel('your_file.xlsx', engine='openpyxl')# Create a database connection
engine = create_engine('sqlite:///your_database.db')# Insert data into the database
df.to_sql('your_table_name', con=engine, if_exists='replace', index=False)print("Data inserted successfully!")
- Read the Excel file: The
pd.read_excel
function reads the Excel file into a DataFrame. - Create a database connection: The
create_engine
function from SQLAlchemy creates a connection to the database. In this example, we’re using SQLite, but you can replace the connection string with one for your database (e.g., PostgreSQL, MySQL). - Insert data into the database: The
df.to_sql
function inserts the DataFrame into the specified table in the database. Theif_exists='replace'
parameter ensures that the table is replaced if it already exists.
Ensure that the connection to the destination database is established and closed outside of the loop.
Does this help?
Hi there sorry that I mentioned that there are just 3000 entries. In real there are nearly 7500 entries. Because I have my data in excel, I am actually looping through all rows and then inserting to database. Even after keeping the bot running over the night, it just entered 1000 lines only. This definitely isn’t an efficient way as i will be having 7500 entries or more everyday. Please suggest
Hi ,
You can have a look
https://learn.microsoft.com/en-us/sql/relational-databases/import-export/import-data-from-excel-to-sql?view=sql-server-ver16
There are something in bot store as well
https://botstore.automationanywhere.com/bot/bulk-upload-of-excel-data-to-sql-server-database
https://botstore.automationanywhere.com/bot/insert-data-from-files-into-sql-database-or-server
Please give a try
Hi ,
You can have a look
https://learn.microsoft.com/en-us/sql/relational-databases/import-export/import-data-from-excel-to-sql?view=sql-server-ver16
There are something in bot store as well
https://botstore.automationanywhere.com/bot/bulk-upload-of-excel-data-to-sql-server-database
https://botstore.automationanywhere.com/bot/insert-data-from-files-into-sql-database-or-server
Please give a try
I had seen this But i can’t download this as bot.
Hi ,
You can have a look
https://learn.microsoft.com/en-us/sql/relational-databases/import-export/import-data-from-excel-to-sql?view=sql-server-ver16
There are something in bot store as well
https://botstore.automationanywhere.com/bot/bulk-upload-of-excel-data-to-sql-server-database
https://botstore.automationanywhere.com/bot/insert-data-from-files-into-sql-database-or-server
Please give a try
I had seen this But i can’t download this as bot.
Also my db is redshift
I don’t think insert query will take more then 10 mins.
Can you share your technical flow of your task?
Reply
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.