#!/bin/bash
########################################################################################################
## Author: 	Brian Hunter
## Date:	09/03/2024
## Email:  	brian@sutechy.com
## Script:	import_spreadsheet.sh 
## Description: Script that will take an exported spreadsheet output and import into a MariaDB Database.
##              You need to export document and save as CVS Text Sheet with ':' deliminter. Works with
##		OpenOffice, Libre Office and Microsoft Office Spreadsheets.
##
## 		Setup Database Table as follows:
## 		MariaDB [sutechyc_sheets]> describe employees
##     		-> ;
##		+-----------+-------------+------+-----+---------------------+----------------+
##		| Field     | Type        | Null | Key | Default             | Extra          |
##		+-----------+-------------+------+-----+---------------------+----------------+
##		| sheet_id  | bigint(128) | NO   | PRI | NULL                | auto_increment |
##		| firstname | varchar(30) | YES  |     | NULL                |                |
##		| lastname  | varchar(30) | YES  |     | NULL                |                |
##		| datetime  | datetime    | NO   |     | current_timestamp() |                |
##		+-----------+-------------+------+-----+---------------------+----------------+
##
##		Database changed
##		MariaDB [sutechyc_sheets]> select * from employees;
##		+----------+-----------+----------+---------------------+
##		| sheet_id | firstname | lastname | datetime            |
##		+----------+-----------+----------+---------------------+
##		|        1 | Brian     | Hunter   | 2024-09-03 12:01:13 |
##		|        2 | Joe       | Smith    | 2024-09-03 12:01:13 |
##		|        3 | Honey     | Pot      | 2024-09-03 12:01:13 |
##		+----------+-----------+----------+---------------------+
##		3 rows in set (0.002 sec)
########################################################################################################
clear
dbname="mydb_name"
dbuser="mydb_user"
dbpasswd="mydb_password"
table="employees"

for user in `cat $HOME/bin/prac-BASH/MyTest` 
do printf "USER=$user\n"
   fname=`echo $user | awk -F: '{print $1}'`
   lname=`echo $user | awk -F: '{print $2}'`
   #printf "FNAME=$fname\n"
   #printf "LNAME=$lname\n"
   mysql -u $dbuser -p$dbpasswd $dbname -e "INSERT INTO $table (sheet_id, firstname, lastname, datetime) VALUES (NULL, '$fname', '$lname', current_timestamp())";
done
exit 0