2

I am having an issue when writing a datatable to power automate desktop, the issue occurs when a cell has a large number, since it converts the data to exponent. Below is how I am writing the datatable to excel.

enter image description here

The datatable as it appears on PAD before pasting it in excel.

enter image description here

This is how the data appears in excel

enter image description here

How do I prevent PAD from reformatting the data before pasting as now its distorting a reference which is creating an issue in referencing.

mulikevs
  • 181
  • 2
  • 17
  • 1
    Looks like it's a parsing issue rather than a formatting issue. Try wrapping the value to be written to Excel in [`TOSTRING()`](https://learn.microsoft.com/en-us/power-automate/minit/string-operations#tostringintformat). – jsheeran Apr 17 '23 at 09:58
  • That doesn't work converting a datatable to string inserts the whole datatable in one cell. – mulikevs Apr 17 '23 at 11:23
  • Can you add a step to format the Excel sheet's first and third columns as TEXT BEFORE pasting the data? – Scott Craner Apr 17 '23 at 15:23
  • What action do you use to format the columns in power automate as its not in the Excel Actions? – mulikevs Apr 18 '23 at 08:20

1 Answers1

0

If you know which column may potentially have either number or alphanumeric type data, you can prepare a template Excel file in advance with text format (Excel: "Format Cells" => "Text") and have PAD to write the data on it.

Following is how it worked on my end when I wrote the data from PAD to the formatted Excel file.

enter image description here

enter image description here

enter image description here

Koki
  • 109
  • 1
  • 6