1 Reply by Purgle Linham

  • Purgle Linham
  • Veteran
  • Offline
  • From: Wiltshire
  • Registered: 30-03-2009
  • Posts: 99

Re: Data import problem

Hi
I have just been importing a couple of spreadsheets created from a larger dataset, and the second of these got all the way to the 'do import' and then failed with the error message below.  Please can someone advise what this refers to and how to get round it as its not listed on the Rec6 website and we've not come across it before.

Thanks

Purgle
Head of WSBRC


date/time         : 2010-03-24, 17:13:17, 828ms
computer name     : WWTMG6
user name         : PurgleL
operating system  : Windows XP Service Pack 3 build 2600
system language   : English
system up time    : 7 hours 47 minutes
program up time   : 1 hour 39 minutes
processors        : 2x Intel(R) Pentium(R) D CPU 3.00GHz
physical memory   : 441/999 MB (free/total)
free disk space   : (C:) 27.07 GB
display mode      : 1280x1024, 32 bit
process id        : $63c
allocated memory  : 31.53 MB
command line      : RecorderApp.exe /328966
executable        : RecorderApp.exe
exec. date/time   : 2009-11-23 08:25
version           : 6.14.5.218
madExcept version : 2.7h
exception class   : EOleException
exception message : Error converting data type varchar to real.

main thread ($4ac):
005658a2 RecorderApp.exe 
7c90eb94 ntdll.dll        RtlAnsiStringToUnicodeString
7e4292de user32.dll       SendMessageW
7e42a993 user32.dll       CallWindowProcA
7c90e470 ntdll.dll        KiUserCallbackDispatcher
7e4196c2 user32.dll       DispatchMessageA

modules:
00340000 C4DLL.DLL         1.0.0.1          \\wwtsql01\recorder6
003b0000 MS5User.Dll       5.0.0.4          \\wwtsql01\recorder6
00400000 RecorderApp.exe   6.14.5.218       \\wwtsql01\recorder6
013b0000 ms5cnv.dll        5.0.0.10         \\wwtsql01\recorder6
01b50000 odbcint.dll       3.525.1132.0     C:\WINDOWS\system32
02a00000 xpsp2res.dll      5.1.2600.5512    C:\WINDOWS\system32
02ce0000 SFSShell.dll      1.21.0.143       C:\Program Files\HPQ\IAM\Bin
02d30000 ItMsg.dll         1.18.0.294       C:\Program Files\HPQ\IAM\bin
036f0000 SQLOLEDB.RLL      2000.85.1132.0   C:\Program Files\Common Files\System\Ole DB
03700000 msadcer.dll       2.81.1132.0      C:\Program Files\Common Files\System\msadc
03a00000 GoToKey.ocx       6.0.1.10         \\Wwtsql01\recorder6\Addins
03c40000 LastKeyUpdate.ocx 6.0.0.6          \\Wwtsql01\recorder6\Addins
03e60000 NBNExchange.ocx   6.13.2.24        \\Wwtsql01\recorder6\Addins
040b0000 Phenology.ocx     6.0.1.10         \\wwtsql01\recorder6\Addins
043d0000 ShowUnchecked.ocx 6.0.0.13         \\Wwtsql01\recorder6\Addins
04600000 StdValLib.dll     6.14.3.208       \\wwtsql01\recorder6\Addins
048c0000 Normaliz.dll      6.0.5441.0       C:\WINDOWS\system32
05330000 PDFShell.dll      7.0.0.0          C:\Program Files\Adobe\Acrobat 7.0\ActiveX
10000000 MS5.Dll           5.0.0.12         \\wwtsql01\recorder6
20000000 xpsp3res.dll      5.1.2600.5512    C:\WINDOWS\system32
3d930000 WININET.dll       8.0.6001.18876   C:\WINDOWS\system32
3dfd0000 iertutil.dll      8.0.6001.18876   C:\WINDOWS\system32
4dd00000 sqloledb.dll      2000.85.1132.0   C:\Program Files\Common Files\System\Ole DB
4de10000 msado15.dll       2.81.1132.0      C:\Program Files\Common Files\System\ado
4ec50000 gdiplus.dll       5.2.6001.22319   C:\WINDOWS\WinSxS\x86_Microsoft.Windows.GdiPlus_6595b64144ccf1df_1.0.6001.22319_x-ww_f0b4c2df
5ad70000 uxtheme.dll       6.0.2900.5512    C:\WINDOWS\system32
5b860000 NETAPI32.dll      5.1.2600.5694    C:\WINDOWS\system32
5d090000 comctl32.dll      5.82.2900.5512   C:\WINDOWS\system32
5edd0000 olepro32.dll      5.1.2600.5512    C:\WINDOWS\system32
60870000 dnaapphook.dll                     C:\Program Files\NetSupport\NetSupport DNA\Client\Components
60e30000 MSDATL3.dll       2.81.1132.0      C:\Program Files\Common Files\System\Ole DB
662b0000 hnetcfg.dll       5.1.2600.5512    C:\WINDOWS\system32
69b10000 msxml4.dll        4.20.9876.0      c:\WINDOWS\system32
6d4f0000 DBNETLIB.DLL      2000.85.1132.0   C:\WINDOWS\system32
6d6e0000 scrchpg.dll       6.0.3.857        C:\Program Files\Kaspersky Lab\Kaspersky Anti-Virus 6.0 for Windows Workstations
71a50000 mswsock.dll       5.1.2600.5625    C:\WINDOWS\system32
71a90000 wshtcpip.dll      5.1.2600.5512    C:\WINDOWS\System32
71aa0000 WS2HELP.dll       5.1.2600.5512    C:\WINDOWS\system32
71ab0000 WS2_32.dll        5.1.2600.5512    C:\WINDOWS\system32
71ad0000 wsock32.dll       5.1.2600.5512    C:\WINDOWS\system32
71b20000 mpr.dll           5.1.2600.5512    C:\WINDOWS\system32
71bf0000 SAMLIB.dll        5.1.2600.5512    C:\WINDOWS\System32
71c10000 ntlanman.dll      5.1.2600.5512    C:\WINDOWS\System32
71c80000 NETRAP.dll        5.1.2600.5512    C:\WINDOWS\System32
71c90000 NETUI1.dll        5.1.2600.5512    C:\WINDOWS\System32
71cd0000 NETUI0.dll        5.1.2600.5512    C:\WINDOWS\System32
71f80000 security.dll      5.1.2600.5512    C:\WINDOWS\system32
73000000 winspool.drv      5.1.2600.5512    C:\WINDOWS\system32
73160000 oledb32.dll       2.81.1132.0      C:\Program Files\Common Files\System\Ole DB
732e0000 RICHED32.DLL      5.1.2600.0       C:\WINDOWS\system32
74060000 msadce.dll        2.81.3002.0      C:\Program Files\Common Files\System\msadc
74320000 odbc32.dll        3.525.1132.0     C:\WINDOWS\system32
74720000 MSCTF.dll         5.1.2600.5512    C:\WINDOWS\system32
74e30000 RICHED20.dll      5.30.23.1230     C:\WINDOWS\system32
750b0000 RESUTILS.DLL      5.1.2600.5512    C:\WINDOWS\system32
750f0000 MTXCLU.DLL        2001.12.4414.706 C:\WINDOWS\system32
75130000 colbact.DLL       2001.12.4414.700 C:\WINDOWS\system32
75350000 OLEDB32R.DLL      2.81.1132.0      C:\Program Files\Common Files\System\Ole DB
754d0000 CRYPTUI.dll       5.131.2600.5512  C:\WINDOWS\system32
755c0000 msctfime.ime      5.1.2600.5768    C:\WINDOWS\system32
75f60000 drprov.dll        5.1.2600.5512    C:\WINDOWS\System32
75f70000 davclnt.dll       5.1.2600.5512    C:\WINDOWS\System32
75f80000 browseui.dll      6.0.2900.5512    C:\WINDOWS\system32
76390000 IMM32.DLL         5.1.2600.5512    C:\WINDOWS\system32
763b0000 comdlg32.dll      6.0.2900.5512    C:\WINDOWS\system32
765b0000 MSDART.DLL        2.81.1132.0      C:\WINDOWS\system32
76600000 CSCDLL.dll        5.1.2600.5512    C:\WINDOWS\System32
76620000 comsvcs.dll       2001.12.4414.702 C:\WINDOWS\system32
76780000 SHFolder.dll      6.0.2900.5512    C:\WINDOWS\system32
76790000 cryptdll.dll      5.1.2600.5512    C:\WINDOWS\system32
767a0000 ntdsapi.dll       5.1.2600.5512    C:\WINDOWS\system32
76980000 LINKINFO.dll      5.1.2600.5512    C:\WINDOWS\system32
76990000 ntshrui.dll       5.1.2600.5512    C:\WINDOWS\system32
769c0000 USERENV.dll       5.1.2600.5512    C:\WINDOWS\system32
76b20000 ATL.DLL           3.5.2284.2       C:\WINDOWS\system32
76b40000 winmm.dll         5.1.2600.5512    C:\WINDOWS\system32
76c30000 WINTRUST.dll      5.131.2600.5512  C:\WINDOWS\system32
76c90000 IMAGEHLP.dll      5.1.2600.5512    C:\WINDOWS\system32
76d10000 CLUSAPI.DLL       5.1.2600.5512    C:\WINDOWS\system32
76d60000 iphlpapi.dll      5.1.2600.5512    C:\WINDOWS\system32
76f20000 DNSAPI.dll        5.1.2600.5625    C:\WINDOWS\system32
76f60000 WLDAP32.dll       5.1.2600.5512    C:\WINDOWS\system32
76fb0000 winrnr.dll        5.1.2600.5512    C:\WINDOWS\System32
76fc0000 rasadhlp.dll      5.1.2600.5512    C:\WINDOWS\system32
76fd0000 CLBCATQ.DLL       2001.12.4414.700 C:\WINDOWS\system32
77050000 COMRes.dll        2001.12.4414.700 C:\WINDOWS\system32
77120000 oleaut32.dll      5.1.2600.5512    C:\WINDOWS\system32
773d0000 comctl32.dll      6.0.2900.5512    C:\WINDOWS\WinSxS\x86_Microsoft.Windows.Common-Controls_6595b64144ccf1df_6.0.2600.5512_x-ww_35d4ce83
774e0000 ole32.dll         5.1.2600.5512    C:\WINDOWS\system32
77920000 SETUPAPI.dll      5.1.2600.5512    C:\WINDOWS\system32
77a20000 cscui.dll         5.1.2600.5512    C:\WINDOWS\System32
77a80000 crypt32.dll       5.131.2600.5512  C:\WINDOWS\system32
77b20000 MSASN1.dll        5.1.2600.5875    C:\WINDOWS\system32
77b40000 appHelp.dll       5.1.2600.5512    C:\WINDOWS\system32
77c00000 version.dll       5.1.2600.5512    C:\WINDOWS\system32
77c10000 msvcrt.dll        7.0.2600.5512    C:\WINDOWS\system32
77c70000 msv1_0.dll        5.1.2600.5876    C:\WINDOWS\system32
77dd0000 advapi32.dll      5.1.2600.5755    C:\WINDOWS\system32
77e70000 RPCRT4.dll        5.1.2600.5795    C:\WINDOWS\system32
77f10000 GDI32.dll         5.1.2600.5698    C:\WINDOWS\system32
77f60000 SHLWAPI.dll       6.0.2900.5912    C:\WINDOWS\system32
77fe0000 Secur32.dll       5.1.2600.5834    C:\WINDOWS\system32
78130000 urlmon.dll        8.0.6001.18876   C:\WINDOWS\system32
7c000000 MSVCR70.dll       7.0.9466.0       C:\WINDOWS\system32
7c800000 kernel32.dll      5.1.2600.5781    C:\WINDOWS\system32
7c900000 ntdll.dll         5.1.2600.5755    C:\WINDOWS\system32
7c9c0000 shell32.dll       6.0.2900.5622    C:\WINDOWS\system32
7e290000 shdocvw.dll       6.0.2900.5803    C:\WINDOWS\system32
7e410000 user32.dll        5.1.2600.5512    C:\WINDOWS\system32
7e4b0000 HHCtrl.ocx        5.2.3790.4110    C:\WINDOWS\system32
7e720000 SXS.DLL           5.1.2600.5512    C:\WINDOWS\system32

Purgle Linham
Wiltshire & Swindon Biological Records Centre

Purgle Linham's Website

2 Reply by johnvanbreda

  • johnvanbreda
  • Administrator
  • Offline
  • From: UK
  • Registered: 27-04-2006
  • Posts: 1,237

Re: Data import problem

Hi Purgle,
What columns are you importing into? It sounds like you are importing text data into a column that expects a number, and it can't be converted. Of course this should be handled more elegantly though!
Best Wishes

John van Breda
Biodiverse IT

johnvanbreda's Website

3 Reply by RobLarge

  • RobLarge
  • RobLarge
  • Veteran
  • Offline
  • From: Wiltshire
  • Registered: 15-07-2008
  • Posts: 812

Re: Data import problem

John,

Purgle and I went through the data and checked all sorts of things, but finally tracked down the data which was causing this error. Following you suggestion we looked at all the columns we were importing, but none of them were numeric. The actual error did turn out to be in a column of abundance data however.

One record had in the abundance field the value +8Pair. This was erroneous as it should have read 8+ Pair (with a space in between 8+ and Pair) which would have imported Pair as the measurement qualifier and 8+ as the data value. As I said, neihter of these are actually numeric, but the error clearly occurred somewhere in the code which interprets the data.

Hope this helps track it down.

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

RobLarge's Website

4 Reply by burkmarr

  • burkmarr
  • burkmarr
  • FSC BioLinks Digital Development Officer
  • Offline
  • From: Preston Montford
  • Registered: 14-05-2006
  • Posts: 105

Re: Data import problem

For the record, I had the same problem and same solution. The root of the error in a spreadhsheet of several thousand records  was one record where abundace data started with the hyphen symbol (-). A good way of isolating such problems in an excel spreadsheet with lots of records is to put a filter on the column and then look at the unique values in the drop-down for any that are suspect.

Richard Burkmar
Biodiversity Project Officer
Field Studies Council

burkmarr's Website

5 Reply by Mary Campling

  • Mary Campling
  • Administrator
  • Offline
  • Registered: 16-04-2010
  • Posts: 101

Re: Data import problem

Thanks Richard, it is on the feature wish-list.

6 Reply by TonyP

  • TonyP
  • TonyP
  • Veteran
  • Offline
  • From: Somerset
  • Registered: 15-05-2006
  • Posts: 306

Re: Data import problem

burkmarr wrote:

For the record, I had the same problem and same solution. The root of the error in a spreadhsheet of several thousand records  was one record where abundace data started with the hyphen symbol (-). A good way of isolating such problems in an excel spreadsheet with lots of records is to put a filter on the column and then look at the unique values in the drop-down for any that are suspect.

I agree Richard but there is a caveat at least with Excel 2003 and before. The number of unique items has a limit and if exceeded there is no warning it just ignores the extra values and you have to identify manually the offending term and use the custom option. This is a very annoying bug in the filters as one assumes Microsoft had some idea of what they were doing. This alone has cost me days of work.

Data Manger
Somerset Environmental Records Centre

TonyP's Website