1 Reply by James Dargie

  • James Dargie
  • Member
  • Offline
  • Registered: 10-02-2010
  • Posts: 25

Re: Spreadsheet import

I have a contractor entering data. They seem to be using Version 4.16 with Windows Vista and have successfully imported 3 spreadsheets to a survey, but have a further 5 spreadsheets to import to the same survey. However, my contractor reports that they:

"have managed to import 3 of the spreadsheets and all of the species names are correct but when i try and import the remaining spreadsheets the same error message is coming up for all of them - that the sample reference already exsists - values must be unique. When i change the sample reference number by adding a zero or underscore or letter or changing the number entirely to make it unique, the spreadsheet is accepted initially but the import is not completed due to an error box that pops up saying tat the application cannot continue due to a runtime error and shuts down.

This is happening everytime, and when i try importing again the error again pops up saying the sample reference already exsists and i have to start again by changing the sample ref number trying to upload it and it failing due to a runtime error!"

My contractor insists they have checked and rechecked sample reference values for uniqueness. I understand that there are a total of 157 samples between the 8 spreadsheets.

Does anybody know if there is a maximum number of samples that can be attached to a survey or an individual survey event? Or has anybody had similar problems?

Meantime I've advised the contractor to upgrade to version 4.42 to see if that makes a difference, but I'm not aware that the version changes 4.17 & 4.40 to 4.42 were designed to solve spreadsheet import problems; so I'm not convinced that this will solve the problem.

2 Reply by James Dargie

  • James Dargie
  • Member
  • Offline
  • Registered: 10-02-2010
  • Posts: 25

Re: Spreadsheet import

Contractor has now upgraded to version 4.42 and still experiences the same problems importing the spreadsheets. I am in the course of obtaining copies of the data to try and work out what is going on.

3 Reply by James Dargie

  • James Dargie
  • Member
  • Offline
  • Registered: 10-02-2010
  • Posts: 25

Re: Spreadsheet import

I have merged the contractors survey into our master dataset here in SNH and have replicated the spreadsheet import error:

- The spreadsheet validates correctly
- On proceeding to import the query status at the bottom left gets part way.
- but then, very quickly a runtime error and bailout

I have exported the data that is there to a snapshot and have run some queries, I am able to confirm that the EventIDs specified in the spreadsheet exist in the database and have no samples attached. I cannot see any duplicate user sample references in the relevant spreadsheets for import to events which in any case have no existing samples. I see no logical explanation as to why the spreadsheets don't import.

I will now proceed to let the contratcor know that I see no alternative to entry through the interface - I don't suppose this will go down very well, but I see no alternative.....

4 Reply by charliebarnes

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

Re: Spreadsheet import

Have you tried the "divide and conquer" approach - splitting the spreadsheet in half and importing until you find the/a/if there is an offending record?

Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

charliebarnes's Website

5 Reply by James Dargie

  • James Dargie
  • Member
  • Offline
  • Registered: 10-02-2010
  • Posts: 25

Re: Spreadsheet import

charliebarnes wrote:

Have you tried the "divide and conquer" approach - splitting the spreadsheet in half and importing until you find the/a/if there is an offending record?

Not yet!

The samples are split between 8 spreadsheets, at least 3 of which appear to be problematic. I've checked on the first problematic spreadsheet very carefully indeed, none of the problematic samples exist in the survey, let alone the survey event; interestingly I don't get the first error about "that the sample reference already exsists - values must be unique" that the contractor reported which tallies with the checks I've run. I've calculated the total character count per row in that same spreadsheet and none exceeds the permitted number defined in the manual.

The spreadsheets validate without problem. The import commences and very quickly crashes out with the runtime error.

My next plan is to zap all the sample data  from the survey and start pulling in the data from scratch; if that fails I will try the divide and conquer approach - thanks for the suggestion.

I'm also in a fairly awkward situation as it is our contractors responsibility to get this sorted, but they are stuck, so muggins here has to juggle sorting the contractor out with an already over subscribed workprogram. I think this may well become one I fix in 'my own time'....

6 Reply by James Dargie

  • James Dargie
  • Member
  • Offline
  • Registered: 10-02-2010
  • Posts: 25

Re: Spreadsheet import

It seems likely that this is caused by individual values in the  'Habitat name' row of the import spreadsheet exceeding 100 characters. The manual currently provides the current advice:

"It is advisable to keep the number of samples (Excel columns) to a smallish number (maybe 50 or less), if more samples are needed simply use multiple spreadsheets.  There is a limit or 2048 characters in any one spreadsheet row when it is imported into Microsoft Access, so it is also advisable to be concise with the habitat name."

So currently I believe that the Spreadsheet validataion checks that the total row character count does not exceed 2048 characters. However, the individual cell values flow into an NBNData.mdb field with a width of 100 characters. At present there is no check on the width of the individual cell values. If they are exceeded the import will seemingly fail and generate a runtime error.

JNCC and exeGesIS have looked at this and I think are about to release a patch to provide additional validation for spreadsheets prior to import.