Blue Prism Product

last person joined: yesterday 

Chat about Blue Prism products
Expand all | Collapse all

Working effectively with CSV in BP to avoid any memory issues

  • 1.  Working effectively with CSV in BP to avoid any memory issues

    Posted 09-21-2020 01:47
    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
    ------------------------------


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

    Posted 09-21-2020 09:35
    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
    ------------------------------



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

    Posted 09-21-2020 14:13
    @ 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
    ------------------------------



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

    Posted 09-21-2020 14:56
    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
    ------------------------------



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

    Posted 09-22-2020 12:46
    You could achieve it with this flow.







    The result is something like the following



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



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

    Posted 09-23-2020 01:40
    @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
    ------------------------------



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

    Posted 30 days ago
    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
    ------------------------------