Visit Our UNIX and Linux User Community


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


 
Thread Tools Search this Thread
Top Forums UNIX for Beginners Questions & Answers Using bash script : How to Import data from a dsv file into multiple tables in mysql
# 1  
Old 02-20-2019
Using bash script : How to Import data from a dsv file into multiple tables in mysql

HI

I have a dsv file that looks like:

Code:
<<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 :
Code:
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  
Old 02-20-2019
Do you have any experience with Perl?
# 3  
Old 02-20-2019
Quote:
Originally Posted by steveo314
Do you have any experience with Perl?
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  
Old 02-20-2019
Quote:
Originally Posted by tera
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 .
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  
Old 02-21-2019
Quote:
Originally Posted by steveo314
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.
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
Code:
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 .

Previous Thread | Next Thread
Test Your Knowledge in Computers #481
Difficulty: Medium
Generally speaking, programs do not start out as source code.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Shell script automation using cron which query's MySQL Tables

What I have: I have a input.sh (script which basically connect to mysql-db and query's multiple tables to write back the output to output1.out file in a directory) note: I need to pass an integer (unique_id = anything b/w 1- 1000) next to the script everytime I run the script which generates... (3 Replies)
Discussion started by: kkpand
3 Replies

2. Shell Programming and Scripting

In PErl script: need to read the data one file and generate multiple files based on the data

We have the data looks like below in a log file. I want to generat files based on the string between two hash(#) symbol like below Source: #ext1#test1.tale2 drop #ext1#test11.tale21 drop #ext1#test123.tale21 drop #ext2#test1.tale21 drop #ext2#test12.tale21 drop #ext3#test11.tale21 drop... (5 Replies)
Discussion started by: Sanjeev G
5 Replies

3. Shell Programming and Scripting

Append data by looking up 2 tables for multiple files

I want to lookup values from two different tables based on common columns and append. The trick is the column to be looked up is not fixed and varies , so it has to be detected from the header. How can I achieve this at once, for multiple data files, but lookup tables fixed. The two lookup... (5 Replies)
Discussion started by: ritakadm
5 Replies

4. Shell Programming and Scripting

Bash script with python slicing on multiple data files

I have 2 files generated in linux that has common output and were produced across multiple hosts with the same setup/configs. These files do some simple reporting on resource allocation and user sessions. So, essentially, say, 10 hosts, with the same (2) system reporting in the files, so a... (0 Replies)
Discussion started by: jdubbz
0 Replies

5. Web Development

mysql query for multiple columns from multiple tables in a DB

Say I have two tables like below.. status HId sName dName StartTime EndTime 1 E E 9:10 10:10 2 E F 9:15 10:15 3 G H 9:17 10:00 logic Id devName capacity free Line 1 E 123 34 1 2 E 345 ... (3 Replies)
Discussion started by: ilan
3 Replies

6. Shell Programming and Scripting

Shell snip to import CSV data into BASH array

I have been trying to write a simple snip of bash shell code to import from 1 to 100 records into a BASH array. I have a CSV file that is structured like: record1,item1,item2,item3,item4,etc.,etc. .... (<= 100 items) record2,item1,item2,item3,item4,etc.,etc. .... (<= 100 items)... (5 Replies)
Discussion started by: dstrout
5 Replies

7. Shell Programming and Scripting

Reading data from multiple tables from Oracle DB

Hi , I want to read the data from 9 tables in oracle DB into 9 different files in the same connection instance (session). I am able to get data from one table to one file with below code : X=`sqlplus -s user/pwd@DB <<eof select col1 from table1; EXIT; eof` echo $X>myfile Can anyone... (2 Replies)
Discussion started by: net
2 Replies

8. Shell Programming and Scripting

Data Import perl script

Hi, I have a requirement for creating a Perl Script which will perform Data Import process in an automated way and I am elaborating herewith : Section 1 ) - use the following command line format : "./import.pl -h hostname -p port -f datafile.txt" Section 2) datafile.txt will... (3 Replies)
Discussion started by: scott_apc
3 Replies

9. Shell Programming and Scripting

Converting tables of row data into columns of tables

I am trying to transpose tables listed in the format into format. Any help would be greatly appreciated. Input: test_data_1 1 2 90% 4 3 91% 5 4 90% 6 5 90% 9 6 90% test_data_2 3 5 92% 5 4 92% 7 3 93% 9 2 92% 1 1 92% ... Output:... (7 Replies)
Discussion started by: justthisguy
7 Replies

10. Shell Programming and Scripting

Import data from compressed file

HI I need to import data from a file which is in comressed format but system doesn't have enough space to uncompress file Is there any way so that i can do import from compressed file. (4 Replies)
Discussion started by: ap_gore79
4 Replies

Featured Tech Videos