Could you help me build an excel file?

Help
, , , ,
#1

Hi everyone,
could you help me solve my problem?
I should start by reading the input file and create an output file.
My problem is how do I build the output file by copying values from the input file?
In the output file I have to copy two values which are in the input file which are:
Name Surname → concatenate them in the AuthorizationProfileName output field
User ID → UserId field of the output file
I am attaching the two files if they can be useful.
I hope you can help me

Thanks
Victory

InputUser.xlsx (10.0 KB) OutUser.xlsx (8.7 KB)

#2

Hy @vittoria,

Try the following

  • Populate the Input file into a data table
  • Create a new data table
  • Loop through the imput data table and add rows to the output data table
  • paste the output data table to excel

I will post here a workflow for you in just a moment

Regards

#3

Hello @vittoria

  • use ReadRange Activity (workbook flavor) to read InputUser.xlsx.
  • add a datatrow with AddDataRow named "AuthorizationProfileId" and typed as you need it
  • add a datatrow with AddDataRow named "AuthorizationProfileName" and typed as String
  • use ForEachRow Activity (with the previous obtained DataTable).
  • for each row, Assign row("AuthorizationProfileName") = row("Name").ToString & " " & row("Surname").ToString
  • after the ForEachRow, use FilterDataTable and in the second tab, check “keep” and add “UserId”, “AuthorizationProfileId” and “AuthorizationProfileName”.
  • use WriteRange (workbook flavor) to create your OutUser.xlsx

I don’t know how you get AuthorizationProfileName but you can probably handle it in the loop.

The idea is to work with datatables, adding your target column as needed, fill them according eadh occurence then filter and manage columns before dumping the obtained datatable to a file.

#4

Hy @vittoria,

Check a look at my workflow

UserInputOutPut_Test.zip (36.8 KB)

I have just one question: Where does the filed “AuthorizationProfileId” comes from?

Regards

#5

Hi @William_Blech_Sister ,
thanks for the example I will try it soon.
The “AuthorizationProfileId” field is a progressive number but I don’t know how it can be generated so I have to search the forum, do you know what activities to do this thing?
thanks
victory

#6

Hi,
I have tested it and it actually works the only thing is that in the output file there are duplicate lines, is there a way to remove them with a query and leave only one ?
thanks

#7

Hy @vittoria,

I am glad I could help you :slight_smile

I use the append range activity to paste data in excel. It means it will always put the new data below the existing one. Ok?!

1 Like
#8

@vittoria

with dt as your datatable,

dt = dt.DefaultView.ToTable(True, "field1name_to_keep", "field2", "etc")

EDIT: there is also a Remove Duplicate Rows activity.

image

2 Likes
#9

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.