Help importing .csv to Teradata (system.OutOfMemoryException TDP 4.0.0.624)

Toad Data Point & Toad Intelligence Central
1

Any help is appreciated in order to help me get my data imported. Thank you in advance.

I sporadically get a ‘Toad Data Point Error Message’ and ‘Toad Error Message’ when attempting to import .csv to Teradata existing table using Toad Data Point 4.0.0.624 Import Wizard. I get the error whether the .csv is on a network file share or local. The .csv is about 125MB and 20000 rows. I’ve tried allowing TDP to ‘Optimize block size’, but that fails every time so I have manually set the ‘Processing row count’ to 5000. The .csv is generated by an upstream reporting system, and I’m attempting to load it several times per week until we get an automation solution in place.

This is on a Dell Precision 7510 with 16GB RAM and 7GB free while running this import.

Error text and log below:

Toad Data Point Error Message

System.OutOfMemoryException
Exception of type ‘System.OutOfMemoryException’ was thrown.
Stack Trace:
at System.Drawing.Graphics.get_Clip()
at System.Drawing.GraphicsContext…ctor(Graphics g)
at System.Drawing.Graphics.Save()
at System.Windows.Forms.Control.WmPaint(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at DevExpress.XtraBars.Controls.DockedBarControl.WndProc(Message& msg)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

Toad Error Message

Toad Data Point Error Message

System.OutOfMemoryException
Exception of type ‘System.OutOfMemoryException’ was thrown.
Stack Trace:
at System.Diagnostics.NtProcessInfoHelper.GetProcessInfos(IntPtr dataPtr)
at System.Diagnostics.NtProcessInfoHelper.GetProcessInfos()
at System.Diagnostics.ProcessManager.GetProcessInfos(String machineName)
at System.Diagnostics.Process.EnsureState(State state)
at System.Diagnostics.Process.get_ProcessName()
at DevExpress.Utils.FormShadow.FormShadow.get_IsDesignMode()
at DevExpress.Utils.FormShadow.FormShadow.get_ShouldShowShadowWindow()
at DevExpress.Utils.FormShadow.FormShadow.UpdateShadowWindowPositions(Boolean delayWhileMove)
at DevExpress.Utils.FormShadow.FormShadow.OnUpdate()
at DevExpress.Utils.FormShadow.FormShadow.EndUpdate()
at DevExpress.XtraEditors.XtraForm.InitFormShadow()
at DevExpress.XtraEditors.XtraForm.UpdateFormBorderEffect()
at DevExpress.XtraEditors.XtraForm.OnLoad(EventArgs e)
at Quest.Toad.Gui.BaseForm.OnLoad(EventArgs e)
at System.Windows.Forms.Form.OnCreateControl()
at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
at System.Windows.Forms.Control.CreateControl()
at System.Windows.Forms.Control.WmShowWindow(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
at System.Windows.Forms.Form.WmShowWindow(Message& m)
at System.Windows.Forms.Form.WndProc(Message& m)
at DevExpress.XtraEditors.XtraForm.WndProc(Message& msg)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

Toad Data Point Error Message

System.OutOfMemoryException
Exception of type ‘System.OutOfMemoryException’ was thrown.
Stack Trace:
at DevExpress.XtraBars.Painters.BarPainter.DrawLink(GraphicsInfoArgs e, BarControlViewInfo viewInfo, BarLinkViewInfo item)
at DevExpress.XtraBars.Painters.BarPainter.Draw(GraphicsInfoArgs e, CustomViewInfo info, Object sourceInfo)
at DevExpress.XtraBars.Controls.CustomControl.DirectDraw(PaintEventArgs e)
at DevExpress.XtraBars.Controls.CustomControl.OnPaint(PaintEventArgs e)
at DevExpress.XtraBars.Controls.CustomBarControl.OnPaint(PaintEventArgs e)
at System.Windows.Forms.Control.PaintWithErrorHandling(PaintEventArgs e, Int16 layer)
at System.Windows.Forms.Control.WmPaint(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at DevExpress.XtraBars.Controls.DockedBarControl.WndProc(Message& msg)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

Log data

08:29:36 Thread (1) Connecting to :EFB0371@TERAFRZ1.ca.boeing.com (BEDW_POC_EFBI_METRICS_T).
08:29:36 Thread (43) Import Started [2017-05-23 08:29:36]
08:29:36 Thread (43) Processing “EFBI_COGNOS_CONTENT_CLEAN.csv” into “BEDW_POC_EFBI_METRICS_T.COGNOS_CONTENT_CLEAN”
09:06:53 Thread (43) The rows failed importing will be written to file : \NW\data\EFBI\EFBI_Data_Transfer\NetVisn_to_EFBI\EFBI_COGNOS_CONTENT_CLEAN_rows_with_error.csv
09:06:53 Thread (43) Importing 1 out of 1 files
09:06:54 Thread (43) Reading from file EFBI_COGNOS_CONTENT_CLEAN.csv
09:06:58 Thread (43) Failed to parse the file.
Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index
09:06:58 Thread (43) Nothing to discard, delete \NW\data\EFBI\EFBI_Data_Transfer\NetVisn_to_EFBI\EFBI_COGNOS_CONTENT_CLEAN_rows_with_error.csv
09:37:55 Thread (43) Error(s) occured, no rows were imported.

09:37:55 Thread (43) Error importing data, please check file format options: Failed to parse the file.
Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index
09:37:55 Thread (43) Import Finished [2017-05-23 09:37:55]
09:37:55 Thread (43) Import time: 01:08:19.2423486

2

I see this in the log.

09:06:54 Thread (43) Reading from file EFBI_COGNOS_CONTENT_CLEAN.csv
09:06:58 Thread (43) Failed to parse the file.
Index was out of range. Must be non-negative and less than the size of the collection.

If this csv file is made from another app I suspect that there are a mismatch of columns or values that should be quoted. We are not able to parse the file and probably running out of memory because we don’t see the proper end terminator.

Can you post the csv file so I can have a look? Or can you import to excel and see if it can parse and fix the formatting?

3

Yes, I saw that also but was unable to determine why Toad would error and fail so ungracefully due to possible bad formatting. I’ve gotten ‘Failed to parse the file’ messages before that were not accompanied by the System.OutOfMemeoryException application error.

I opened the .csv in Excel with no problem (I usually do that to do a quick sanity check on the data). This time I then saved it back to .csv from Excel and the file size dropped from 133049KB to 125963KB so something definitely changed.

I will attempt importing again tomorrow morning with the newly (re)saved .csv and see if just opening and saving in Excel fixes the issue.

I’m sorry I’m unable to post the .csv due to proprietary information.

4

Hi there,

Even I faced those issues in the past. Also happened in two different scenarios. When the application starts as well when I tried to use Import wizard, in these both scenarios, I faced “System.OutOfMemoryException” .

I suspect there could be nothing wrong with your .csv file or either with toad. And thats a nice call you mentioned your system configuration too. 7GB free while running is a good thing.

Still, clear the temp data in your system in case if any, and restart the toad once. It worked for me that time.

Even after that if you cant get rid of, take to Quest developers forum. That might help then.

5

Dealing with csv files I have found UltraEdit is the tool to use. You can see this product at www.ultraedit.com

You can also see how to deal with csv files in this video. https://www.ultraedit.com/support/tutorials-power-tips/ultraedit/csv-files.html

I have found that when you open a csv file in Excel, it changes the structure of the file somehow.

6

Interesting. I am still trying to determine if an exact set of actions will set this off.

I restart Toad and still get the issue. Sometimes if I run Toad first thing after a reboot, it will be fine, but sometimes it’s also fine 6 hours into my day.

Thanks for the developers forum tip.

7

Thanks for the tip of using a text editor. I’m not allowed to use UltraEdit at my company, but I do use Notepad++.

I know Excel changes things, which is why I usually just open the file to view, but do not save it. I saved it this time just to see if the changes Excel made behind the scenes will fix the issue I’m having.

After looking at the original and the one just opened and saved in Excel, it looks like Excel removes a bunch of text qualifier quotes and only leaves them when they enclose a comma (column delimiter).

The import this morning failed gracefully with log error “08:52:02 Thread (18) Failed to parse the file. Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index”

Back to the drawing board I guess…

8

PS. You can always upgrade to TDP 4.2 and use the 64-bit application. The 32-bit one you are using does not use all available process space and memory if you have 64-bit OS.

9

Thanks Debbie, I’m on Win7 64-bit and the toad.exe is running in 32-bit space and is installed in Program Files (x86) so I assume it’s actually 32-bit. The internal company software library that installed this TDP version said it was 64-bit, so I assume the library metdata was wrong. I will try manually installing TDP 4.2 via the .exe and see if that helps.

I did verify that the columns all match correctly, with or without the string quotations.

10

Would it be possible to post the csv file that is a problem. It takes very little time for us to see if we would have issues with the file. If you don’t want to do this, then open a support case and we will help you there. We would still need the file to investigate

11

I’m sorry I can’t post he .csv file, and it turns out I won’t have to!

Your prior suggestion of using TDP 4.2 64-bit version appears to have solved the problem!

I manually installed TDP 4.2 64-bit and the toad.exe is clearly not in 32-bit space, so our internal software repository was incorrect since the 4.0 that I had was definitely 32-bit.
Using TDP 4.2 x64, I imported and saved the template .tim for a newer .csv that just became available (2017-05-23 data) from the source and that was successful.
I then used that same new import template .tim file to import the previos problem .csv (data from 2017-05-22) and it imported correctly.

12

yAy[H]

13

Thank you again - Just following up that I have had no issues loading historically-created data or new data from the upstream tool.