How to automate excel formulas and perform double click on the bottom end of the formula cell

This video shows how to generate insert statements from an Excel file and load them into SQL

Therefore, I would like to use it for SQLite

It is highly recommended that you watch the entire video

I want to “insert into the table name (parameters) values” from the first row of the column

Next, the second row will contain the data, for example

“(‘1’,‘Barney Pitt’,‘Los Angeles’,‘16367’,‘Male’,‘Self-motivation’),”

I have added a single quote to all my data, but the format of the data can be different in other sheets or excel files.

For example, a column with an integer value will not contain a quote, so the automation needs to adapt to it

Also, please take a look at the video’s date format

For example, a table employees has already been created with a few int values, a date format, and some strings.

To understand the table format, I will take all the input

If the user said the column “C” was an int, then when the automation is writing down the Excel formula it will not add a single quote, same for other instructions.

Once the insert and the first row data excel commands have been written, the automation should press enter and then double click on the second row bottom end to implement the formula for all the following rows.

After that, it should just copy the entire column and do something (I’ll check ahead after this).

In order to write the formula in the next column, the automation should find the end of the column

If you ask why I’m doing all this mess rather than using the bulk insert activity, let me summarize it in short: I’m currently working with SQLlite, and the package I use has a bulk insert activity, but it’s awfully slow. So I thought using all the data into one insert command would be a lot faster, so I found a way to get a bulk data into an insert query using excel formulas.

Hi @sagar.raval ,

We need not do the Preparation of the Insert Query and the values to be inserted in the same manner as provided in the video, We could adopt the datatable Approach, and prepare the Query Dynamically, Maybe a Template would need to be fixed for the Querty, then a Duplication would be performed for all the rows. Using a For Each Row or a Linq we could prepare the data format as needed for using it in Input Query.

For this, do we know in Advance what are the Columns and what would be it’s data (either a number/date/text) ?

Need to understand is the User going to be intervened at times, or do we get the Excel file and should develop the Query based on it only?

If you could provide us with a Sample Excel file and the Final Query that the Output should be, We could maybe provide a similar suggestion/approach.

Every type of format will be informed before starting the automation if its an int or a specific date format or a string

well right now the excel i have is all of string (just a demo excel of 800k no of rows)

it contains all the data and the out put (im not sure if the formula will show for you too when you will open the excel file in your system , let me know if it doesn’t shows up)

here is the excel in 7zip file