Manually updating large sets of borrower data can be time-consuming and prone to errors. In this guide, we'll walk through the process of updating WhatsApp numbers (referred to as "phone" in our system) and date of birth for borrowers in Koha using SQL and spreadsheet tools.
Step 1: Identify Missing Information
First, we need to identify borrowers with missing date of birth and WhatsApp numbers (phone) in our database. We can do this using a SQL query:
SELECT
borrowernumber,
cardnumber,
surname,
phone,
dateofbirth
FROM
borrowers
WHERE
dateofbirth IS NULL
AND phone IS NULL;
Step 2: Save the Report
Save the results of the SQL query as a report for reference during the update process.
Step 3: Prepare Spreadsheet or Google Form
Create a spreadsheet with columns borrowernumber, dateofbirth, and phone or create a Google Form with fields for Cardnumber, Date of Birth, and WhatsApp Number.
Step 4: VLOOKUP Borrowernumbers
In the spreadsheet, use the VLOOKUP function to match the cardnumber from the first sheet to the second sheet and retrieve the respective borrowernumber.
Step 5: Save as update_dob_phone.csv
Save the updated spreadsheet as a CSV file named update_dob_phone.csv.
Step 6: Upload CSV File
Upload the update_dob_phone.csv file into the web server directory /var/www/html.
sudo cp update_dob_phone.csv /var/www/html
Step 7: Update Database
Now, we'll use SQL to update the borrower information in our database.
sudo mysql -uroot -pmysqlrootpassword
USE koha_library;
CREATE TEMPORARY TABLE temp_borrower_data (
borrowernumber INT,
dateofbirth DATE,
phone VARCHAR(20)
);
LOAD DATA INFILE '/var/www/html/update_dob_phone.csv'
INTO TABLE temp_borrower_data
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;
UPDATE borrowers b
JOIN temp_borrower_data t
ON b.borrowernumber = t.borrowernumber
SET b.dateofbirth = t.dateofbirth,
b.phone = t.phone;
DROP TEMPORARY TABLE IF EXISTS temp_borrower_data;
