Excel to Text

[a026833]

Hello, I have a process that reads an Excel file into a collection (good collection) and then proceeds to create a text row for each row in the collection. The text row consists of some of the data from the Excel row as well as additional data derived from the process. There is quite a bit of decision making within the process. This all works great with smaller Excel files however a 40,000 row Excel file will take almost 4 hours to convert to text. Using append to text file page of the File Management Utility:

Text row build:
"D"&[good collection.NSCC Intermediary ID]&[24 spaces]&
[account number padded]&[9 spaces]&([good collection.CUSIP])&[21 spaces]&
[Trimmed BIN]&[BIN ind]& [mm]&[dd]&"20"&[yy]&[type]&" "&"1"&
[dollars padded]&[cents]&"USD"&
[another string]&" "&[new transaction id]&
[29 spaces]&
[good collection.SDR]&[Last of the record]&" " 

Any thoughts on how I might speed this up?

------------------------------
June Siravo
Analyst
FIAM
America/New_York
------------------------------

[PabloSarabia]

Hi @June Siravo

I understand that you have a general loop and in each iteration you write inside the text file, right?

As a general rule, opening and closing files line by line is usually much more expensive for the process.

Have you tried storing all the lines in a single data item and then writing it all at once?



Hope this helps you!!

See you in the community, bye 🙂

------------------------------
Pablo Sarabia
Solution Manager & Architect
Altamira Assets Management
Madrid
------------------------------

[a026833]

I will give this a try thank you for your response! I will let you know the outcome 🙂

------------------------------
June Siravo
Analyst
FIAM
America/New_York
------------------------------

[sahana.mohan]

Hi June,

if you are converting Excel to collection and then to Text,  have you considered converting the excel to CSV and reading the file?

------------------------------
Sahana Mohan
Solution Designer
Blueprism
------------------------------