1 Reply by ser

  • ser
  • ser
  • Administrator
  • Offline
  • From: Staffordshire Ecological Recor
  • Registered: 24-09-2008
  • Posts: 603

Re: Error importing into Rec 6

I'm trying to import a file from MapMate I am getting the following error message at the point after allocation field names to the columns:

Exception occurred in application Recorder 6 at 22/01/2009 10:47:32.
Version : 6.13.2.176

Exception path:
EOleException : String or binary data would be truncated

I've tried from both text faile and Excel with the same result - I've studied the dataset almost one line at a time and I can't see any silly lines (i.e. records stuck together, etc.) So any ideas what is causing the error would be appreciated

Craig

Craig Slawson
Staffordshire Ecological Record

ser's Website

2 Reply by ser

  • ser
  • ser
  • Administrator
  • Offline
  • From: Staffordshire Ecological Recor
  • Registered: 24-09-2008
  • Posts: 603

Re: Error importing into Rec 6

Again, I have eventually found the problem, but think this needs sorting in Recorder

The data for the "Abundance_Data" field could be up to 30 characters - I shortened this by adding abbreviations and the Import continued without error - I don't know what the maximum this field is expecting, but crashing at 30 characters seems a little low!

Craig

Craig Slawson
Staffordshire Ecological Record

ser's Website

3 Reply by sallyrankin

  • sallyrankin
  • Administrator
  • Offline
  • Registered: 07-04-2006
  • Posts: 371

Re: Error importing into Rec 6

When importing a large file via the import wizard brackets in some of the abundance data meant that it was not imported in the normal way. The import file contained ‘Present’ in the Quantity column and ‘Mine (vacated)’ in the Stage column in some rows. I concatenated these columns into an Abundance Data column. Each time the import was run it failed with the exception message : String or binary data would be truncated. As described in the Help (Contents tab – Tasks – Exchanging data – Import data – Import Wizard – Abundance data) you can usually determine what goes into Measurement Qualifier and Data by the position of the space but when brackets are present the whole string goes into Measurement Data. As ‘Present Mine (vacated)’ is 22 characters it was too long to go into the 20 characters allowed in that field (DATA in TAXON_OCCURRENCE_DATA), hence the error message. When this string was changed to ‘Present Minevacated’ this could be imported as Measurement Qualifier ‘Mine (vacated)’ and Measurement Data ‘Present’ provided Minevacated is matched to ‘Mine (vacated)’ when matching measurement qualifiers. Also, ‘Present 2ndSummer’ imported as Measurement Qualifier ‘2nd Summer’ and Measurement Data ‘Present 2’ as ndSummer was matched to ‘2nd Summer’ – another inconsistency.

Why are brackets causing this problem? Can the import wizard be modified to handle abundance data containing brackets in the same way it handles this data when it doesn’t contain them? Can it also be modified to flag abundance data as an error when either the qualifier or the data are too long (as it does with other fields) instead of crashing. Sorting out work-arounds for bugs like this is very time consuming.

Sally Rankin, JNCC Recorder Approved Expert
E-mail: s.rankin@btinternet.com
Telephone: 01491 578633
Mobile: 07941 207687

4 Reply by Lynn Heeley

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

Re: Error importing into Rec 6

An explanation of why fields could be truncated in Recorder from Dorset (which may of use):
The 3rd party component we use to import and export data (SMImport) only looks at the first hundred records or so when determining field lengths for the temporary table it creates. It analyses the first hundred records to see the maximum comment size, it then uses this size as the field length when it imports/exports data.  I.e. if in the top hundred records the maximum comment size is 200 characters, it'll use this as the field size when initially importing the data. If there are longer comments further down the file, e.g. row 300 may contain a comment of 400 characters, then this will be truncated.

It has been requested that the taxon occurrence data field being lengthened to 40 characters (as opposed to 20) - this has been logged on the list of requests. Also I have logged the issue with brackets in the abundance field in the import wizard.

Thanks,
Lynn

5 Reply by Matt_tullie

  • Matt_tullie
  • Veteran
  • Offline
  • From: Carlisle, Cumbria
  • Registered: 18-05-2007
  • Posts: 166

Re: Error importing into Rec 6

Im having difficulty with this one currently.

So which fields does this truncation risk relate to? Is it a finite list - or all of them that go into a temp table?

Taxon Occ comments, Specimen comments, Abundance???

Trying to sort records on the basis of one field length is feasible - but ordering them to maximise all the fields...!

Thanks in advance

MAtt

Cumbria Biodiversity Data Centre
Tullie House Museum

Matt_tullie's Website

6 Reply by Lynn Heeley

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

Re: Error importing into Rec 6

Hi Matt,

Sorry for the late reponse.
Presumably this relates to all fields in the import wizard. You could identify the largest record in each field and put these rows near the top of the spreadsheet to be sure.

Cheers,
Lynn

7 Reply by charliebarnes

  • charliebarnes
  • Veteran
  • Offline
  • From: Greater Lincolnshire
  • Registered: 07-09-2008
  • Posts: 1,396

Re: Error importing into Rec 6

Lynn Heeley wrote:

only looks at the first hundred records or so when determining field lengths for the temporary table it creates.

I think the solution to this one would be not to use SMImport to import and export data...

Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

charliebarnes's Website