topic RE: 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

PaulHumphreys — Fri, 07 Feb 2020 09:11:00 GMT

Hi

You could try populating the values with a leading single quote. you would need to convert to a text data item frist (if not already) but I have found that this often forces Excel to accept the value you input and not apply any formatting to it.

Not guaranteed to work but worth a try.

Thanks

Paul

------------------------------
Humphreys
Senior Technical Specialist
Europe/London
------------------------------

RE: Writing to a CSV

tim.jarvis-rouse — Fri, 07 Feb 2020 10:06:00 GMT

Excel needs a ' as a prefix to preserve leading zero in things like phone numbers this has been the case for XLSX for quite a while. for CSV your best option is to use the action "Get Collection As CSV" from the String Utility, this will output the collection to a text data item. When you have the text data item its just a case of using File Management Utility to "Write Text File"

------------------------------
Tim Jarvis-Rouse
Automation & Integration Consultant
VANAD GROUP
Europe/London
------------------------------