Memory error when trying to import a large .CSV file to a collection

[jpcrepeau]

Hi!

I need to automate a process where a user:

• Has en Excel sheet opened
• Imports a .CSV file, converts 5 fields to date before the import
• copies the full content of the import and pastes it into another excel sheet

The import of the CSV and the copy/paste part are not a problem, the trouble I am having is converting the columns into date. The CSV has about 25K rows and columns from A to DR. I wanted to Get CSV text as colletion with defined column already set as dates, but when I try to import, I get a memory error.

I also wanted to import CSV in Excel and modify the fields inside Excel, but I cannot find a function to chage a filed attribute to date in the VBO. I can spy the ribbon, but only in AA mode, so I am convinced there is a better solution.

Any help would be greatly appreciated!

J-P

 

[VJR]

Hi!

I need to automate a process where a user:

• Has en Excel sheet opened
• Imports a .CSV file, converts 5 fields to date before the import
• copies the full content of the import and pastes it into another excel sheet

The import of the CSV and the copy/paste part are not a problem, the trouble I am having is converting the columns into date. The CSV has about 25K rows and columns from A to DR. I wanted to Get CSV text as colletion with defined column already set as dates, but when I try to import, I get a memory error.

I also wanted to import CSV in Excel and modify the fields inside Excel, but I cannot find a function to chage a filed attribute to date in the VBO. I can spy the ribbon, but only in AA mode, so I am convinced there is a better solution.

Any help would be greatly appreciated!

J-P

Hi jpcrepeau,

In the Excel, what is the value prior to converting it to a date and what is the date value you need after the conversion. Can you provide three examples.

Or are you looking to change only the Format of the cells to Date which is currently showing as Text? Example, the right click Format Cells option of Excel.

The format of the values in Excel is possible to be changed to a date via Blue Prism. But note that some folks on the forum have reported that even though the value in Excel is a Date, the data type in the Collection remains as a Text which could be due to the way BP imports it into the collection. You may try that to see if things have changed now.

Regarding the memory error you might need to break the data into chunks (probably only two or so maybe sufficient) before proceeding.

 

[jpcrepeau]

Hi,

I am looking at modifying the value from General to Date.

I decided to go with splitting the file in 10K blocks, but I never know how big the file will be , so it's more work.

thanks!

 

[VJR]

Use the Format Cell action of the Excel VBO with the format as "dd/mm/yyyy" or any as required.
This can be done to all the rows on the sheet at once after finding the last row.