Using bash script : How to Import data from a dsv file into multiple tables in mysql

UNIX for Beginners Q & A
1

HI

I have a dsv file that looks like:

<<BOF>>
record_number|id_number|first name|last name|msisdn|network|points|card number|gender
312|9101011234011|Test Junior|Smith|071 123 4321|MTN|73|1241551413214444|M
313|9012023213011|Bob|Smith|27743334321|Vodacom|3|1231233232323244|M
314|8706055678011|Frank|Frankinson|2771 156 1567|8ta|0|1231123453214444|M
315|9102078765011|Mary|Van Niekerk|+27(0)711236677|CellC|2|1278933213214444|F

316|9005074545011|Susan|Wilson|0821121124|CellC|705|1231233216544444|F
317|9101013232011|Katherine|Jeeves|+271233214|8ta|112|1231233678214444|F
318|9101011234011|Matthew|Matthias|0711111111|MTN||1231555213214444|M
319|9103126666011|Michael|Bay|085-6122-161|8ta|63|1231244413214444|M
320|7506023232300|Tyrone|Olivier|711234322|CellC|89|1234563213214444|M
321|8901020304055|Burt|Jackson|071 4566544|Vodacom|1|4567233213214444|M
<<EOF>>

I also have tables in Mysql looking like this :

CREATE TABLE `tUSER` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`id_number` varchar(20) NOT NULL,
`first_names` varchar(100) NOT NULL,
`last_name` varchar(100) NOT NULL,
PRIMARY KEY (`id`),
INDEX(`id_number`,`first_names`,`last_name`)
);
CREATE TABLE `tPROFILE` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`tUSER_id` bigint(20) DEFAULT NULL,
`tTYPES_id` bigint(20) DEFAULT NULL,
`value` varchar(100) NOT NULL,
PRIMARY KEY (`id`),
INDEX(`tUSER_id`,`tTYPES_id`,`value`)
);
CREATE TABLE `tTYPES` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`type` varchar(100) NOT NULL DEFAULT '',
`description` varchar(255) NOT NULL,
`deleted` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
INDEX(`type`,`description`,`deleted`)
);

How would i go about importing the data from the dsv file to the 3 tables above using a bash script

2

Do you have any experience with Perl?

3

Thank you for your response , no steve I don't have any experience there . Perhaps a perl script could also help because I'm looking to have reusable script .

4

I use Perl and Bash everyday. I have written a lot of scripts that I use over and over. Delimited files arent hard to work with with Perl. Look at the split routine for Perl to start with. File handlers. Reach out to me if you need help with it.

5

I have written an sql query , my intentions are to read and store the data to a tmp table, after that i want to read and insert into two tables that are related through a many to many relation ship . is this the right approach ? .

if it is how can i convert the following sql statements into perl scripting syntax

CREATE TEMPORARY TABLE tmp_import (`record_number` INT(10) NOT NULL PRIMARY KEY , 
id_number VARCHAR(50), `first name` VARCHAR(255) , `last name` VARCHAR(255) ,
 msisdn VARCHAR(50) , network VARCHAR(20) , points INT ,
`card number` VARCHAR(100) , gender VARCHAR(1));

LOAD DATA LOCAL INFILE 'path/to/dsv' 
INTO TABLE tmp_import 
FIELDS TERMINATED BY '|' 
LINES TERMINATED BY '\n';

DELETE FROM tmp_import WHERE record_number = 0;

-- select * from tmp_import;

INSERT INTO tUSER (id_number ,first_names,last_name)
SELECT id_number, `first name`, `last name`
FROM   tmp_import;

INSERT INTO tTYPES (`type` ,cellphone , description)
SELECT network, `msisdn`, `gender`
FROM   tmp_import;

and how can i grab each id (PK) from both tables after inserting , and store them into an associative array to insert them later to the many to many bridge table , this could be totally off your suggestions are welcomed .