topic RE: Excel Split macro in Product Forum

Excel Split macro

Preranajain — Fri, 27 Jan 2023 07:38:00 GMT

I have an excel split macro file, it splits a file which contains data of around 25,000, the data is split into batches of 150 each. The batch file is in .csv format.

The input file data keeps increasing weekly.

But the concern is that after preparing 157 batches, the macro goes to next stage, leaving the remaining data as it is.

------------------------------
Prerana jain
------------------------------

RE: Excel Split macro

PvD_SE — Fri, 27 Jan 2023 07:54:00 GMT

Hi Prerana,

That sounds more like a XL macro problem than a BP problem.... Unless I misunderstood you of course. In that case: can you explain more what happens in your process?

------------------------------
Happy coding!
---------------
Paul
Sweden
------------------------------

RE: Excel Split macro

Preranajain — Fri, 27 Jan 2023 10:20:00 GMT

The process takes split macro file and divides the main excel containing the data into batches.

As the url on which the data needs to be uploaded supports only 150 accounts.

attching the screenshot of process.

Yes, the macro is having the issue & not the tool.

------------------------------
Prerana jain
------------------------------

RE: Excel Split macro

PvD_SE — Fri, 27 Jan 2023 10:27:00 GMT

Aha.

Am I correct in understanding that new data is constantly being added to the XL, or do you get an XL with 25k rows and split it up while the XL does not change?

------------------------------
Happy coding!
---------------
Paul
Sweden
------------------------------

RE: Excel Split macro

PvD_SE — Fri, 27 Jan 2023 11:30:00 GMT

Hi again,

If the macro does nothing else than picking the next 150 rows and puting them in a CSV and the marco liekly the culprit for the bad behavior of the process page, you might consider the following:
Remove the macro from the XL. Then, have the process copy all XL rows to a collection (by using OLEDB, or with the 'VBO XL Fast Copy' action), then let the process create the 150-row-blocks in CSV format.

------------------------------
Happy coding!
---------------
Paul
Sweden
------------------------------

RE: Excel Split macro

michaeloneil — Fri, 27 Jan 2023 16:58:00 GMT

hi @Prerana Jain

I agree with @PvD_SE on this, if the macro is only chunking up the data to 150 records for a url that only allows that many at a time then let BP handle the heavy lifting. As paul suggested you can get the rows out of the file and let BP write them to the URL or you can probably add these to a work queue which will give you better control over exceptions, retries etc and let you know which ones have been done and which have yet to be completed. Using BP to initiate a macro on a file more often than not just creates more problems than its worth, for one thing if the macro throws an error BP will usually just hang there with no way to escape from it which means your process cant end potentially impacting run time for other processes.

------------------------------
Michael ONeil
Technical Lead developer
NTTData
Europe/London
------------------------------

RE: Excel Split macro

ÖzençGezgin — Mon, 30 Jan 2023 14:17:00 GMT

Hi,

Maybe you can try "Split Worksheet" activity in MS-Excel VBO... Than you can use this files in a loop.

------------------------------
Özenç Gezgin
Supervisor
Türkiye İş Bankası A.Ş.
Europe/Istanbul
------------------------------

RE: Excel Split macro

ÖzençGezgin — Thu, 02 Feb 2023 19:43:00 GMT

Hi @Prerana jain

Can you try "Split Worksheet" activity in MS-Excel VBO... Than you can use this files in a loop.

------------------------------
Özenç Gezgin
Supervisor
Türkiye İş Bankası A.Ş.
Europe/Istanbul
------------------------------