topic RE: Working effectively with CSV in BP to avoid any memory issues in Product Forum

Working effectively with CSV in BP to avoid any memory issues

MayankGoyal2 — Mon, 21 Sep 2020 05:47:00 GMT

Hi All, I have a CSV file with 20 columns and 24k rows. I have to update column names of this CSV and save it as xlsx with a particular file name and sheet name. I dont want to read csv in collection to avoid any memory issues. Kindly suggest on a better solution for the same. Is there any VBO to work directly with CSV for updating column names and saving it as excel.

------------------------------
Mayank Goyal
------------------------------

RE: Working effectively with CSV in BP to avoid any memory issues

NicholasZejdlik — Mon, 21 Sep 2020 13:35:00 GMT

You should be able to open an Excel instance and load the CSV file via Excel. You could use the Excel functionality to rewrite the column names in row 1, then save it as an XLSX. You'd avoid having to load the collection into Blue Prism altogether.

------------------------------
Nicholas Zejdlik
RPA Developer
------------------------------

RE: Working effectively with CSV in BP to avoid any memory issues

MayankGoyal2 — Mon, 21 Sep 2020 18:13:00 GMT

@ Nicholas Zejdlik - Thanks a lot for your response. I was thinking the same design but not sure whats the most effective way of updating all columns of row1. There are current column headings which I have to update with new column heading, like a one to one replacement of heading for all 20 columns.

------------------------------
Mayank Goyal
------------------------------

RE: Working effectively with CSV in BP to avoid any memory issues

NicholasZejdlik — Mon, 21 Sep 2020 18:56:00 GMT

You should be able to extract the headers on row 1 via a calculation stage. This should pull the row using this:
Left([CSV], InStr([CSV], NewLine()))

You could then call CSV to Collection on the header row, which would give you an empty collection with column definitions (so it wouldn't have any data that could potentially lead to an out of memory exception). Perform the column name updates on the empty collection, then call Collection to CSV to switch it back to CSV. Finally, merge the updated header CSV with the rest of the data like this:
[Updated Headers] & Right([CSV], Len([CSV]) - InStr([CSV], NewLine()) + 1)

Alternatively, you could use the Replace() function in a calculation stage to update the headers instead of loading them in as a collection, but that could lead to unwanted results depending on your column names.

There may be some easier ways to do this, but this should work.

------------------------------
Nicholas Zejdlik
RPA Developer
------------------------------

RE: Working effectively with CSV in BP to avoid any memory issues

MiguelCarrillo — Tue, 22 Sep 2020 16:46:00 GMT

You could achieve it with this flow.

The result is something like the following

------------------------------
Miguel Carrillo
------------------------------

RE: Working effectively with CSV in BP to avoid any memory issues

MayankGoyal2 — Wed, 23 Sep 2020 05:40:00 GMT

@Miguel Carrillo - Thanks a lot for your response, if I have existing column names and have to replace it with new column names after finding it in Row1 which action should I use from excel VBO.

------------------------------
Mayank Goyal
------------------------------

RE: Working effectively with CSV in BP to avoid any memory issues

MiguelCarrillo — Wed, 23 Sep 2020 23:01:00 GMT

You need to use the Action called Set Cell Value

where you can specify the cell Reference (the cell you want to change, eg. A1, B1, C1
and the new value for that cell.

------------------------------
Miguel Carrillo
------------------------------