CSV file containing leading zeros
Can Excel connector open a CSV file without losing leading zeros?
When the CSV has data such as "0000001", then Excel launched by the Robot displays it as "1". If you use Excel application manually, then you can choose various options. But what about opening it with Robot's Excel connector?
It seems that there is no parameters in Excel object's Properties window or in Open/Start methods' parameters to specify this kind of thing.
**Moderation Team has archived post**
This post has been archived for educational purposes. Contents and links will no longer be updated. If you have the same/similar question, please write a new post.
To do this you will need to employ a script and a couple of automations that I describe in my https://pdn.pega.com/community/product-support/question/pega-robotics-excel-how post. The script is based on C# code from Brian Hart found at https://www.codeproject.com/Tips/136476/Import-a-CSV-File-Into-an-Excel-Workbook-Programma.
Here is the automation:
Create a script container to hold the ImportCSV script. Add the following references:
Add this script (see attachments for full script code). Make sure to update the line where the columnDataTypes values are set to match your import file:
script continued in the second graphic ...
With a little more work you can add the columnDataTypes as an input parameter to the script. Setting the columnDataType = 2 for the column with the leading zeros will preserve the leading zeros in the spreadsheet. Hope this helps.
Keep up to date on this post and subscribe to comments
- I imported data records from csv into the data type (Pega 7.2) with leading zeros. The property is defined as text, but pega report doesn't show the leading zeros.
- Remove leading zeros from an integer
- Pasting values to Excel removes leading zeroes
- How to set up the file listener to read values from CSV; create WOs and to save the result as CSV using Connect-FTP?
- Upload CSV File on UI from Data-Portal and then parse the CSV Records