1 Reply by charlesroper

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

Re: Importing data from R2K2

I've got a set of data that have been exported from R2K2 that I'm having difficulty importing into R6. Although it analyses just fine, with only one invalid item, virtually the whole lot is rejecting. After a bit of analysis of the ImportRejects.txt file (which didn't give me enough info) and some wrangling in Access (which gave me much more info), I managed to come across this error:

ODBC--call failed

[Microsoft][SQL Native Client]Fractionaltruncation (#0) [Microsoft][SQL Native Client]Datetime field overflow(#0)

This is the error I get if I try to paste one of the rejecting rows into the live database. It would seem that the live database doesn't like the ENTRY_DATE value of 00:00:00. If I remove these values, it pastes in fine. If I manually specify a full date and time, it goes in fine. It's only with the value specified above do I get the error. Now, on analysis of the import file I have, there are quite a few ENTRY_DATEs with a value of 00:00:00, which is rendering my import impossible.

What is going on here?

Cheers,

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

2 Reply by johnvanbreda

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

Re: Importing data from R2K2

Are you able to identify where the records that have a zero entry_date came from?  Records entered using Recorder should never have this value for a date (unless there was a bug).

John van Breda
Biodiverse IT

johnvanbreda's Website

3 Reply by charlesroper

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

Re: Importing data from R2K2

Thanks John, I will investigate further. They all came from within one organisation and I know Mike W. is their reseller and he's been doing quite a bit of work for them, so that gives me a few leads to follow up. :)

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

4 Reply by MikeWeideli

  • MikeWeideli
  • Administrator
  • Offline
  • From: Littlefield Consultancy
  • Registered: 05-07-2006
  • Posts: 2,625

Re: Importing data from R2K2

I have noticed this in lots of systems, but have never been able to pin down where they are coming from.   I haven't done any import work for the HighWeald Unit and as far as I know their data has either been  entered via the system, or via the import wizard, so this may narrow it down a bit.  I will have a look at their database to see if I can spot anything.

Mike Weideli

MikeWeideli's Website

5 Reply by charlesroper

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

Re: Importing data from R2K2

I made a mistake, it's CHANGED_DATE with a value of 00:00:00 that seems to be causing the problem. CHANGED_DATEs with a null value go in fine. I've uploaded an mdb with a row of data that won't go in for you to have a look at. I've tried a standard Recorder import and it rejects; if I try and manually paste the row in via Access, I get the same error I mentioned above. If I remove the 0 value CHANGED_DATE, I can paste it in just fine, so this is what I assume is causing the rejection.

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

6 Reply by johnvanbreda

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

Re: Importing data from R2K2

You're right Charles, Changed_Date entries should be set to null not zero otherwise it breaks the import in the fashion you have described.  I'd still be interested in finding out where the records came from.

John van Breda
Biodiverse IT

johnvanbreda's Website

7 Reply by MikeWeideli

  • MikeWeideli
  • Administrator
  • Offline
  • From: Littlefield Consultancy
  • Registered: 05-07-2006
  • Posts: 2,625

Re: Importing data from R2K2

I have investgated the entries in the Location table and they are  not in continuous runs.  They are ususally in the middle of a  run of changes,  which suggests that they do not arise from any form of transfer, but actually at some point during input or the  correction of input. Possibly when a change is abandoned, but more investigation is required.

Mike Weideli

MikeWeideli's Website

8 Reply by MikeWeideli

  • MikeWeideli
  • Administrator
  • Offline
  • From: Littlefield Consultancy
  • Registered: 05-07-2006
  • Posts: 2,625

Re: Importing data from R2K2

I have done some further checking and I was wrong about the data not being in blocks. The situation was confused by the fact that the HighWeald Unit have made a lot of changes to the data which corrects the 00:00:00 in the Changed_By field. I can now see the data has been entered in blocks over many  different entered_by dates with the records going in every few seconds within the block. The size of the blocks (around 40 records)  suggests that the data was going in from one of the data input screens. The fact that they are not all the same date would seem to rule out the bulk move. I suspect the zero date has gone into the field  at the  time the data was input.  Also they all  occur prior to December 2005,  so may be related to a previous version of Recorder 2002. I am not sure when HW upgraded  and from what version.

Mike Weideli

MikeWeideli's Website

9 Reply by johnvanbreda

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

Re: Importing data from R2K2

The only screen in Recorder that could create 40 records in a block like this is the record card screen.  In Recorder 2002, this has had no code changes during 2004 or 2005 that affect the way it creates records and the changed_date field.  Could these records have come in from some external import mechanism, or the Import Wizard addin?

John van Breda
Biodiverse IT

johnvanbreda's Website

10 Reply by charlesroper

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

Re: Importing data from R2K2

I've got some more information on the problem:

* Nearly all of the rows with a 0 value CHANGED_DATE do not have a CHANGED_BY value, i.e. they have not been changed.
* All of the rows that have been changed also have a valid CHANGED_DATE.
* None of the data were entered using the import wizard; it was all done via either recording cards or the enter a species record... method.
* Some of the data were entered on Recorder 2002 version 2.3.1.0 and some on 2.3.7.8
* Much of the data has been edited at some point
* The data have been transferred around 'quite a lot' between copies using the standard Recorder import/export routines.
* Some of data have been entered as recently as 2006 while some was entered as early as 2004

Given all of this, I still need to import the data, so the original database needs to be fixed. Mike, are you able to look into repairing their database for them?

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

11 Reply by MikeWeideli

  • MikeWeideli
  • Administrator
  • Offline
  • From: Littlefield Consultancy
  • Registered: 05-07-2006
  • Posts: 2,625

Re: Importing data from R2K2

Its easy to fix, but as I said I have seen this elsewhere, also with systems which have never had any special work done on them.  I did contact  Sally Westaway at the High Weald unit and she assures me that all the input was done using the normal recording cards. The work I did  recently did not involve changing or modifying the original database and anyway I have just checked and the zero value are there before I changed the database. This is a mystery to me.  If it something which is widespread then the conversion from Recorder 2002 to Recorder 6 shoudl handle it, otherwise there will potentially be lots of systems to change.

Mike Weideli

MikeWeideli's Website

12 Reply by Rob B

  • Rob B
  • Veteran
  • Offline
  • From: Edinburgh
  • Registered: 08-05-2006
  • Posts: 88

Re: Importing data from R2K2

Just to let you know, on closer inspection of import rejects from R2K to R6,  we have exactly the same series of mystery CHANGED_DATE values of 00:00:00

They occur in the following tables which corresponds exactly with the import reject text file:
SURVEY_EVENT
SAMPLE
TAXON_OCCURRENCE
SURVEY_EVENT_RECORDER
SAMPLE_SOURCES
TAXON_OCCURRENCE_DATA
TAXON_DETERMINATION

This wouldn’t be any form of Y2K would it?
The other thing I thought may be happening here is when a user has the century cut-off date set to something other than 40?