1 Topic by philr

  • philr
  • Veteran
  • Offline
  • From: CPERC
  • Registered: 12-07-2012
  • Posts: 58

Topic: Problems exporting

I have had problems for a while exporting large datasets to Excel, where it will do the export and then when trying to open the file it will say 'Excel found unreadable content' and then when you try and the recover the contents it will give an error like the one below -


Removed Part: /xl/sharedStrings.xml part with XML error.  (Strings) Undeclared entity. Line 114190, column 37.
Removed Records: Cell information from /xl/worksheets/sheet1.xml part


I have been getting round this by exporting to Access instead but I just found a dataset that will not do that as well as it says something like 'Export failed - invalid argument'. I tried another large dataset though and it did successfully export to Access.

I can't export to text file as the tabs in the comments fields cause problems there as well.

We have 6.22, I did not have any problems exporting to Access when we had 6.18 but I don't know if this has anything to do with it.

Any solutions?


Thanks,

Phil

CPERC

2 Reply by TeresaF

  • TeresaF
  • Veteran
  • Offline
  • From: BTO
  • Registered: 12-05-2008
  • Posts: 172

Re: Problems exporting

I have issues with our annual whole database export we do as part of our verification process, I think because of some issues with the comments and sources.

The solution I have come up with separately two years in a row (I'd forgotten I'd done it the first time until I re-read my notes!) is to export to a text file, with ¬ as the field delimiter, and " as the text qualifier; and then to import the text file into Access with those same parameters.

For me it doesn't work if you don't use " to denote the text fields, and ¬ is always useful as a character not ever used in comments... yet!

-----------------
Teresa Frost | Wetland Bird Survey National Organiser | BTO
Other hat  | National Forum for Biological Recording Council
(Old hats  | NBN Board, ALERC Board, CBDC, KMBRC)

3 Reply by philr

  • philr
  • Veteran
  • Offline
  • From: CPERC
  • Registered: 12-07-2012
  • Posts: 58

Re: Problems exporting

Thanks Teresa. I have just done some more testing and it looks like it is the comments field that is causing the problems.

Is there a reliable and safe batch update out there for cleaning the comments fields, or at least for identifying which records are causing the problems?

Phil

4 Reply by charliebarnes

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

Re: Problems exporting

I wonder if removing rich text might help?

http://forums.nbn.org.uk/viewtopic.php?id=916

Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

charliebarnes's Website

5 Reply by MikeWeideli

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

Re: Problems exporting

I have been trying to duplicate this, but with no success. I have input all the special characters which R6 will allow, plus a selection of combinations of these and they all export to Access . This suggests that whatever is causing the problem has been imported  in some way. If we can work out what the cause is then we do a bath update to change/remove it . If it turns out to be something which can be imported, but not keyed in  then it should be possible to adjust the comment  during import.  Any information which will help in identifying exactly what the cause is would be appreciated. Could be perhaps imported html or corrupted rtf

Mike Weideli

MikeWeideli's Website

6 Reply by philr

  • philr
  • Veteran
  • Offline
  • From: CPERC
  • Registered: 12-07-2012
  • Posts: 58

Re: Problems exporting

I have now run the update to remove RTF from the taxon occurrence comments using the relevant SQL function.

However the excel problem is still occurring, with an error like the one below when the exported excel file is opened -

Removed Part: /xl/sharedStrings.xml part with XML error.  (Strings) Undeclared entity. Line 1165429, column 37.
Removed Records: Cell information from /xl/worksheets/sheet1.xml part

It is definitely caused by the taxon occurrence comment field as there is no problem with the export when this is removed.

Is there anything else I can try to find out what is causing the problem?

Thanks

7 Reply by charliebarnes

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

Re: Problems exporting

Using 7zip or similar you should be able to extract the XLSX file and open the XML files referenced - find Line 1165429. This should tell you what the problem is (probably e.g. &somethinghere;)

Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

charliebarnes's Website

8 Reply by philr

  • philr
  • Veteran
  • Offline
  • From: CPERC
  • Registered: 12-07-2012
  • Posts: 58

Re: Problems exporting

Thank you very much Charlie! I did as you said and found out that the problem was a + and - on top each other in one record (to indicate approximately) which in the xml was shown as ±

It is amazing that something so small can cause such problems. Out of interest is the issue caused by the fact that this is not a recognised character in Excel?

9 Reply by charliebarnes (edited by charliebarnes 10-10-2014 14:58:03)

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

Re: Problems exporting

I think it's a problem with the exporter - XML doesn't define entities (the new Excel format is XML based), so they have to be translated to their numeric equivalent. Presumably this happens for all entities except ± (I tried some common examples and they were all fine).

(I may be entirely wrong though - this is what I've gleaned from http://stackoverflow.com/questions/2816 … g-with-xml and http://fmepedia.safe.com/CommunityAnswe … 000cwtVAAQ)

The export works in Access, and presumably the old Excel format, as they're not XML based.

Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

charliebarnes's Website

10 Reply by philr

  • philr
  • Veteran
  • Offline
  • From: CPERC
  • Registered: 12-07-2012
  • Posts: 58

Re: Problems exporting

Yes that makes sense as I think the problems started when Recorder updated to exporting xlsx files. I haven't been able to replicate the problem I had with an Access export again though so that was probably caused by something else.