Importing large amounts of GL journal data into Sage 300 2014 PU5

[PhilDOakley]

I have found some interesting things while trying to import a very large amount of data (circa 350,00 csv lines as there are 2 lines of optional fields for each entry) into a GL journal batch as one entry from a CSV file.
The standard 300 GL journal import process never releases memory and eventually runs out at about 1.8gb. I think this is probably the upper limit for a 32 bit app.
So I decided to write a VBA macro to import the file and do it in smaller chunks. What I have found is the inital creating and import consumes all the memory. However, if I create the batch and then just add to it it does not consume nearly as much memory and apears to grow and then fall back. The imitial creation and import is fairly fast. The adding to an existing batch is very slow!
I wrote this using a VBA macro as it was the quickest way to test this. My intention is to move it over to c#.
The speed this runs at is probably going to mean this is not a viable solution and I do not want to invest the time in writing the c# option if it is going to be just as slow.

I feel there must be hope as the GL consolidation program imports large amounts of data into a GL journal and in a reasonable amount of time.

Has anyone got evidence that using the COMAPI is very slow and is there a better alternative that will yield a massive speed improvment?

 

[ettienne]

I regularly import large numbers of GL transactions (millions of records in data conversions) and don't see any of this.
I use VBA and write to the GL batch, header and detail views - I specifically avoid using the GL import (or any import) function because of its limitations.
So when you say import do you literally mean the Sage 300 import function - or are you using the views to create transactions?

Sage 300 Certified Consultant

 

[ettienne]

There's a PU5 for 2014?

Sage 300 Certified Consultant

 

[PhilDOakley]

Ettienne
Thanks for the comment. By import I mean the standard GL journal import from within the journal screen.
Yes there is a PU5 for 2014 as this is what is installed lol
My feeling is that the issue might be caused by the optional fields as I have other import without opt fld that have not been an issue.

 

[ettienne]

If you do this on a regular basis then I would suggest that you avoid using the import engine, it has its limitations.
If you have any VBA or coding experience then look at creating GL batches using the API, it is much faster.
Start be recording a macro while you manually enter a GL batch, then clean up the recorded code and add your logic. Creating GL batches is certainly one of the simplest to do and a good starting point from a learning perspective.

I'm lagging behind with PUs, not keeping so up to date since I'm focused on NetSuite at the moment


Sage 300 Certified Consultant