Allow copy / paste of whole location-row out of excel

[yvessavoy]

Current situation

Filling the location for a speciment is probably the most time consuming task of a speciment. To improve working speed I propose a copy/paste solution to automatically fill fields from an excel-row in the clipboard.

Context

Currently we have an excel where we keep track of all locations that have been previously entered. The excel consists of the following columns:

• Verbatim Locality (Maps to field Verbatim Locality in GUI)
• Verbatim Locality Comment (no mapping to GUI)
• Collection (No mapping to GUI)
• Country (Maps to field Country in GUI)
• State/Province (Maps to field State/Province in GUI)
• Latitude / Longitude (Maps to field Latitude and Longitude in Georeference-Dialog)
• Error Radius (Maps to field Error radius in Georeference-Dialog)
• Source (Maps to Method in Georeference-Dialog)
• Cell Number (No mapping to GUI)

Solution

My proposal now is that we can copy a whole row out of the excel, press CTRL + V when the DataShot-GUI is focused and it will automatically fill the 7 mentioned fields above. If I copy a row and paste it into a text editor, the following is shown:
Mendrisio : Vla. Forestera Switzerland Ticino Villa Foresta 45.86259, 8.98313 250 Geoportal The seperator (not shown here) is unicode U+0009 (Horizontal Tabulation). My pseudo-code solution would look like this:

if (user.pressed(CTRL+V) {
  if (user.getClipboard() != null) {
    if (user.getClipboard().split(U+0009).length() >= 9) { // The first 9 columns are necessary, but more should also work, so we check for more than or exactly 9 columns
      splits = user.getClipboard().split(U+0009)
      gui.verbatimLocality = splits[0]
      // Splits 1 and 2 are not mapped to GUI
      gui.country = splits[3]
      gui.stateProvince = splits[4]
      gui.specificLocality = splits[5]
      gui.georeference.lat = splits[6].split(", ")[0] // lat / long are in the same column and seperated by ", "
      gui.georeference.lon = splits[6].split(", ")[1]
      gui.georeference.errRad = splits[7]
      gui.georeference.method = splits[8]
    }
  }
}

Is it feasible to implement such a solution?

[GenieTim]

Thanks for the idea! Yes, this is absolutely feasible and your issue including pseude-code (especially already knowing which tabulation character to split at) is really awesome.

I will make sure to implement this this week, though I will first think of a way to have the mapping a bit more dynamic (e.g. via configuration), so the solution could also be used if the Excel had other/gets new columns in between the existing ones.

[yvessavoy]

Thanks Tim! I attached you a screenshot of an excel example for reference. Copy/Pasting only the first 9 columns makes more sense IMO as column 10 is never used. I will adapt my original comment to only check if there are at least 9 splits available.

[GenieTim]

Some questions on how you would expect this to behave:

1. What to do if one of the fields to fill already has a value? (a) overwrite, (b) ignore & skip, (c) ignore & cancel, (d) prepend, (e) append, (f) something else
2. Would it be useful to propagate, e.g. it works if you paste 9 fields to verbatimLocality, but also if you paste 6 fields to country, etc.?

[yvessavoy]

To 1: It should behave as the GeoDialog does today with country / state. Skip the ones that are already filled. So if I Country is already filled the software should just leave it as is and continue with the other fields. So it would be proposal B.

To number 2, I don't think we need such a functionality. We either copy a whole row or nothing at all.

Generally speaking, I thought about the paste-thing again and I think it would make sense to have a new button next to the Georeference-Button (called "Paste from Xls" or something like that). If I press this button the new behaviour should be invoked. This would not confuse anyone due to a dedicated new button.

[GenieTim]

Thanks for the specification, this feature is now available in https://github.com/BernhardWebstudio/DataShot_DesktopApp/releases/tag/untagged-DataShot-20210204-1214-beta-c3e2d29097f0b4b88ec5e009615a5b07c8023672

It will also reduce the impact of #34 , as those fields will therefore not be filled by the autocomplete. This is advantagous, as @ greffm asked me to leave that issue for someone else.

[yvessavoy]

Thanks tim for the quick implementation. We just tested it and have 2 things to add:

• Can the button be next to the Georeference-Button in the main-gui instead of in the Georeference-dialog? When copy/pasting it's not necessary to open the Georeference-Dialog, thus it would save us 2 clicks. So we would prefer to have the button to the right of the Georeference-Button.
• When pasting a row from the excel, the State/Province is not copied from the excel correctly (empty) and then automatically filled (in my test now wrongly filled as I copied Zürich and got Zurich). State/Province is Columns nr. 5, so index 4 in the excel. Could you check that as well?

Other than that, it works pretty good, so thanks again!

[GenieTim]

Thanks for the response. The first point is a good one, though I fear there are dangers of wrong values in the Georeference dialog if they are set without anyone having a look at them, e.g. when the structure of the Excel-file changed.

For the second one, you might be able to adjust it yourself:
In DataShot, click in the Menu on Configuration -> Preferences (see attached Screenshot). Then, you can look for the preferences with a key beginning with "georef.import....": the one for State/Province is georef.import.state_province.col. Adjust this value to your needs. If the index is correct, it might be a mistake on my site though, feel free to reopen this issue if this is the case.

[yvessavoy]

Seems like the index is correct, can you check this?`

This is the record I copied:

Maybe it's due to the non-ASCII bytes? EDIT: I just checked country, it has the same problem. It seems like everything is pasted and then the auto-fetching for country and state/province happens.

Regarding point 1, I agree, so let's leave it like that.

PS: I don't think we can reopen issues, I didn't find any button to do so.

[yvessavoy]

Another point I just found: When pasting it should overwrite any data already present. Sorry for that, earlier I said that it should skip the fields filled, but now, after some use, we see that it makes more sense if it overwrites anything present.

[GenieTim]

Another point I just found: When pasting it should overwrite any data already present. Sorry for that, earlier I said that it should skip the fields filled, but now, after some use, we see that it makes more sense if it overwrites anything present.

Use the property georef.import.overwrite and set it to "true" (in the newest, untested version).

[EntomologicalZivi]

"Method" does not get put in. Its always left on not recorded.

[yvessavoy]

Any update on the Country / State bug where these fields are not taken from the excel but are filled automatically from the GEO-API?

[GenieTim]

Any update on the Country / State bug where these fields are not taken from the excel but are filled automatically from the GEO-API?

I have not yet heard of the one who is supposed to fix it, no, sorry.

[GenieTim]

Can the button be next to the Georeference-Button in the main-gui instead of in the Georeference-dialog? When copy/pasting it's not necessary to open the Georeference-Dialog, thus it would save us 2 clicks. So we would prefer to have the button to the right of the Georeference-Button.

In v1.14, it finally is, together with all the other pressing georeference fields.