Database queries taking time

Hi @Enab, 

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. The if_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

I had seen this But i can’t download this as bot.

Also my db is redshift

@Enab ,

I don’t think insert query will take more then 10 mins.

Can you share your technical flow of your task?