1 Reply by DaveSlade (edited by DaveSlade 16-10-2008 10:20:04)

  • DaveSlade
  • Veteran
  • Offline
  • Registered: 15-05-2006
  • Posts: 332

Re: Problem with an Import from Excel.

I've just imported 18,000 records from an Excel file and it appeared to work without any problems, but right at the end after everything was completed it rejected 6000 items.

The Import Rejects file contains a whole stack of entries like this:
--------------------

Failed to insert LC00021500002DJ4 into SURVEY_EVENT_RECORDER
Database Error Message:
Row cannot be located for updating. Some values may have been changed since it was last read.
--------------------

Failed to insert LC00021500009IHZ into TAXON_DETERMINATION
Database Error Message:
Row cannot be located for updating. Some values may have been changed since it was last read.

On looking at the data, it does look as though most of the records are there, but occasionally when trying to look at a Sample I get an unhandled error:

Exception occurred in application Recorder 6 at 16/10/2008 11:07:46.
Version : 6.10.4.120

Exception path:
EAccessViolation : Access violation at address 0073443B in module 'RecorderApp.exe'. Read of address 04E88000

Last event\actions:
  TfrmTaxonOccurrences created
  TfrmTaxonOccurrences destroyed
  TfrmSampleDetails created
  TfrmSampleDetails destroyed
  TfrmTaxonOccurrences created
  TfrmTaxonOccurrences destroyed
  TfrmEventDetails created
  TfrmEventDetails destroyed
  TfrmSampleDetails created

Operating System : Windows XP  5.01.2600  Service Pack 3
Physical Memory available : 1,046,508 KB

DLLs loaded:
  advapi32.dll (5.1.2600.5512)
  comctl32.dll (5.82.2900.5512)
  comdlg32.dll (6.0.2900.5512)
  gdi32.dll (5.1.2600.5512)
  HHCtrl.ocx (5.2.3790.4110)
  kernel32.dll (5.1.2600.5512)
  mpr.dll (5.1.2600.5512)
  MS5.Dll (5.0.0.12)
  MS5User.Dll (5.0.0.4)
  odbc32.dll (3.525.1132.0)
  ole32.dll (5.1.2600.5512)
  oleaut32.dll (5.1.2600.5512)
  shell32.dll (6.0.2900.5512)
  user32.dll (5.1.2600.5512)
  version.dll (5.1.2600.5512)
  winmm.dll (5.1.2600.5512)
  winspool.drv (5.1.2600.5512)

Is this as a result of the rejected items? If so should I delete the survey and re-import it from scratch? Any thought gratefully received.

Dave Slade
Senior IT & Records Officer,
15 Talbot Road, Talbot Green, Pontyclun, CF72 8AD
www.sewbrec.org.uk, www.sewbrecord.org.uk

2 Reply by Lynn Heeley

  • Lynn Heeley
  • Administrator
  • Offline
  • Registered: 03-07-2008
  • Posts: 345

Re: Problem with an Import from Excel.

Hi David,

I had a similiar issue just recently while testing for another issue - the import looked fine and then most things were rejected at the actual import stage.

There is currently a bug which means that some tables are not exporting properly which may result in the large amounts of rejected items on import of the file (the 'failure to insert' statements suggest that it can't find some of the data it expects which is likely to be in the tables that have not exported). The failure to find the data it needs for certain records may well be what it is triggering the unhandled errors.

The tables known to be not exporting properly are for zipped nbn format:
Organisation_Department, Survey_Event_Owner_Type,  Keyword and Concept and Location_Type

and For XML Exports  the  missing tables are

Location_Boundary, Grid_Square, Management_Aim, Reference_Keyword, Concept, Measurement_Type_Context, Survey_Event_Owner_Type, Organisation_Department, Keyword (and possible LocationType).

This has been reported to Dorset and will be fixed I hope in the next release.
I hope this explains a few things as for how to get round it in the meantime I'm sorry I don't know, perhaps Steve Wilkinson or Charles Roper may be able to help with that part?

I noticed that you haven't upgraded to the latest version.

Best Wishes,
Lynn  Heeley
JNCC

3 Reply by wilkin_s

  • wilkin_s
  • Administrator
  • Offline
  • Registered: 31-03-2006
  • Posts: 87

Re: Problem with an Import from Excel.

Dave,
That looks like something we might want to follow up but as Lynn points out you would really need to be on the latest version (there were some changes made to the import wizard in 6.13). If you can upgrade then I would do that and try reimporting again from scratch. If you are still getting problems then please send us through a copy of the file and we will try it here.
Best wishes
Steve

4 Reply by DaveSlade

  • DaveSlade
  • Veteran
  • Offline
  • Registered: 15-05-2006
  • Posts: 332

Re: Problem with an Import from Excel.

Ok, will do.
Thanks,
Dave

Dave Slade
Senior IT & Records Officer,
15 Talbot Road, Talbot Green, Pontyclun, CF72 8AD
www.sewbrec.org.uk, www.sewbrecord.org.uk

5 Reply by DaveSlade

  • DaveSlade
  • Veteran
  • Offline
  • Registered: 15-05-2006
  • Posts: 332

Re: Problem with an Import from Excel.

Following the upgrade the import appears to have work without any problems, and I like the new features. Nevertheless there are a couple of issues that have come up as a result.

After the import I noticed that several records had been given a date of 'Unknown' - which struck me as odd since all the records should have at least been from 2006, and the import wizard did not show up any problems with the dates. It turns out that the records in question were not just dates, but date and time (formatted to only show the date part). I think it would be helpful if Recorder would either accept the date/time values or throw them out as invalid rather than re-assign the date to 'Unknown'.

Secondly, I re-imported this rather large dataset on the basis that the 'Delete survey' add-in would remove the redundant records, however the add-in does not appear to work. the version I have is 6.0.0.23, I get the following error message:
Exception occurred in application Recorder 6 at 17/10/2008 15:47:04.
Version : 6.13.2.176

Exception path:
EOleException : Invalid filename

Last event\actions:
  actObservations invoked
  TfrmObservations created
  TfrmObservations activated
  TfrmSurveyDetails created
  TfrmSurveyDetails destroyed
  TfrmSurveyDetails created
   invoked
  TdlgContainer created
  TdlgContainer activated

Operating System : Windows XP  5.01.2600  Service Pack 3
Physical Memory available : 1,046,508 KB

DLLs loaded:
  advapi32.dll (5.1.2600.5512)
  comctl32.dll (5.82.2900.5512)
  comdlg32.dll (6.0.2900.5512)
  gdi32.dll (5.1.2600.5512)
  gdiplus.dll (5.1.3102.5581)
  HHCtrl.ocx (5.2.3790.4110)
  kernel32.dll (5.1.2600.5512)
  mpr.dll (5.1.2600.5512)
  MS5.Dll (5.0.0.12)
  MS5User.Dll (5.0.0.4)
  odbc32.dll (3.525.1132.0)
  ole32.dll (5.1.2600.5512)
  oleaut32.dll (5.1.2600.5512)
  shell32.dll (6.0.2900.5512)
  user32.dll (5.1.2600.5512)
  version.dll (5.1.2600.5512)
  winmm.dll (5.1.2600.5512)
  winspool.drv (5.1.2600.5512)

Information has been saved to the file R:\User Files\LastError.txt
----
And also for information, the 'LastError.txt' file was not created.

Dave

Dave Slade
Senior IT & Records Officer,
15 Talbot Road, Talbot Green, Pontyclun, CF72 8AD
www.sewbrec.org.uk, www.sewbrecord.org.uk

6 Reply by Lynn Heeley

  • Lynn Heeley
  • Administrator
  • Offline
  • Registered: 03-07-2008
  • Posts: 345

Re: Problem with an Import from Excel.

Hi David,

Yes, there is an issue with date and time information in the same column. Currently Recorder just pretends everything is fine and then fills in the 'unknown' that you found. Really I think we need Recorder to let the user know that there is a problem at the import stage rather than ignoring it. I will get this fixed.

Steve is looking into your delete survey addin point.

Thanks,
Lynn

7 Reply by Lynn Heeley

  • Lynn Heeley
  • Administrator
  • Offline
  • Registered: 03-07-2008
  • Posts: 345

Re: Problem with an Import from Excel.

Can I check that you are using the delete survey addin correctly? It is an addin that enables you to delete a whole survey and all its contents (ie before you import a spreadsheet with the same survey name with old and new records in - to avoid duplicates) - you would therefore delete the survey before importing the new spreadsheet or import the new spreadsheet into a different survey and then delete the old one using the addin.
See Recorder addin webpage (http://forums.nbn.org.uk/recorderWebsite/Addins.htm) I have checked the addin here and it appears to be working as I would expect it to.

How are you using the delete survey addin and when exactly does the invalid filename error occur? Does it occur when you hit import (the spreadsheet) or can you get to view the data in the observation hierarchy? Please can you give us a bit more detail.

Thanks,
Lynn

8 Reply by DaveSlade

  • DaveSlade
  • Veteran
  • Offline
  • Registered: 15-05-2006
  • Posts: 332

Re: Problem with an Import from Excel.

This is what I have been doing:
Go to Tools -> Delete Survey
Selecting the Survey that I wish to Delete (and yes do I mean the whole survey)
Make sure the checkbox 'Delete the Survey Entry' is checked
Then hit ok.
It is at this point that the invalid filename exception occurs, and the LastError file is not updated.

When I first tried to import the 2007 bird data I was adding to a survey that was created for the 2006 data. Since I did not know which records had failed to import properly, I created two new surveys (one for each year) and re-imported it. I now want to delete the one with the failed 2007 import.

I do think that the add in not working is a symptom of another problem, linked to the inability of the LastError file to be updated, but there have been no changes to the server or workstations as far as I can see that would have changed the permissions. It is a Windows XP update (which I've not ruled out), then it's a real coincidence that it took place immediately after the Recorder upgrade!

Dave Slade
Senior IT & Records Officer,
15 Talbot Road, Talbot Green, Pontyclun, CF72 8AD
www.sewbrec.org.uk, www.sewbrecord.org.uk

9 Reply by charlesroper

  • charlesroper
  • charlesroper
  • Digital Development Manager
  • Offline
  • From: Field Studies Council
  • Registered: 26-03-2006
  • Posts: 906

Re: Problem with an Import from Excel.

I've just tried the Delete Survey add-in myself and I am getting exactly the same "Invalid filename" error. I've tried running in both admin and limited user modes.

I've also just checked my LastError.txt file and although there is one there, it has not been updated since 26/6/2008, which is probably about the time we upgraded to 6.13. The thing about this is that the directory where it is being created has Full Control permissions for the whole team (it's not in Program Files), so this would appear to be something other than a permissions problem OR LastError.txt is being written somewhere else and we're just looking in the wrong place for it.

Charles

Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

charlesroper's Website

10 Reply by DaveSlade

  • DaveSlade
  • Veteran
  • Offline
  • Registered: 15-05-2006
  • Posts: 332

Re: Problem with an Import from Excel.

Thanks Charles - I thought I was beginning to doubt my sanity!

Dave Slade
Senior IT & Records Officer,
15 Talbot Road, Talbot Green, Pontyclun, CF72 8AD
www.sewbrec.org.uk, www.sewbrecord.org.uk

11 Reply by Lynn Heeley

  • Lynn Heeley
  • Administrator
  • Offline
  • Registered: 03-07-2008
  • Posts: 345

Re: Problem with an Import from Excel.

Steve has just posted a temporary fix for this (but against the other set of forum posts I think - 'upgrade problem') - create a folder on the C drive called TEMP and run it again - this should fix the deletesurvey issue for the time being.

Lynn