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.
Any ideas?
Thanks,
Moritaka Kanai
**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.
Accepted Solution
Pegasystems Inc.
US
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:
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.
Updated: 12 Sep 2019 5:27 EDT
ServiceNow
JP
Thank you for your great help! I could successfully executed the scenario you indicated as the following movie. (included in attachment)
For the other people's reference, I share my project file. (attached)
Unzip the file, and place dummy.xlsx (empty Excel file) and MaezeroTestComma.csv under C:\temp, then you can run the whole scenario.
Pega Robotics version: 8.0.1025
Thanks,
Moritaka Kanai
***Moderator Edit-Vidyaranjan: Removed Box url and updated content***