search

kevinrae / mosskey

Multiple entry key for North American Mosses
0 stars 0 forks source link

Test impact of structural changes to google spreadsheet #20

Closed kevinrae closed 6 years ago

kevinrae commented 6 years ago

  1. Remove keycharacter row (validate rht, lft cell data updates correctly)

  2. Remove taxa row (check for valid map query on computed map tab)

stephenprae commented 6 years ago

Do you confirm that both 1 and 2 implement together or in sequence with testing after each?

stephenprae commented 6 years ago

GitHub account created with username stephenprae

kevinrae commented 6 years ago

Let's try 1 & 2 above in sequence.

kevinrae commented 6 years ago

column deletion of col 326 metaneckera menziesii breaks the map query

kevinrae commented 6 years ago

Good news... found fixes for 1 & 2 above. Tested by making a copy of the spreadsheet and making edits in the new sheets. Here is the process:

Delete row n (KeyCharacter) from Master sheet

Example (delete row n=13)

Here is an example of the corrupted formula in cell d14 (row n+1 after a row n deletion event):

=ifs(and(A14="branch",#REF!="trunk"),#REF!+1, AND(A14="leaf", #REF!="branch"),#REF!+1, and(A14="leaf", #REF!="leaf"),#REF!+1, A14="trunk",maxifs(E:E,J:J,#REF!)+1, A14="branch",maxifs($E$5:E13,J$5:J13,J14,$K$5:K13,#REF!)+1)

And here is the fixed formula with the corrected values:

=ifs(and(A14="branch",A13="trunk"),D13+1, AND(A14="leaf", A13="branch"),D13+1, and(A14="leaf", A13="leaf"),E13+1, A14="trunk",maxifs(E:E,J:J,J13)+1, A14="branch",maxifs($E$5:E13,J$5:J13,J14,$K$5:K13,K13)+1)

Delete column n (Taxon) from Master

stephenprae commented 6 years ago

K

This is great news. I am a little busy on another non-computer issue so will get back to the software installation and review of the newly described process in about an hour. Congrats. Your effort here yields success!

Dad

On Sun, Mar 4, 2018 at 11:24 kevinrae notifications@github.com wrote:

Good news... found fixes for 1 & 2 above. Tested by making a copy of the spreadsheet and making edits in the new sheets. Here is the process: Delete row n (KeyCharacter) from Master sheet

  • unhide all columns
  • delete row n
  • Row n+1 will have #REF values...
  • to fix edit formula for row n+1 (refer to formulas in row n-1 for which cells should be referenced) Note: row n+1 will move up and become row n after you delete row n, but I'm just going to ignore the deletion and refer to it by it's 'beforehand' name: i.e. n+1
  • these are complicated formulas... look for #REF, these are the errors you need to fix...
  • you know the #REFs all pointed to cells in row n, but you don't know which column - so look at the same formula but in row n-1....
  • each of these columns have the same formula just incremented for each row. Anyway, just edit the formula... wait for the sheet to recalculate and the #REFs should disappear.

Delete column n (Taxon) from Master

  • delete taxon in col n
  • delete corresponding column from MAP-WIP (should have an entire column of #REFs)
  • edit formula in MAP-WIP!A1. Find the #REF and delete.
  • confirm Taxa-Generated tab no longer displays taxon from col n (you shouldn't have to do anything here)

— You are receiving this because you were assigned. Reply to this email directly, view it on GitHub https://github.com/kevinrae/mosskey/issues/20#issuecomment-370256013, or mute the thread https://github.com/notifications/unsubscribe-auth/AjUNqukd8LOA4eIxgrIN_irng22xVAlJks5tbD9WgaJpZM4SbHS3 .

-- Stephen P. Rae, Bryologist - Plant Ecologist Napa Valley California

kevinrae commented 6 years ago

Leaving this open for now... so we can track testing and document remediation necessary for adding columns (taxon) and rows (keycharacters).

kevinrae commented 6 years ago

Hmmm. Found a problem where taxa table in mysql seems to have different ids than the imported table.csv file.

db:

mysql> select * from taxa where taxa.id BETWEEN 326 AND 331;
+-----+------------------------+-------------+-------+-------------+---------------------+
| id  | Name                   | description | URLId | AuthorityId | TimeStamp           |
+-----+------------------------+-------------+-------+-------------+---------------------+
| 326 | Macrocoma              | NULL        |  NULL |        NULL | 2018-03-05 19:55:41 |
| 327 | Macromitrium           | NULL        |  NULL |        NULL | 2018-03-05 19:55:41 |
| 328 | Macromitrium richardii | NULL        |  NULL |        NULL | 2018-03-05 19:55:41 |
| 329 | Meesia                 | NULL        |  NULL |        NULL | 2018-03-05 19:55:41 |
| 330 | Metaneckera            | NULL        |  NULL |        NULL | 2018-03-05 19:55:41 |
| 331 | Metaneckera menziesii  | NULL        |  NULL |        NULL | 2018-03-05 19:55:41 |
+-----+------------------------+-------------+-------+-------------+---------------------+
6 rows in set (0.00 sec)

and taxa.csv:

...
grep 326 -A10 -B10 Taxa.csv 
317,Loeskypnum
318,Loeskypnum badium
319,Lorentziella
320,Luisierella
321,Lyellia
322,Macrocoma
324,Macromitrium
323,Macromitrium richardii
325,Meesia
327,Metaneckera
326,Metaneckera menziesii
328,Microbryum
329,Micromitrium
330,Mielichhoferia
331,Mnium
332,Molendoa
334,Myrinia
333,Myrinia pulvinata
337,Myurella
335,Myurella julacea
336,Myurella siberica
...

need to get to the bottom of this.

kevinrae commented 6 years ago

ok taxa.csv must be imported with this command: 

load data local infile 'Map.csv' into table Map fields terminated by ',' enclosed by '"' lines terminated by '\n' ignore 1 lines (taxaid, keycharacterid);

and not this one:

load data local infile 'Map.csv' into table Map fields terminated by ',' enclosed by '"' lines terminated by '\n' ignore 1 lines (taxaid, keycharacterid);

note: this leaves all taxa names in the db with \r (carriage return) appended to them. this causes strange behavior in the mysql interactive client and results do not display. it also seems to crash MySQLWorkbench. annoying. need to clean up these carriage returns.

kevinrae commented 6 years ago

aha. found that all these .csv files exported from google sheets are line terminated with CRLF (i.e. \r\n). So, after saving the .csv files I need to preprocess these exported files to change:

\r\n

to:

\n

...and only then is it safe to run a db import.

kevinrae commented 6 years ago
  1. open these files in vim (linking to issue #21 )
  2. :set ff=unix
  3. :wq!
  4. verify with: od -c \<filename>
kevinrae commented 6 years ago

This issue was moved to kevinrae/mosskey-www#5