BASH script to analyse data in Collectory

collectory-sql-script-1.sh

#!/bin/bash

# https://unix.stackexchange.com/a/295528
# get list of fields: 
# mysql -u root -ppassword -D collectory -s -e "describe data_resource;" (paste into Excel and copy first column)
#
# run as ./collectory-sql-script-1.sh data_resource_fields.txt > data_resource_fields.csv

TABLE="data_hub"
USER="root"          # change to your DB user
PASSWORD="password"  # change to your DB password
if [[ ! -r "$1" ]]; then
    echo "unable to read file '$1'"
    exit 1
fi

echo "field,count"

cat "$1" | while read fieldname; do
    echo -n "$fieldname,"
    echo $(mysql -sN -u $USER -p$PASSWORD -D collectory -e "SELECT COUNT(id) FROM $TABLE WHERE ${fieldname} <> '' OR ${fieldname} IS NOT NULL;")
done

data_resource_fields.txt

id
version
uid
phone
data_provider_id
altitude
state
rights
citable_agent
name
isalapartner
focus
longitude
attributions
website_url
network_membership
citation
acronym
last_updated
guid
pub_description
email
address_street
address_state
address_postcode
address_post_box
address_country
address_city
image_ref_file
image_ref_caption
image_ref_copyright
image_ref_attribution
tech_description
date_created
latitude
user_last_modified
notes
display_name
logo_ref_file
logo_ref_caption
logo_ref_copyright
logo_ref_attribution
institution_id
taxonomy_hints
data_generalizations
information_withheld
resource_type
license_type
license_version
connection_parameters
contributor
harvest_frequency
harvesting_notes
last_harvested
status
data_currency
last_checked
mobilisation_notes
permissions_document
download_limit
filed
risk_assessment
content_types
permissions_document_type
default_darwin_core_values
provenance
public_archive_available
image_metadata
gbif_dataset
keywords
make_contact_public
begin_date
east_bounding_coordinate
end_date
gbif_doi
gbif_registry_key
geographic_description
is_shareable_withgbif
method_step_description
north_bounding_coordinate
pub_short_description
purpose
quality_control_description
south_bounding_coordinate
west_bounding_coordinate