แก้ไขไฟล์ my.cnf
[client]
local_infile=1
ไฟล์ script.sh
#!/usr/bin/env sh
USER=root
PASSWORD=123456
HOST=localhost
PORT=3306
DATABASE=vaccine
SQLITE_FILE=province_40.sqlite
sqlite3 -header -csv $SQLITE_FILE "select * from appointment;" > appointment.csv && \
sqlite3 -header -csv $SQLITE_FILE "select * from hospital;" > hospital.csv && \
sqlite3 -header -csv $SQLITE_FILE "select * from hospital_person_queue;" > hospital_person_queue.csv && \
sqlite3 -header -csv $SQLITE_FILE "select * from hospital_user;" > hospital_user.csv && \
sqlite3 -header -csv $SQLITE_FILE "select * from immunization_aefi_followup;" > immunization_aefi_followup.csv && \
sqlite3 -header -csv $SQLITE_FILE "select * from immunization_aefi_observe;" > immunization_aefi_observe.csv && \
sqlite3 -header -csv $SQLITE_FILE "select * from person;" > person.csv && \
sqlite3 -header -csv $SQLITE_FILE "select * from person_conflict;" > person_conflict.csv && \
sqlite3 -header -csv $SQLITE_FILE "select * from vaccine_appointment;" > vaccine_appointment.csv && \
sqlite3 -header -csv $SQLITE_FILE "select * from vaccine_inventory;" > vaccine_inventory.csv && \
sqlite3 -header -csv $SQLITE_FILE "select * from vaccine_inventory_lot;" > vaccine_inventory_lot.csv && \
sqlite3 -header -csv $SQLITE_FILE "select * from vaccine_inventory_movement;" > vaccine_inventory_movement.csv && \
sqlite3 -header -csv $SQLITE_FILE "select * from vaccine_inventory_movement_type;" > vaccine_inventory_movement_type.csv && \
sqlite3 -header -csv $SQLITE_FILE "select * from vaccine_manufacturer;" > vaccine_manufacturer.csv && \
sqlite3 -header -csv $SQLITE_FILE "select * from visit_immunization;" > visit_immunization.csv && \
mysql -u$USER -p$PASSWORD -h $HOST -P $PORT -e "truncate table appointment" $DATABASE && \
mysql -u$USER -p$PASSWORD -h $HOST -P $PORT -e "truncate table hospital" $DATABASE && \
mysql -u$USER -p$PASSWORD -h $HOST -P $PORT -e "truncate table hospital_person_queue" $DATABASE && \
mysql -u$USER -p$PASSWORD -h $HOST -P $PORT -e "truncate table hospital_user" $DATABASE && \
mysql -u$USER -p$PASSWORD -h $HOST -P $PORT -e "truncate table immunization_aefi_followup" $DATABASE && \
mysql -u$USER -p$PASSWORD -h $HOST -P $PORT -e "truncate table immunization_aefi_observe" $DATABASE && \
mysql -u$USER -p$PASSWORD -h $HOST -P $PORT -e "truncate table person" $DATABASE && \
mysql -u$USER -p$PASSWORD -h $HOST -P $PORT -e "truncate table person_conflict" $DATABASE && \
mysql -u$USER -p$PASSWORD -h $HOST -P $PORT -e "truncate table vaccine_appointment" $DATABASE && \
mysql -u$USER -p$PASSWORD -h $HOST -P $PORT -e "truncate table vaccine_inventory" $DATABASE && \
mysql -u$USER -p$PASSWORD -h $HOST -P $PORT -e "truncate table vaccine_inventory_lot" $DATABASE && \
mysql -u$USER -p$PASSWORD -h $HOST -P $PORT -e "truncate table vaccine_inventory_movement" $DATABASE && \
mysql -u$USER -p$PASSWORD -h $HOST -P $PORT -e "truncate table vaccine_inventory_movement_type" $DATABASE && \
mysql -u$USER -p$PASSWORD -h $HOST -P $PORT -e "truncate table vaccine_manufacturer" $DATABASE && \
mysql -u$USER -p$PASSWORD -h $HOST -P $PORT -e "truncate table visit_immunization" $DATABASE && \
mysql -u$USER -p$PASSWORD -h $HOST -P $PORT -e "SET GLOBAL local_infile=1" && \
mysql -u$USER -p$PASSWORD -h $HOST -P $PORT --local-infile $DATABASE -e "LOAD DATA LOCAL INFILE 'hospital.csv' INTO TABLE hospital FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 LINES" && \
mysql -u$USER -p$PASSWORD -h $HOST -P $PORT --local-infile $DATABASE -e "LOAD DATA LOCAL INFILE 'appointment.csv' INTO TABLE appointment FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 LINES" && \
mysql -u$USER -p$PASSWORD -h $HOST -P $PORT --local-infile $DATABASE -e "LOAD DATA LOCAL INFILE 'hospital_person_queue.csv' INTO TABLE hospital_person_queue FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 LINES" && \
mysql -u$USER -p$PASSWORD -h $HOST -P $PORT --local-infile $DATABASE -e "LOAD DATA LOCAL INFILE 'hospital_user.csv' INTO TABLE hospital_user FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 LINES" && \
mysql -u$USER -p$PASSWORD -h $HOST -P $PORT --local-infile $DATABASE -e "LOAD DATA LOCAL INFILE 'immunization_aefi_followup.csv' INTO TABLE immunization_aefi_followup FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 LINES" && \
mysql -u$USER -p$PASSWORD -h $HOST -P $PORT --local-infile $DATABASE -e "LOAD DATA LOCAL INFILE 'immunization_aefi_observe.csv' INTO TABLE immunization_aefi_observe FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 LINES" && \
mysql -u$USER -p$PASSWORD -h $HOST -P $PORT --local-infile $DATABASE -e "LOAD DATA LOCAL INFILE 'person.csv' INTO TABLE person FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 LINES" && \
mysql -u$USER -p$PASSWORD -h $HOST -P $PORT --local-infile $DATABASE -e "LOAD DATA LOCAL INFILE 'person_conflict.csv' INTO TABLE person_conflict FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 LINES" && \
mysql -u$USER -p$PASSWORD -h $HOST -P $PORT --local-infile $DATABASE -e "LOAD DATA LOCAL INFILE 'vaccine_appointment.csv' INTO TABLE vaccine_appointment FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 LINES" && \
mysql -u$USER -p$PASSWORD -h $HOST -P $PORT --local-infile $DATABASE -e "LOAD DATA LOCAL INFILE 'vaccine_inventory.csv' INTO TABLE vaccine_inventory FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 LINES" && \
mysql -u$USER -p$PASSWORD -h $HOST -P $PORT --local-infile $DATABASE -e "LOAD DATA LOCAL INFILE 'vaccine_inventory_lot.csv' INTO TABLE vaccine_inventory_lot FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 LINES" && \
mysql -u$USER -p$PASSWORD -h $HOST -P $PORT --local-infile $DATABASE -e "LOAD DATA LOCAL INFILE 'vaccine_inventory_movement.csv' INTO TABLE vaccine_inventory_movement FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 LINES" && \
mysql -u$USER -p$PASSWORD -h $HOST -P $PORT --local-infile $DATABASE -e "LOAD DATA LOCAL INFILE 'vaccine_inventory_movement_type.csv' INTO TABLE vaccine_inventory_movement_type FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 LINES" && \
mysql -u$USER -p$PASSWORD -h $HOST -P $PORT --local-infile $DATABASE -e "LOAD DATA LOCAL INFILE 'vaccine_manufacturer.csv' INTO TABLE vaccine_manufacturer FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 LINES" && \
mysql -u$USER -p$PASSWORD -h $HOST -P $PORT --local-infile $DATABASE -e "LOAD DATA LOCAL INFILE 'visit_immunization.csv' INTO TABLE visit_immunization FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 LINES" && \
rm *.csvเปลี่ยนโหมดไฟล์
chmod +x script.sh
Run
./script.sh
