Spurious commas at the end of tab-delimited export files

grantfitzsimmons commented 1 year ago

Please see the attached csv that I exported from a SPECIFY query. You will notice that all of the columns from the query have been mashed into a single column, column A, on the spreadsheet.

issue_query_results.csv

Contents of the exported CSV:

"Catalog Number Genus   Species Preparations Tissue#    Tissue Source   Tissue Available    Barcode Submit? Barcode Success?    Latitude1   Longitude1  Verbatim Locality",,,,,
"23853  Urobatis    jamaicensis 126 Muscle  1           25.3770000000   -81.7446660000  [Gulf of Mexico", ca. 59 km west on Ponce de Leon Bay, FL],,,
"24734  Urobatis    jamaicensis 946 Muscle  1           26.0381660000   -82.1500000000  [Gulf of Mexico", ca. 42 km southwest of North Naples, FL],,,
"29597  Urobatis    jamaicensis 3414    Muscle  1           25.9020000000   -81.9028330000  [Gulf of Mexico", ca. 19.7 km west of Marco Island, FL],,,
"29631  Urobatis    jamaicensis 3498    Muscle  1   1   1   26.0381660000   -82.1500000000  [Gulf of Mexico", ca. 42 km southwest of North Naples, FL],,,
"34040  Urobatis    jamaicensis 4148    Muscle  1           25.5753330000   -82.1571660000  [Gulf of Mexico", ca. 55.93 km SW of Ten Thousand Island, FL; Start 25.575333 N, -82.157166 W, End 25.550333 N, -82.150833 W]
"34685  Urobatis    jamaicensis 4293    L Pelvic    1           25.3135000000   -82.1410000000  [Gulf of Mexico ca. 95.01 km SW of Everglades City", FL; START 25.31350N -82.1410W END 25.33833330N -82.14416660W],,,,
"34685  Urobatis    jamaicensis 4294    R Pelvic    1           25.3135000000   -82.1410000000  [Gulf of Mexico ca. 95.01 km SW of Everglades City", FL; START 25.31350N -82.1410W END 25.33833330N -82.14416660W],,,,
"35239  Urobatis    jamaicensis 4332    Muscle  1           25.3135000000   -82.1410000000  [Gulf of Mexico ca. 95.01 km SW of Everglades City", FL; START 25.31350N -82.1410W END 25.33833330N -82.14416660W],,,,
"35308  Urobatis    jamaicensis 4325    R Pelvic    1   1   1   26.8118170000   -80.0458170000  [Munyon Island", south end, North Palm Beach, FL; 26.8118170N -80.0458170S],,
"36272  Urobatis    jamaicensis 4873    Muscle  1           25.7345000000   -82.0658333300  Gulf of Mexico ca. 40.00 km WSW of Ten Thousand Island", FL; START 25.73450N -82.065833330W END 25.726166670N -82.0920W],,,,

There should not be the extra set of commas at the end

maxpatiiuk commented 1 year ago

@grantfitzsimmons were you able to replicate this on the test server? I can't get it to happen

grantfitzsimmons commented 1 year ago

I was also unable to recreate across several delimiters and queries. I'll close this for now...

grantfitzsimmons commented 1 year ago

https://specify.saiab.ac.za/specify/query/860/

For some reason, in the csv of query results, there are quotes around the captions of the following fields: Determination Remarks, Preparation Remarks, and Maximum and Minimum Depth.

On v7.8.7.1 in saiab

query_results_2023-03-21T13 56 51.176558.csv

On macOS in Firefox, I was unable to recreate:

Catalogue Number    Collection Name Taxon   Family  Catalogue Number    Collection Name Taxon   Family
199809  Fish Observation    Caranx ignobilis    Carangidae  27266   Tissue Collection   Lutjanus bohar  Carangidae
199809  Fish Observation    Caranx ignobilis    Carangidae  27286   Tissue Collection   Epinephelus cyanopodus  Carangidae
199809  Fish Observation    Caranx ignobilis    Carangidae  27287   Tissue Collection   Lutjanus bohar  Carangidae

a_tissueDets.json.zip

grantfitzsimmons commented 1 year ago

I was able to recreate! Happens in both Chrome, Edge, and Firefox on macOS and Windows.

Query: A1 Standard Query1 (Quotes ).json.zip

Output:

Catalogue Number    "Alt Catalogue
Number" Family  Taxon   Preferred Taxon Order   "Determination 
Remarks"    Preparation Type    "Preparations Preparation
Remarks"    Event Number    Locality    Country Geography   Locality Lat1text   Locality Long1text  Latitude1   Longitude1  Drainage Branch River Name  Drainage System Locality Details Ocean  "Maximum
Depth (m)"  "Minimum
Depth (m)"  Preparations Container  Preparations Count  Preparations TissueTubeNumber   Individual ID   SAIAB Ind No.   Standard Length Total Length    Catalogued Date Collection Object Remarks   Determinations Current  Collectors  Collecting Event Method Start Date  Last Name   Determiner  Verbatim Locality   Locality originalElevationUnit  Type Status State   Original Filename   Preparation Type
80643       Serranidae  Variola albimarginata       Perciformes             Taa 4   Taa Reef 4  Tanzania    Africa | Tanzania   5.300121° S 39.129667° E    -5.3001210000   39.1296670000               Southwestern Indian Ocean   300.0000000000  70.0000000000                               2008/03/20      1   Lucy E.P. Scott purchased   2007/09/27  Mackenzie   Monica Mwale                        
101200      Acanthuridae    Acanthurus dussumieri       Perciformes Transect count  5 individuals           Alg 134 Station 25-1    Doodles Reef    Mozambique  Africa | Mozambique 26.828917° S    32.896767° E    -26.8289170000  32.8967670000               Western Indian Ocean                                        2007/05/27      1   Martin K.S. Smith   SCUBA   2005/04/22  Watt-Pringle    Martin K.S. Smith                       
94714       Tripterygiidae  Helcogramma rosea       Perciformes                 Phuket, Thailand    Thailand    Asia | Thailand                                                             27.6        2007/11/16      1               unknown R.W. Winterbottom           paratype            
76518       Distichodontidae    Hemigrammocharax        Characiformes               JPF-05-002  Bridge at Musola just outside Kasanka National Park Zambia  Africa | Zambia 12.63379955° S  30.2777996063° E    -12.6337995500  30.2777996063       Musola River    Luapula System                                          2005/11/22      1   Roger Bills; Alex D. Chilala; John Friel    various 2005/09/20  Bushula Roger Bills                     
76518       Distichodontidae    Hemigrammocharax        Characiformes               JPF-05-002  Bridge at Musola just outside Kasanka National Park Zambia  Africa | Zambia 12.63379955° S  30.2777996063° E    -12.6337995500  30.2777996063       Musola River    Luapula System                                          2005/11/22      1   Roger Bills; Alex D. Chilala; John Friel    various 2005/09/20  Bushula Roger Bills                     
76518       Distichodontidae    Hemigrammocharax        Characiformes               JPF-05-002  Bridge at Musola just outside Kasanka National Park Zambia  Africa | Zambia 12.63379955° S  30.2777996063° E    -12.6337995500  30.2777996063       Musola River    Luapula System                                          2005/11/22      1   Roger Bills; Alex D. Chilala; John Friel    various 2005/09/20  Bushula Roger Bills                     
76585       Alestiidae  Micralestes     Characiformes               JPF-05-006  Luapula River Bridge    Zambia  Africa | Zambia 12.11559963° S  29.8472003937° E    -12.1155996300  29.8472003937       Luapula River   Luapula System                                          2005/11/24      1   Roger Bills; Alex D. Chilala; John Friel    gill net    2005/09/22  Bushula Roger Bills                     
76585       Alestiidae  Micralestes     Characiformes               JPF-05-006  Luapula River Bridge    Zambia  Africa | Zambia 12.11559963° S  29.8472003937° E    -12.1155996300  29.8472003937       Luapula River   Luapula System                                          2005/11/24      1   Roger Bills; Alex D. Chilala; John Friel    gill net    2005/09/22  Bushula Roger Bills                     
76585       Alestiidae  Micralestes     Characiformes               JPF-05-006  Luapula River Bridge    Zambia  Africa | Zambia 12.11559963° S  29.8472003937° E    -12.1155996300  29.8472003937       Luapula River   Luapula System                                          2005/11/24      1   Roger Bills; Alex D. Chilala; John Friel    gill net    2005/09/22  Bushula Roger Bills                     
94988       Bythitidae  Microbrotula greenfieldi        Gadiformes  Microbrotula greenfieldi MS name based on CAS 222542, Vanua Levu Island, Pascoe Reefs, west of Yadua Island, Fiji, South Pacific; 11 - 14 m depth; 16 50.2' S, 178 13.7' E; collectors DW Greenfield et al, 4.4.2002                                                                                        34.3        2006/08/15      1               unknown Michael E. Anderson         holotype            
94680       Mullidae    Mulloidichthys vanicolensis     Perciformes                 Two mile reef   South Africa    Africa | South Africa | KwaZulu-Natal   27° 31' S   32° 41' E   -27.5166664100  32.6833343506               Southwestern Indian Ocean                                       2006/10/25      1           2006/10/21  Mthombeni                   KwaZulu-Natal       
101201      Chaetodontidae  Chaetodon guttatissimus     Perciformes Transect count  8 individuals           Alg 134 Station 29-4    6 Inch Reef South Africa    Africa | South Africa   27.8666° S  32.607567° E    -27.8666000000  32.6075670000               Southwestern Indian Ocean                                       2007/05/27      1   Martin K.S. Smith   SCUBA   2005/04/26  Watt-Pringle    Martin K.S. Smith                       
94782       Dactylopteridae Dactyloptena peterseni      Dactylopteriformes  Specimen not kept           PCH2007-M07 Off Mozambique  Mozambique  Africa | Mozambique 26° 30.4' S 32° 58.4' E -26.5066666667  32.9733333333               Southwestern Indian Ocean   82.0000000000   81.0000000000                       370     2009/01/08      1   P.C. Heemstra; Elaine Heemstra  bottom trawl    2007/10/01  Traas   P.C. Heemstra                       
94782       Dactylopteridae Dactyloptena peterseni      Dactylopteriformes  Specimen not kept           PCH2007-M07 Off Mozambique  Mozambique  Africa | Mozambique 26° 30.4' S 32° 58.4' E -26.5066666667  32.9733333333               Southwestern Indian Ocean   82.0000000000   81.0000000000                       370     2009/01/08      1   P.C. Heemstra; Elaine Heemstra  bottom trawl    2007/10/01  Traas   P.C. Heemstra                       
101202      Sternoptychidae Argyropelecus       Stomiiformes    Midwater trawl          Alg 122 Co 8502 RV Algoa 122 Co 8502    Tanzania    Africa | Tanzania   8.110333° S 39.979833° E    -8.1103330000   39.9798330000               Western Indian Ocean    460.0000000000  460.0000000000                          100 2007/05/20      1   James R. Stapley    midwater trawl  2003/07/30  Watt-Pringle    James R. Stapley                        
95303       Alepisauridae   Alepisaurus brevirostris        Aulopiformes                                                                                                    2008/04/18      1               Heemstra                            
95052       Gobiidae    Trypauchen vagina       Perciformes                                                                                                 2006/11/29      1               Heemstra                            
101203      Labridae    Gomphosus caeruleus     Perciformes Transect count  1 individual            Alg 134 Station 30-1    Coral Gardens   South Africa    Africa | South Africa   27.52245° S 32.6864° E  -27.5224500000  32.6864000000               Southwestern Indian Ocean                                       2007/05/27      1   Martin K.S. Smith   SCUBA   2005/04/27  Watt-Pringle    Martin K.S. Smith                       
95283       Polynemidae Polydactylus malagasyensis      Perciformes                 Southern Mozambique Mozambique  Africa | Mozambique                             Western Indian Ocean                                    220 2008/03/31      1           2008/03/31  Heemstra                            
101204      Balistidae  Sufflamen chrysopterum      Tetraodontiformes   Transect count  9 individuals           Alg 134 Station 15-3    Kyle's Skrik    Mozambique  Africa | Mozambique 24.482133° S    35.239183° E    -24.4821330000  35.2391830000               Western Indian Ocean    17.0000000000   12.0000000000                               2007/05/20      1   Martin K.S. Smith   SCUBA   2005/04/16  Watt-Pringle    Martin K.S. Smith                       
101205      Acanthuridae    Acanthuridae        Perciformes Transect count  1 individual            Alg 134 Station 32-4    Stringers   South Africa    Africa | South Africa   27.5297° S  32.682817° E    -27.5297000000  32.6828170000               Southwestern Indian Ocean                                       2007/05/27      1   Martin K.S. Smith   SCUBA   2005/04/27  Watt-Pringle    Martin K.S. Smith                       
95193       Paralichthyidae Paralichthyidae     Pleuronectiformes                                                                                                   2007/11/21      1               Heemstra                            
101206      Callionymidae   Callionymus filamentosus        Gobiesociformes Bottom trawl  No field collection record            Alg 112 Co 8205 RV Algoa 112 Co 8205    Mozambique  Africa | Mozambique 18.9766° S  36.428333° E    -18.9766000000  36.4283330000               Western Indian Ocean    20.0000000000   20.0000000000                               2007/05/15      1       bottom trawl    2002/08/06  Watt-Pringle                            
94733       Haemulidae  Plectorhinchus flavomaculatus       Perciformes                 Bikini  South Africa    Africa | South Africa | KwaZulu-Natal   27.5333° S  32.6833° E  -27.5333000000  32.6833000000               Southwestern Indian Ocean                                       2007/08/30      1           2005/12/09  Mthombeni   Phillip C. Heemstra             KwaZulu-Natal       
95027       Gobiidae    Kelloggella     Perciformes                                                                                                 2006/11/21      1               Heemstra                            
101207      Lutjanidae  Lutjanus kasmira        Perciformes Transect count  20 individuals          Alg 134 Station 25-2    Doodles Reef    Mozambique  Africa | Mozambique 26.828917° S    32.896767° E    -26.8289170000  32.8967670000               Western Indian Ocean                                        2007/05/27      1   Martin K.S. Smith   SCUBA   2005/04/22  Watt-Pringle    Martin K.S. Smith                       
95649       Mullidae    Upeneus doriae      Perciformes SMF 26055               off Iran    Iran    Asia | Iran                             Western Indian Ocean                                71      2009/02/17      1           2009/02/17  Heemstra                            
101208      Peristediidae   Peristedion weberi      Scorpaeniformes Bottom trawl  Field colection record only           Alg 112 Co 8224 RV Algoa 112 Co 8224    Mozambique  Africa | Mozambique 18.035° S   37.62° E    -18.0350000000  37.6200000000               Western Indian Ocean    200.0000000000  162.0000000000                              2007/05/15      1       bottom trawl    2002/08/12  Watt-Pringle                            
101209      Labridae    Coris caudimacula       Perciformes Transect count  1 individual            Alg 134 Station 34-4    9 Mile Reef UTR South Africa    Africa | South Africa   27.414917° S    32.726667° E    -27.4149170000  32.7266670000               Southwestern Indian Ocean                                       2007/05/27      1   Martin K.S. Smith   SCUBA   2005/04/29  Watt-Pringle    Martin K.S. Smith                       
101210      Labridae    Thalassoma hebraicum        Perciformes Transect count  1 individual            Alg 134 Station 32-2    Stringers   South Africa    Africa | South Africa   27.5297° S  32.682817° E    -27.5297000000  32.6828170000               Southwestern Indian Ocean                                       2007/05/27      1   Martin K.S. Smith   SCUBA   2005/04/27  Watt-Pringle    Martin K.S. Smith                       
101211      Acropomatidae   Neoscombrops cynodon        Perciformes Bottom trawl  Recorded as N.annectens in field collection           Alg 112 Co 8224 RV Algoa 112 Co 8224    Mozambique  Africa | Mozambique 18.035° S   37.62° E    -18.0350000000  37.6200000000               Western Indian Ocean    200.0000000000  162.0000000000                              2007/05/15      1       bottom trawl    2002/08/12  Watt-Pringle                            
101212      Lutjanidae  Lutjanus lutjanus       Perciformes Transect count  20 individuals          Alg 134 Station 32-1    Stringers   South Africa    Africa | South Africa   27.5297° S  32.682817° E    -27.5297000000  32.6828170000               Southwestern Indian Ocean                                       2007/05/27      1   Martin K.S. Smith   SCUBA   2005/04/27  Watt-Pringle    Martin K.S. Smith                       
79952       Cichlidae   Tylochromis polylepis       Perciformes DNA specimen only           LT-04-04    Niamakolo beach Zambia  Africa | Zambia 08° 45.58' S    31° 6.73' E -8.7596664429   31.1121673584           Lake Tanganyika System                                          2007/08/10      1   Roger Bills; Robert Schelly SCUBA   2004/03/01  Mackenzie   Roger Bills                     
79952       Cichlidae   Tylochromis polylepis       Perciformes DNA specimen only           LT-04-04    Niamakolo beach Zambia  Africa | Zambia 08° 45.58' S    31° 6.73' E -8.7596664429   31.1121673584           Lake Tanganyika System                                          2007/08/10      1   Roger Bills; Robert Schelly SCUBA   2004/03/01  Mackenzie   Roger Bills                     
85036       Cyprinidae  Barbus      Cypriniformes   """Black spot"""            ES08B4  Bridge on road east of Camacupa Angola  Africa | Angola 11° 59' 3'' S   17° 43' 21'' E  -11.9841666667  17.7225000000   Kwiva   Kwiva River Kwanza System                                           2009/09/17      1   Ernst R. Swartz electrofishing  2008/08/10  Traas   Ernst R. Swartz                     
101213      Labridae    Thalassoma hebraicum        Perciformes Transect count  1 individual            Alg 134 Station 30-2    Coral Gardens   South Africa    Africa | South Africa   27.52245° S 32.6864° E  -27.5224500000  32.6864000000               Southwestern Indian Ocean                                       2007/05/27      1   Martin K.S. Smith   SCUBA   2005/04/27  Watt-Pringle    Martin K.S. Smith                       
85072       Cyprinidae  Labeobarbus     Cypriniformes   """Red eye"""           ES08B6  New dam site    Angola  Africa | Angola 09° 41' 35'' S  14° 59' 55'' E  -9.6930555556   14.9986111111   Kwanza  Kwanza River    Kwanza System                                           2009/09/17      1   Ernst R. Swartz     2008/08/16  Traas   Ernst R. Swartz                     
95119       Tripterygiidae  Tripterygiidae      Perciformes                                                                                                 2007/07/18      1               Heemstra

grantfitzsimmons commented 12 months ago

I thought I should let you know that I am still finding quotation marks in my headers when I export data from a Sp7 query.

Reported By: Willem at SAIAB

CarolineDenis commented 12 months ago

it comes from the phantom fields, the empty comas are when the field is null. it's more obvious when exporting locality data

emenslin commented 1 month ago

Fixed

specify / specify7

Spurious commas at the end of tab-delimited export files #3105