[UiPath] How to paste tabular data from Clipboard to Excel/Create DataTable from Clipboard

Learn
#1

In this article I would love to show you:

  1. How to store a table from clipboard (eg. from SAP or any other tool) in a String variable.
  2. How to work with Generate Data Table From Text activity in order to transform that String to DataTable
  3. How to write the result in new Excel file.

Get tabular data from clipboard

Let’s say in your automation there is a step where you in some application select all the data in some table and you copy it to Excel. You copy the data from source application, then go to Excel, indicate first cell and Paste the data. The nature of the data will automatically format it in columns and rows.

But how to achieve the same in UiPath? When working with Excel automation we usually don’t go directly to open Excel and sending shortcuts (Ctrl+V) to enter the data. We rather work with DataTable variables and then write them to Excel files. Let’s see how to do that!

Once you are in the point where you know your tabular data is in the clipboard (probably after Send Hotkey - CTRL+C) add the Get From Clipboard activity. Create and assign output variable to store the text.

Use Log Message to log the result String. Double Click the Log messsage from “Output” pane to see its raw format. You will notice that there are strange characters prefixed with backslash \ .

As an example I copied this from Excel (but you can copy it from anywhere):
image

The result of these activities:
image
…looks in Output like this:
image
And if you double click it, you will get the raw version:

{
  "message": "id\tfirst_name\tlast_name\temail\tgender\tip_address\r\n1\tClayborn\tTinkham\tctinkham0@cdc.gov\tMale\t140.111.28.143\r\n2\tElianora\tLinning\telinning1@hhs.gov\tFemale\t170.47.237.200\r\n3\tTerrance\tDober\ttdober2@goo.ne.jp\tMale\t181.90.26.168\r\n4\tMichel\tLutton\tmlutton3@soup.io\tMale\t132.15.1.101\r\n5\tCassius\tButting\tcbutting4@icio.us\tMale\t189.145.144.34\r\n6\tMindy\tKingsly\tmkingsly5@unesco.org\tFemale\t123.206.58.37\r\n7\tAngeline\tGyse\tagyse6@kickstarter.com\tFemale\t109.56.112.201\r\n",
  "level": "Information",
  "logType": "User",
  "timeStamp": "14:50:53",
  "fileName": "Main",
  "processVersion": "1.0.0",
  "jobId": "c9a446ac-c3fa-4279-a9b4-3bd1891bf234",
  "robotName": "roman.hruska@robotict.com-attended",
  "machineId": 111788
}

Notice the part in “message”. This is your table but full of so called “special characters”.
\t is a TAB
\r\n is a NEW LINE (this could be different based on what system you are copying the table from)

So if you observe it closer you will see that the table in the Clipboard is stored in one line with these special characters telling to go to next column (TAB) and that there is new line comming (NEW LINE)

What you have now is kind of text from a CSV file (Comma Separated Value), but it is not separated by Commas but TABs. :wink:

Use Generate Data Table From Text to convert this String to DataTable

Setup new variable for the data table and fill the input too:
image

Hit the Options… button.
This will open a new window where you can adjust the settings. You can test how it works, but you have to supply “Sample Input” in the raw format (= copy the “message” from Log)
Important: From my experience the Sample Input and Preview does not always have good results. But try to experiment with these options:
image
And run the process. Setup it logically based on how your raw text looks like and I guarantee you success!

Write the table to Excel

Last step is to take the created DataTable variable and write it to Excel.
I go with using Workbook activities → Write Range.

This is how the result looks like:
image

Still not working?

Let me know if you have any issues following this tutorial. Always please provide some sample “raw” data that you are trying to paste and save.

Download the example workflow :arrow_down:

PasteTabularData.zip (58.6 KB)

1 Like