REFramework- excel to DB then to Orchestrator queues

Help Studio
, ,
1

Hi all. I need help with inserting an excel data table to a sql database table. The excel data table should be inserted as a Json string or just a text string in the data column. Then i want to retrieve the Reference IDs of the database table to orchestrator queue. The database table has the following columns: ID, ProcessName, MachineName, JobID, ReferenceID, Status, DataJson, CreatedAt, UpdatedAt. The excel file has about 5 columns. These columns are the ones that should makeup the Json file which will sit on the DataJson column on the Db. The client does not want the data on the cloud so it has to sit on-prem. I can only use IDs in the queues. I do not know if i explained it well. Thank you

2

Hello @pateswanaw,

In dispatcher, you can read the Excel file, insert the data into the DB, and then add the ID to the Queue.

The Performer will consume the Queue’s data; in the process, it will retrieve the data and start processing each ID in the Queue.

This can be done.

Thanks,
Karthik

3

Hi @pateswanaw First Read Excel into a DataTable and then convert it to a JSON string using JsonConvert.SerializeObject, and then insert this JSON into an on-prem SQL table using an INSERT query with OUTPUT INSERTED.Id to get the generated ID, and then store that ID and pass it as the Reference in Add Queue Item so only the ID goes to Orchestrator, and then while processing fetch the Reference ID from the queue and use it to query the database and get the JSON back and then convert it to a DataTable using JsonConvert.DeserializeObject for further use.

Cheers

4

The database table has the following columns: ID, ProcessName, MachineName, JobID, ReferenceID, Status, DataJson, CreatedAt, UpdatedAt. The excel file has about 5 columns. These columns are the ones that should makeup the Json file which will sit on the DataJson column on the Db.

5

Hello @pateswanaw,

You mainly need to handle JSON escape characters — especially " (use \"), \ (use \\), and control characters like new lines (\n) or tabs (\t).

Instead of manually escaping, it’s best to use JsonConvert.SerializeObject in UiPath, which automatically handles all escaping correctly.

For DB insertion, use parameterized queries so you don’t have to worry about SQL escaping (like single quotes).

Machine and all you can get from Job Info

Thanks,
Karthik