topic Re: Handling large CSV file in Product Forum

Handling large CSV file

SayeedBinAbdullah — Thu, 31 Jul 2025 06:58:32 GMT

Hi Everyone,

We’re working with a .txt file approximately 180MB in size, and need to perform the following operations efficiently given its volume, what will be the right approach to handle such files?

Cleanse the data by removing all "|" characters from each line.
Convert the cleaned file to a .csv format, ensuring all special characters are eliminated.
Aggregate currency values: The file contains over 300,000 rows across 10 currency types and 15 Columns. The goal is to compute the total sum for each currency and store the results in an output collection containing 10 records (one per currency).

Best Regards,
Sayeed Bin Abdullah

Re: Handling large CSV file

Parthiban_Viatris24 — Fri, 01 Aug 2025 09:56:35 GMT

Using OLEDB queries to interact with CSV files can be an effective solution for handling large-sized or data-heavy CSV files

Re: Handling large CSV file

SayeedBinAbdullah — Fri, 01 Aug 2025 10:40:39 GMT

Hi @Parthiban_Viatris24

Thankyou for the reply,

Could you also suggest how we can eliminate "|" characters from each line, as there are over 3,00,000 records in the file?

Best Regards,
Sayeed Bin Abdullah

Re: Handling large CSV file

Parthiban_Viatris24 — Fri, 01 Aug 2025 10:49:36 GMT

Try reading the file as a text file and replacing the '|' character with a blank in a single step. After the replacement, convert the text into CSV format. You can then either proceed with collection-based manipulation for further processing or save it back as a CSV file and use OLEDB queries to interact with the data and access the relevant information.

Re: Handling large CSV file

SayeedBinAbdullah — Fri, 01 Aug 2025 10:58:40 GMT

As this is a huge file will we be able to get the whole text in a data item and perform replace action in calculation stage and write it back to the text file?

Best Regards,
Sayeed Bin Abdullah

Re: Handling large CSV file

Parthiban_Viatris24 — Fri, 01 Aug 2025 11:41:19 GMT

Yes, you can proceed—there are no character limitations for Blue Prism Data Items with the Text data type. If you still encounter issues, we can address them using VB.NET or C#.NET code.

Re: Handling large CSV file

faheemsd — Sat, 02 Aug 2025 10:20:13 GMT

Dear @SayeedBinAbdullah ,

I'm happy to help you if I get a text file with a dummy data to give you the final solution on this.

Re: Handling large CSV file

SayeedBinAbdullah — Mon, 04 Aug 2025 03:01:30 GMT

@faheemsd

Thankyou for your response.

The file size is around 180 MB and we can only upload 5 MB max here,

I found some solution online and I'm working on it, will share it here if that solution works.

Best Regards,
Sayeed Bin Abdullah

Re: Handling large CSV file

SayeedBinAbdullah — Mon, 04 Aug 2025 10:06:48 GMT

@faheemsd @Parthiban_Viatris24

I'm currently using below custom code to accomplish this. I’d appreciate any suggestions to help fine-tune the implementation or ideas for alternative approaches.

Code to Replace "|" character

Here I'm reading each line from ".txt" file replacing the "|" character and writing the updated content to a ".csv" file.

Input Parameters

Output Parameters

Code to Fetch Records in Collection

Input Parameters

Output Parameters

Final Output

Best Regards,
Sayeed Bin Abdullah

Re: Handling large CSV file

SouravSaha — Thu, 07 Aug 2025 06:14:09 GMT

The optimal solution to handle this kind of scenario, thanks @SayeedBinAbdullah for sharing detailed explanation!