1 Topic by Darwyn Sumner

  • Darwyn Sumner
  • Darwyn Sumner
  • Veteran
  • Offline
  • From: Dipterists Forum
  • Registered: 10-05-2006
  • Posts: 250

Topic: Truncated data in Report output to Excel

If I run a report wizard on a taxon and include the fields Sample Longitude and Sample Latitude in my wizard I see the full precise coordinates (e.g. -0.67568711259975) on screen but when I "Report output" this to an Excel spreadsheet all I get is -0.68
Has this been fixed in recent versions?
(It's fine in text output but a 10 step export routine is a bit laborious)

2 Reply by MikeWeideli

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

Re: Truncated data in Report output to Excel

It hasn't ben fixed. It was reported in  2015, but there is no simple answer, because third party software is used to handle the  Excel output.  I will flag it up for John van Breda to look at.

Mike Weideli

MikeWeideli's Website

3 Reply by charliebarnes

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

Re: Truncated data in Report output to Excel

One workaround is to export as TXT - this preserves  the lat/long fields.

Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

charliebarnes's Website

4 Reply by Darwyn Sumner

  • Darwyn Sumner
  • Darwyn Sumner
  • Veteran
  • Offline
  • From: Dipterists Forum
  • Registered: 10-05-2006
  • Posts: 250

Re: Truncated data in Report output to Excel

These Excel formulae seem to do the trick:

Latitude =IF(MID(F9,IFERROR(FIND(“N”,F9),FIND(“S”,F9)),1)=”N”,LEFT(F9,(IFERROR(FIND(“N”,F9),FIND(“S”,F9))-1)),LEFT(F9,(IFERROR(FIND(“N”,F9),FIND(“S”,F9))-1))*-1)

Longitude =IF(MID(F9,IFERROR(FIND("W",F9),FIND("E",F9)),1)="W",(LEFT(RIGHT(F9,(LEN(F9)-FIND(" ",F9))),LEN(RIGHT(F9,(LEN(F9)-FIND(" ",F9))))-1))*-1,(LEFT(RIGHT(F9,(LEN(F9)-FIND(" ",F9))),LEN(RIGHT(F9,(LEN(F9)-FIND(" ",F9))))-1)))

Operating on the decimal "spatial reference" included in your report wizard contained in column F (formulae are for row 9)

A bit complex as they need to give negative values for S and W coordinates https://msdn.microsoft.com/en-us/library/aa578799.aspx