How to automate excel formula while adding single quote to each column I'd?

Help Activities
,
1

So i was trying to create bulk SQL insert query using Excel itself

So here are the two parts of it

First the formula to create the insert command

=" Insert into tableName("&A1&","&B1&",and so on for the rest of the columns) values"

So here the tableName will be a string variable which holds any kind of table name string

The “&CoulmnId&” in here the double quotes and the & operator is used for contacting the columns

Second formula to create the data command

="('"&A2&"','"&B2&"','"&TEXT(C2,"MM/DD/YYYY")&"','"&D2&'"),"

Now in here i have to add a single quate and a double quato

The single quate will be added to the final result will say that the value is a string in SQL

Now as you can see i have added ‘“&TEXT(C2,“MM/DD/YYYY”)&”’ which means that the column contains a date because when i just use the normal concat like above the coumnn which contains the date is getting a wierd output so for that reason I’m adding this specific date format so i can get the date as it is in the column

Now in the real question is how do I automate it in uipath

I just have to automate two rows the first one that contains the coumnnname will be the insert query formula

Now the second one will be the first data formula eventually i will just drag ( or double click the cell that contains the data formula so it will apply to all the below rows that contains the data

Okay wait a min stop reading further , if you are getting confused just look at this entire video and you will understand it all

Now i will tell you what things are dynamic here

Insert one

1:- after a read range i will get it into the datatable now i want that the automation identify the column I’d and put the column I’d inside that fix “&coumnid&”

2:- actually i don’t want the table name to be dynamic i will later change the table name manually as it’s is not a big concern

3:- idk how the double quotes will work here

4:- after each ending double quote it should add a comma to sperate the next one from the last one added ( actually didn’t need comma after the last coulmn end but i can remove it manually if its cplicated

Data one

1:- as the same datatable will get the second row ( contains the first data) it has to identify the column I’d and then add it in a single quate then in a double quote and finally insiee the & coumn I’d and repeat it for closing that particular concat

2:- most of the steps are same for the data as shown in the insert

3:- now when the automation is getting or get the data it has to identify the date column (i will provide a manual date format so Automation didn’t have to identify the date format but it has to identify that the cell it is in rn contain a date ) and then the automation has to add the TEXT (coumnid,“my given date format”) and then continue the normal Automation for the rest of the data

4:- the data will always have a string values in it so Automation have to add single quate for each of them

Note! I forgot to mention that not all of the Coulmn contains data so the Automation also have to look for empty coulmn and then adjust the insert formula and the data formula accordingly by not selecting the empty coulmn

Also i want the Automation to be a string Automation and not ui automation so if you are thinking to use Excel itself to automate it no

2

Hi @sagar.raval ,

If I understand correctly then you are looking for creating a insert query using the data read from excel which is present in a datatable.

please follow the below steps

  1. After reading the data check the column count.
  2. First try identifying the blank columns by running a for loop(item)(change type argument to integer) with the column count from step 1. Loop on Ienumerable.Range(0,columncount). Inside that use a if condition with dt.rows(0)(item).Tostring.Trim.Equals(string.Empty). On true side you will get all empty columns and on false side you can construct your insert statement with all identified columns .
  3. Now if your excel columns are strting from A1 then the first formula will have the row value as 1 (eg:A1)and second formula will have row value as 2(eg:A2)
  4. To identify the column name …you can add the item value(column number from first loop) to 65 to convert the number into a ascii character . Then use convert.tochar(65+item) . This will give you the column name as in excel
  5. Paste the formula created into the excel using write cell formula

Cheers

3

no actually the number is static and the characters are incrementing

for eg A1,B1,C1,D1,etc also its is like AA1,AB1,AC1,AD1, can continue this pattern on the number of the columns available

that’s the thing i have to increment characters and not the digits

and i want to do this for only two rows the first rows that contains the column name and for the second row which will contains the first row of data

so for the first one it will be like A1,B1,C1 and so on and for the second one it will be like A2,B2,C2 and so on

4

Hi @sagar.raval ,

This is how you will get the column name on excel using column number from datatable.

You can convert the column number(1,2,3,4…) to excel column name(A,B,C,D…,AA,AB…)

ExcelIndexToColumn.xaml (7.4 KB)

cheers