Excel automation using dataset

[Aki1111 (Akira)]

Hi Guys,

I have a tricky one here, I have two excel files that I have data.

One with regular fields and one with line items,

I am able to extract the line items and the regular fields however when committing to the database I need to use 1 heading from the the regular field followed by the rest of the headings in the line items.

for exam table one has regular fields of invoice number, date, item. table two has all the line items however needs to have the invoice number as well from regular fields.

I need to know how to commit table two with regular field and line items together.

Kind Regards

[supermanPunch (Arpan)]

@Aki1111

however when committing to the database I need to use 1 heading from the the regular field followed by the rest of the headings in the line items.

for exam table one has regular fields of invoice number, date, item. table two has all the line items however needs to have the invoice number as well from regular fields.

I need to know how to commit table two with regular field and line items together.

Hi @Aki1111 ,

There might be a confusion that we would need to clear at first. When you mention database, is it the extracted data present in the Datatable or the a Database such as SQL, Oracle etc… ?

Also, If it is a manipulation with the Excel data that you want to perform, then could you show us by providing screenshots of How you would like to arrange the data between the two files ? This would help us to provide suggestions better and more appropriate to your needs.

[Nitya1 (Nitya Tomar)]

Hi @Aki1111

Merge the two DataTables using the “Merge Data Table” activity. Specify the regular fields DataTable as the first input, and the line items DataTable as the second input. Choose the “Join Type” as “Inner Join” and specify the column to join on, which is the invoice number

Once done then Create a new DataTable variable to hold the merged data and Loop through the merged DataTable and use the “Add Data Row” activity to add each row to the new DataTable variable.

For each row, you can specify the column values using the column names from the regular fields DataTable followed by the column names from the line items DataTable.

Let me know if it works for you

[Aki1111 (Akira)]

Hi the data is in excel file that needs to sent to the sql database.

so basically I created two excel files with data,

I need the invoice number from table one to be added to table two, I cant use join table. I dont know how to do that and then commit.

[Aki1111 (Akira)]

I will check and let you know

[Aki1111 (Akira)]

Hi,

I am using SQL to insert these two tables into the db tables.

table 1 of the excel is this (Regular fields)

table 2 of the excel is this (Line items)

However when inserting the table 2 into the database

I need to insert the regular field in red followed by the line items but this is a db.

So basically I have my regular fields and line items seperated however when comitting to the database I need to iterate through each invoice and insert the invoice number with the line items.

how can i go about doing this?

[LAKSHMI_NARAYANA_PEMMASAN (LAKSHMI NARAYANA)]

Read the invoice number from regular field either read cell or read range and get the invoice number

read the line item to datatable

Add column to Datatbale with columnname Invoice_no

Refer below to move column to 0 index position

https://forum.uipath.com/t/how-to-move-column-from-index-0-to-index-10-in-datatable/137583/3

if the invoice no is same for all the records
use assign activity in to field use Dt.Columns(“Invoice_no”).Expression and in Value field give invoice no
otherwise you have use either for each and use assign activity to set the value

Regards

[Aki1111 (Akira)]

Main.xaml (55.8 KB)

This is my work flow I have added the data column for invoice_no,

The only thing I need to know how to do is take the invoice number from table 1 and put in table 2 as it iterates through

for example

if the line items in table two have 5 items then invoice number for that 5 items need to appear 5 times.

[supermanPunch (Arpan)]

@Aki1111

The only thing I need to know how to do is take the invoice number from table 1 and put in table 2 as it iterates through

for example

if the line items in table two have 5 items then invoice number for that 5 items need to appear 5 times.

@Aki1111 ,

When using the Add Data Column activity, we will be also able to provide the Default Value to it. So we can add the extracted Invoice Number from the Regular Fields table and use it there.

This should update all the rows in the Line Items table.

Let us know if you are still facing issues.

[Aki1111 (Akira)]

@supermanPunch

will be also able to provide the Default Value to it. So we can add the extracted Invoice Number from the Regular Fields table and use it there.

how do we add as a variable type from the table then because I have to go through 4000 invoice numbers?

[supermanPunch (Arpan)]

@Aki1111

Main.xaml (55.8 KB)

@Aki1111 ,

I did check your workflow, although I only dived into the part where we would need to make the changes.

So, Could you perform the changes as provided in the below image and let us know if you were able to apply /update the invoice no. in the Line Items table ? I have added the Add Data Column activity with a Default Value

Also, Comment the For Each Row part when doing the above change :

Another kind of an issue noticed is that you are using a static file / Sheet names in Write Range activity, which if having multiple documents to process it would over-write the data and the previous data would be lost.

[Aki1111 (Akira)]

Thank you so much