20241111_DDL_RESULTS_CHRONOLOGY: Additional data for tbl_analysis_entity_ages

[rebeckahw]

Chronology_20240205.xlsx

This file contains additional data for the table tbl_analysis_entity_ages.

[roger-mahler]

Change request: 20241111_DDL_RESULTS_CHRONOLOGY.

Workflow for importing new data:

1. Add new cr 20241111_DDL_RESULTS_CHRONOLOGY

   bin/add-cr --project bugs --change 20241111_DDL_RESULTS_CHRONOLOGY  \
                --issue-id 315 --note "New/updated analysis entity ages data"

2. Create data folder:

   mkdir bugs/deploy/20241111_DDL_RESULTS_CHRONOLOGY

3. Copy the Excel file to the subfolder (must be of same format as previously imported data).
4. Extract relevant columns for the excel into two CSV files. Notice: in2csvand csvcutis part of csvkit (https://csvkit.readthedocs.io/en/1.0.7/index.html)

   
   #!/bin/bash
   SHELL=/bin/bash
   set -e
   script_dir="bugs/deploy/20241111_DDL_RESULTS_CHRONOLOGY"

in2csv --skip-lines 1 ${script_dir}/Chronology_20240205.xlsx --sheet "StratigraphicSeq" | csvformat -D ";" > ${script_dir}/stratigraphic_sequences.csv in2csv --skip-lines 1 ${script_dir}/Chronology_20240205.xlsx --sheet "ArchaeologicalSites" | csvformat -D ";" > ${script_dir}/archeological_sites.csv

csvcut --delimiter ";" --columns identifier,Chosen_C14,Chosen_OtherRadio,Chosen_Calendar,Chosen_Period,AgeFrom,AgeTo ${script_dir}/archeological_sites.csv > ${script_dir}/archeological_sites_csvcut.csv csvcut --delimiter ";" --columns identifier,Chosen_C14,Chosen_OtherRadio,Chosen_Calendar,Chosen_Period,AgeFrom,AgeTo ${script_dir}/stratigraphic_sequences.csv > ${script_dir}/stratigraphic_sequences_csvcut.csv

4. Develop change request script:

Example:

```plpgsql

begin;

\copy bugs_import.results_chronology_import ("identifier","Chosen_C14","Chosen_OtherRadio","Chosen_Calendar","Chosen_Period","AgeFrom","AgeTo") from 'deploy/20241111_DDL_RESULTS_CHRONOLOGY/archeological_sites_csvcut.csv'  with ( format csv,  header, quote '"', delimiter ',',  encoding 'utf-8' );

update bugs_import.results_chronology_import set "change_request" = '20241111_DDL_RESULTS_CHRONOLOGY', "source" = 'archeological_sites' where "source" is null;

\copy bugs_import.results_chronology_import ("identifier","Chosen_C14","Chosen_OtherRadio","Chosen_Calendar","Chosen_Period","AgeFrom","AgeTo") from 'deploy/20241111_DDL_RESULTS_CHRONOLOGY/stratigraphic_sequences_csvcut.csv'  with ( format csv,  header, quote '"', delimiter ',',  encoding 'utf-8' );

update bugs_import.results_chronology_import set "change_request" = '20241111_DDL_RESULTS_CHRONOLOGY', "source" = 'stratigraphic_sequences' where "source" is null;

-- <<ADD ANY DATA CORRECTIONS HERE>>

do $$
begin
    perform sead_utility.import_pending_results_chronologies('20241111_DDL_RESULTS_CHRONOLOGY');
end $$;

end;

commit;

[roger-mahler]

Change request: 20241112_DDL_RESULTS_CHRONOLOGY_IMPORT_UDF.