When trying to import data using an import template, I get the following error in the event log:
3:59:29 PM Thread (24) Importing 186 out of 292 files
3:59:29 PM Thread (24) Reading from file Copy (5) of U_Copy (13) of tstlck.xlsm
3:59:30 PM Thread (24) Exception of type ‘System.OutOfMemoryException’ was thrown.
3:59:30 PM Thread (24) Nothing to discard, delete C:\Documents and Settings\sanchezr\Desktop\ITALY_EXTRACT_STAGING\U_Copy (10) of tstlck_rows_with_error.csv
3:59:39 PM Thread (24)
Imported rows were rolled back.
3:59:39 PM Thread (24) Error importing data, please check file format options: Exception of type ‘System.OutOfMemoryException’ was thrown.
The template is designed to go against a folder and extract data from several worksheets that are in the workbooks files in the folder. I’ve confirmed that it does about 20 workbooks files without problem but when I get up to 200 files, it starts to throw this exception.
Any ideas on what’s causing it? I’ve attached the template that I’m using.
TEST.tim (12.8 KB)
On text files such as csv, fixed width and text, we stream the data and process in chunks so running out of memory is not an issue. However, for excel files we use a third part component and it reads the whole file. Do we know the size of file it is erroring on?
If you turn on Verbose logging in automation we may get more info but that will create a large log file.
Who generates the import files? Do you have a choice on those being csv as opposed to excel/xlsm? We have tested importing xlsm files but that file type can contain formatting that cause issues.
I would first try to determine if a single file is too large to load all at once and import.
Then I would suggest isolating if the issue is with xlsm files.
Another approach would be to separate the imports into 3 or 4 Import templates, thereby breaking up the processing.
I don’t have a quick answer on the cause of the issue, but I am interested in tracking it down and resolving it.
Debbie
Hi,
I am generating the import files and its bascially a workbook with about 6 worksheets that represent extract files. I’m using Excel because that’s the format we’re using for data gathering. We send these excel files out and then they’re returned to us completed by the user. I’m then trying to use TDA to go through these files and pull the data into an Oracle 10G database.
The average file Excel file size is 63kb but I am using the Import wizard’s folder functionality to process about 300 files. It seems to do Ok with 2 or 3 files( I’ve only done boundry load testing so far).
I’ll try to break up the templates into seperate loads and see if that works.
Also, I did receive an email from Quest for Case #875156. I included some more information as well as file examples. Would you be able to see those or should I send them to you as well.
Thanks.
Hi,
Where would I be able to set the verbose option for logging?
Go to the script settings and set the logging level to verbose. See screenshot.
[cid:image001.png@01CB9871.3DCEF1D0]
I can get the files from the support case but I do not see them there yet. Perhaps you have sent them but Chad has not attached them. Since you are working with support please tell Chad to notify me when he has the files and I will pick up the case.
Debbie
