How to copy multiple columns from an Excel File and paste them in a webapp without losing the format?

Hi,

I think the question title is self-explanatory. I have an Excel file with some values gathered in more than one column in different rows and I need those values to be copied and then pasted into a webapp form that mimics the format of a table.

Here’s a snippet view from the Excel:

image

Here’s the form where I need to copy the stuff from the Excel:

image

Whatever comes from the Excel, it has to be “copy-pasted” with the exact and original format into the form. Somewhere in the forum I saw a way to just copy one column:

I’m not being able to find a way to copy more than one column. Does anybody knows a useful syntax and easy way of doing that?

Thanks in advance.

If you were to do this manually, how would you do this? Selecting the range then Ctrl+C, and Ctrl+V on the web form? You can take the same approach with UiPath, using the Select Range activity, then Send Hotkeys.

Anyway, if you would like a different approach, let us know. This will also depend on how your web form interacts with a user, so we would need more info on how the web form works - for example, you could type in each individual value using a loop.

Regards.

Can you past the values “by hand” all at once?
May be you can use the clipboard-activities.

Hi @ClaytonM and @rost, thanks both for your feedback.

In regarding to the tips provided, although it works for example with the selecting range activity and the send hotkeys, it’s not suitable for this case. I need a solution or approach more similar to the use of String.Join like it is seen on the shared screenshot.

Is there any way of keeping the format while doing that solely with some built in .Net method?

Cheers.

Maybe, to concatenate first all row-entries with “\t” for a tabulator and than stick all rows with “environment.newline” togehter, will work for you…

1 Like

In your screenshot, you are using “Set to Clipboard”… that is equivalent to performing a Ctrl+C on your datatable. If you were to set a String to the Clipboard, you won’t have any cell information, which is why I was asking “how do you do this manually?”. - I don’t know how you would get the cells to paste into your form unless the Clipboard was cell data rather than a delimited string.

However, if you do want to store your table to a string, you will need to use the Output Data Table activity, and will be a comma-delimited string. To get the format correct, you might need to perform a Save As to a .CSV file format on the Excel file with the correct format settings. - This will store the formatted cell data as it shows in Excel, because Excel automatically changes the value to lose its formatting. Then, you can use Read CSV or Read Text File to get the data with the correct formatting.

And, like I said you can always use the Output Data Table if you want to convert the table to a string. - although, I don’t know how this will help interact with the web form since it will have a delimiter and will be text.

Additionally, the reason I suggest converting the file to CSV to keep the formatting is because the alternative would be to use a For each loop to change each value individually. - you can’t really use LINQ to change the values in a Data Table (you can do this in string form though, however by splitting the string by the delimiter and newline character, then re-concatenating it back together; I try to avoid this method cause the code gets bulky).

Maybe these ideas are helpful? Let us know if there is a different idea you have in mind. Typically, you need the data in a certain format, and the two options are to fix the format as you process/type it (most common) or to save as the data to a CSV format (assuming you are don’t want to use copy/paste). Then, you run the datatable through a For each loop to process each row (you can make this fairly fast by changing the Delay Before and After properties of a TypeInto activity to Zero. - but I believe you are trying to not use a For each loop.

Regards.

1 Like

Thanks @rost, good tip.

Thanks @ClaytonM , great answer with many tips and tricks for this scenario. Answering to your question, all those ideas were helpful. I think I’ll try the CSV solution, nevertheless, using the “tabulator” solution also works good enough for what I’m looking to do at the moment.

image

2 Likes

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