How can i create a table in sql server from excel file using UIpath and insert the same excel data?

Help
, , ,
1

Hi Folks,

How can i create a table in sql server from excel file using UIpath and insert the same excel data within the sql table?

Details / Flow

  1. Excel having tabular data with column headers
  2. UIPath has to first create the table & then insert the data from the excel into the sql table.

Any help would be appreciated. Thanks

Aslam

1 Like
2

I dont think UiPath can do itself, you may have to create a workflow with steps to create the table and insert the data row by row.

3

But there has to be some approach.

4

Hi @Aslam_Nagarboudi,

You can read the excel file with Excel Application Scope activity → read Range this will give you an DataTable as output.

image

Then you can use insert activity of Database which will directly insert you datatable into Database

image

And if there is no table in the Database you can create first by executing query from here

image

Thanks,
Prankur

5

Thanks Prankur, but the table has to be created before the data get inserted in to the sql table, and it has to be dynamic irrespective of any number of columns in the excel.

6

You can create in the start. Also you can create table on the basis of the Excel you read.

7

how will UIp segregate the columns, its data types within the excel so that accordingly UIp can create the sql table in server.

8

You have to construct sql statement to create a table, column headers can be field names & data type you can figure out from the data row.

9

Excel could have any number of columns , so UiP should create dynamically all the columns and the table. And then insert data.

10

@Aslam_Nagarboudi -

You have to get number of columns from excel when you read that excel the output would be DataTable so you can count the column.

Then you will have to create a dynamic query in which you would add columns in the query and for the data type you will have to play some trick to recognize the type or you can have default data types in a config file that you can pick from.

Thanks,
Prankur

11

Please understand there is no such feature in UiPath…you have to develop a workflow to do the needful

12

@Aslam_Nagarboudi If data already stored in excel or csv than you can refer below link

13

Hi @indra or someone,

I have downloaded the above csvtoDB file but am not able to see the activities it is showing as missing activity.
can someone help me on this.

my uipath version is 2020.2.0

14

Use Uipath.Database.Activities for working with databases.

imagen
imagen733×224 17 KB

Then use an activity in order to read CSV and export contect to DataTable.
You should read de Excel structure, then number of columns, datatype of them, with that information, create a dictionary with datatype and corresponding types in your database. Then compose the Create Datatable command with the column headers of your excel and the data types…
I don´t have that code donde, but sure you can develop after …