Simple excel exercise

datatable
excel
activities

#1

Hi all,

I would like to test the following flow in excel, but I’m not seeing how to do it:

  1. Open a csv file
  2. save it as an xlsx file
  3. change the headers of the columns to new names (half of all the columns)
  4. hide certain columns
  5. Delete the 3 bottom rows that contain values
  6. Sort on more than 1 field

I’m having some issues for starting with point 3 and 4 and 5. Does anyone has some tips or maybe a file where you are performing a similar exercise?

Thanks in advance!
Have a nice day.


#2

Hi,

point 3 - can be done at the datatable level once read in using datatable.coulmn.columnname to change it.
point 4 - Previous Thread
point 5 - again can be done at datatable level - simple use the remove data row if it meets the condition.

Hope that helps,

Thanks,

Tim


#3

Hi! The point 3 doesn’t works for me, so I have used a write cell activity instead. If you could please post a screenshot of the correct syntax or send a .xaml file…


#4

Hi TimK, thanks for your response.

I am trying point 3 as well but I am not certain in which activity I should be using the datatable.column.columname. Is it an assign activity? Feel free to share a xaml file as Niket_Ghai also asked.

Kind regards


#5

You can try Write Cell activity…for point 3


#6

If you don’t know the column index
Find the column index: DataTable.Columns.IndexOf(“Column 5”)

Then use the below in an Assign Activity

DataTable.Columns(Column Index).ColumnName= ‘ABC’


#7

Thanks for your thoughts guys! Really helpful.
I managed to succeed for point 3 and 4 already :).
I’ll be having a look at point 5 later on.

In the meantime I’m having an additional question.
The original data comes from a CSV file and contains numbers.
However, the numbers in column range T:AT are in the format ‘100.00’ instead of ‘100,00’. I can only calculate further with ‘100,00’ (comma numbers).

I would like to do a find and replace all on range T:AT and replace . by ,
I tried with hotkeys but it was not working, or I was missing something…
I also tried with the activity ‘select range’ but then I got stuck as I did not know what to do next.

Can you please share your insights on this?

Thanks in advance,
Kind regards


#8

The workflow attached here changes the particular fields based on some condition:
Try to understand it and tell me if this works for you as well…you just need to make some changes
Replacing.xaml (8.5 KB)


#9

Hi Niket,

Thanks for your response.
I changed the flow but the bot seems to be working for a long time.
I selected range “A:AT” and I guess that he scans all the cells (even blank ones) to search for something to replace. However, in this file I only have about 30 rows, but next file could be 5.000 rows. Any suggestions to this?

Thanks.


#10

If the number of items increases the loop will take more time! I’ll get back to you if I find a better way to do this!
Regards

Niket Ghai