Skip to main content

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 @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 @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?


Reply