topic Writing to a CSV in Product Forum

Writing to a CSV

maneesh.vemula1 — Thu, 06 Feb 2020 20:01:00 GMT

Hello - I am trying to write a collection value to a CSV file. The collection has a field that has a value ex:01014234210015002. I am using MS-Excel VBO actions create instance>openworkbook>writecollection>save>close instance. However, when i go back and open the excel file, the excel is automatically appending 0's at the end to that value (ex:01014234210015000). I tried to use format cell after writing collection to see if i can change the format to text, but that did not help. The formats i tried with format cell are '0' and '@'. I've observed that this behavior only happens in excel if the number value exceeds 15 digits. Please let me know if there are any other ways to solve this issue.

Thanks in advance.

------------------------------
Maneesh Vemula
RPA Developer
------------------------------

RE: Writing to a CSV

ChristopherRoja — Thu, 06 Feb 2020 21:23:00 GMT

Hello,

I would recommend using Text format on the cells prior to input the data as per: https://support.microsoft.com/en-us/help/269370/last-digits-are-changed-to-zeroes-when-you-type-long-numbers-in-cells

That is a behavior that Excel have for number after the 15 digits long due to IEEE 754 especification, probably the csv format is causing issues when changing the format of the cell as well. If not crazy maybe using other file format as a test could help, like xlsx.

Hope this is somewhat useful.

Thanks,

------------------------------
Christopher Rojas
------------------------------

RE: Writing to a CSV

maneesh.vemula1 — Thu, 06 Feb 2020 21:54:00 GMT

Hi Christopher - Thanks for the reply. I will definitely give that a try. However, If i have to input multiple rows and columns with #'s more than 15 digits - is there a utility or some modification to a code stage that I can do to format multiple columns into text before writing the collection instead of just a single particular cell(ex: format cell functionality in MS-Excel VBO)?

------------------------------
Maneesh Vemula
RPA Specialist
America/New_York
------------------------------

RE: Writing to a CSV

Gerald_J — Fri, 07 Feb 2020 07:30:00 GMT

Hi Maneesh ,

The below steps will resolve your issue. Find the attachments for better under standing.

Steps to be followed:

1. Navigate to MS Excel VBO - Format cell

2. Duplicate Page- Format cell and rename as Format Cell Range

3. Create two data item(text) cellstart and cellend and delete cellref data item.

4. Open code stage format cell and rename as format cell range, remove cellref in input tab and initialize the two data item (cellstart and cellend)

5. Modify the code as shown in the screen shot

6. Initialize the created two data items in start stage and publish the object.

7. Open process studio create a flow include Format cell range action in process before writing 16 digits value and pass the input parameters.

8. On Format give value as "@" this will set the cell range format as text.

Gerald | Automation Engineer

Exponential Digital Solutions Pvt. Ltd.

4th Floor, Jyothirmaya Building, Infopark Phase 2, Brahmapuram,

Kochi – 682303

+91 9159842805 (M)

Gerald.J@10xds.com | www.10xds.com

#WeAreExponential

------Original Message------

Hello,

I would recommend using Text format on the cells prior to input the data as per: https://support.microsoft.com/en-us/help/269370/last-digits-are-changed-to-zeroes-when-you-type-long-numbers-in-cells

That is a behavior that Excel have for number after the 15 digits long due to IEEE 754 especification, probably the csv format is causing issues when changing the format of the cell as well. If not crazy maybe using other file format as a test could help, like xlsx.

Hope this is somewhat useful.

Thanks,

------------------------------
Christopher Rojas
------------------------------