Handling large CSV file

[SayeedBinAbdullah]

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

[Parthiban_Viatris24]

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

[SayeedBinAbdullah]

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

[Parthiban_Viatris24]

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.

[SayeedBinAbdullah]

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

[Parthiban_Viatris24]

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.

[faheemsd]

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.

[SayeedBinAbdullah]

@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

[SayeedBinAbdullah · Accepted solution]

@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

[SouravSaha]

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