Script to upload existing family medicine

[ChasNelson1990]

The job is to write a CKAN script to upload the family medicine data:

• Develop and test this for the local dev env
• Commit the code into a folder under who-romania-ckan/util (so alongside the demo data loader).
• It might be helpful to either reuse the adx_toolbox script template, and some of the wrc demo data loader code. Especially see the demo family-medicine dataset in who-romania-ckan/util/demo_data_loader/demo_data/datasets.json
• The data attached contains weekly reports for each month.
• Inside each week's file is a sheet for each of the seven family doctors - ignore the total sheet and ignore the MHPSS & SRH sheet.
• We want to create family-medicine datasets for each Month of 2023, starting with the latest, and working backwards. Each of the resources will be a single sheet from one of the month’s weekly files. The family_doctor field should match the tab name (drop the “FD”) and the week field should be a date of format yy-mm-dd indicating the last day of the week.
• Every excel must conform exactly to the template file you have already created. (With the one exception that the top row must not be balnk - i.e start the files from row 6)
• You can manually edit the attached data (in format only, not in numeric content) to make it comply with your template (i.e. not everything has to be done by your script). What matters is that we have a good clear way to repeat the upload process in the future

[ChasNelson1990]

Requirements: folders containing XLSX files need to be extracted to ./data and renamed using format : YYMM

!https://user-images.githubusercontent.com/15812707/270295368-c02754e9-2897-4b89-9176-fe71294a018f.png|alt="image"!

• The script traverse the ./data folder and takes data from XLSX files (excluding the MONTHLY ones) and create new XLSX files with the data.
• The sheets whose names have "FD" only are taken into account.

What is left to do: write a function to test if all files and sheets were processed into the new XLSX files and in the datasets.json file as well.

Issues I had: Updated pipfile to include OpenPyXL, locking fails though, so I used a temporary pipfile to generate the pipfile.lock

Sometimes, the contents of Report date and Report period are inter-exchanged (pic below)

How did I do: I took the report period as the report date

!https://user-images.githubusercontent.com/15812707/270298798-938fd6d6-bb7e-4263-b414-ce398eabe88d.png|alt="image"!

Sometimes, the report date is a Saturday (pic below)

How did I do: edited file and set report_date as the friday before

!https://user-images.githubusercontent.com/15812707/270298970-fcca1db0-424d-469c-86be-92fc2d0b977b.png|alt="image"!

For FD SERBAN, for July

How did I do: Changed it to be like others' reporting date

!https://user-images.githubusercontent.com/15812707/270343342-5aefc226-a786-4151-b59d-03a8d0536021.png|alt="image"!

For FB CHRISTIAN for April

report_date error or wrong file? I updated the report date to be the same as other sheets in the file

!https://user-images.githubusercontent.com/15812707/270577480-6b677dde-8a4d-4829-bc48-088a48ea1076.png|alt="image"!

I am not able to load the files for all seven month; {{ckanapi.errors.CKANAPIError: ['http://wrc.minikube/api/action/resource_create', 502, '\r\n\r\n\r\n

502 Bad Gateway

\r\n

---

nginx\r\n\r\n\r\n']}}

---------------- updates -------------------

• Added logs for XLSX inconsitencies in ./logs
• Added a function to swap report_date and report period
• Updated XLSX files will be in the [ticket|https://fjelltopp.atlassian.net/jira/software/c/projects/WRC/boards/30?modal=detail&selectedIssue=WRC-18]
• I will add a short test to count processed sheets
• The tests will generate no errors if run again (tests.py), errors are in the ./logs/ csv files Originally posted by A-Souhei on Jira.

[ChasNelson1990]

In total there are 210 sheets (XLSX files) processed into 210 individual files Originally posted by A-Souhei on Jira.