Problem in writing the decimal values in excel

[kavinnatarajan]

Hi Guys,

I tired to populate the double value as 55.90 in excel.

when i tried to populate using write cell activities, it is taking only 55.9 by ignoring the trial zero.

But as this relates to the cent value. i need the zero to be at the end.

Thing is that i cant format the column value like placing " ’ "in the end or something like that as bcoz it an input downloaded file.

Any idea?

[ClaytonM]

That’s a feature of Excel.

Ideally, you would simply just format the column to 0.00, which doesn’t change the value. Then, either save it to a CSV to keep the formatted values or leave it in Excel like that.

I can understand though if there is a requirement for an Excel file (not CSV) to have the value as a string. For that, you will need to change the value using a Read Range to get the Data Table, then a ForEach loop with an Assign to change the value with a ' infront, then Write Range that data back. - Note: this will leave a green mark next to the cell.

so, it depends on what is required. And, by required, I mean the process for the associate can not function. I don’t recommend adding the ' infront if it’s not required, and just format the column using the Format Cell features in Excel. And, reminder, you can also Format it then save it as a CSV; Note: if it is already a CSV, open it in Notepad to see the true value, because Excel will format it on you.

Regards.

[amarasto (Aman Rastogi)]

Hi kavinnatarajan,

There is a hack which i used and worked for me.I created a excel template file to write the data and used it in the process.

The column where i dont want excel to take the decision and apply the formatting i fixed the column format to Text using format cells which resolved the issue.If the Column format is set to general in that case excel itself applies the formatting but if we set it to text then we restrict the excel to apply the default data formatting.

May be you can also give it a try and let me know if it does work for you as well.

[neonova]

You can run a small macro to change the column format to numbers when you open the excel using Invoke VBA activity.

Then when you read into a data table the right ones would come

[kavinnatarajan]

Thanks Everyone !!! i don’t want to complicate my flows using power shell script but rather I just concatenate " ’ " in front of the string variable while using the write cell activity.

So that when it populates in the excel sheet it would take all the decimal values especially trail zeros.

Thanks,
Kavin.

[ClaytonM]

To do this, I suggest not using Write Cell, but using a Data Table.

Here is a quick example:

Excel Scope Read Range, store in DataTable variable
    For each row In dt1
        Assign activity: row("columnname") = "'"+If(IsNumeric(row("columnname").ToString), CDbl(row("columnname")).ToString("0.00"), "0.00")
    
    Write Range, use the DataTable variable and overwrite the excel file.

Hope that explains it well for you.

Regards.