Send Data to MySQL Table Columns


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Send Data to MySQL Table Columns
# 1  
Old 02-07-2018
Send Data to MySQL Table Columns

I have two scripts, each script reads an individual data file and copies specific lines of data and sends to MySQL table. Only difference is, each script sends data to a separate column on the same DB. I want to use one script to populate DB table and have data look horizontal, with no overlapping. So far my database looks like attached image, named sendtodb.png.

I decided to combine two scripts into one (master) script. When I run master script, it only populates data to one column on the DB. I want to send data to two specific columns.

I know exact problem, my script can't distinguish between what data I want to send to what column. I'm using one CSV file that has all the data I need. When I run master script, it reads from CSV file and copies specific lines I need, and sends to DB.

I've been playing around with awk command in hopes it can filter out lines of data I don't need, but so far my grep command copies exact lines I want to send to the DB columns.

My script is below.
Code:
#!/bin/bash

a="grep -H -R -n "STRING" /usr/local/bin/Production/me.csv"
a2="grep -H -R -n "OID:" /usr/local/bin/Production/me.csv"

# Condition will return true only if there is a file in directory
# Condition Not true, script will do nothing and script ends 
if [ `$a | grep -c "STRING"` -gt 0 ]
then

# Going to copy entire contents after STRING:
grep -oP 'STRING:\K.*' me.csv > fixme.csv

# Command to remove "\\" from SNMP output
# Sed command only works when running hrstoragedesc script
sed 's/\\//g' /usr/local/bin/Production/fixme.csv > sendtodb.csv

fi

# Current date will display
fetcha=$(date +"%Y-%m-%d")

if [ `$a2 | grep -c "OID:"` -gt 0 ]
then

grep -oP 'OID:\K.*' me.csv >> sendtodb.csv

fi

IFS=,
while read hrstoragedescr hrstoragetype
*** do
**** echo "INSERT INTO info (server,hrstoragedescr,hrstoragetype,date) VALUES ('EXCHANGE','$hrstoragedescr','$hrstoragetype','$fetcha');"
* 
done < /usr/local/bin/Production/sendtodb.csv | mysql -u root -p SNMP;

My goal is to successfully send data to MySQL table and have the data look horizontal row by row. I also attached CSV file, where script reads from and copies data to sent to the DB.
# 2  
Old 02-07-2018
1) The CSV.txt you've posted is in fact your sendtodb.csv file, is that correct ?

2) What is the content of your me.csv file ?
This User Gave Thanks to ctsgnb For This Post:
# 3  
Old 02-09-2018
Thanks ctsgnb, but I used a shorter command to export data to MySQL DB. I decided to use mysqlimport command and it worked. Thanks for taking the time to help.
# 4  
Old 02-10-2018
You are better off to use PHP for this kind of processing.

See PHP fgetcsv

Quote:
fgetcsv - Gets line from file pointer and parse for CSV fields
Then, you use one of the many PHP mysql commands, depending on the version of PHP use are using.

This type of approach is much easier and far superior than using the standard unix / linux shells like bash, sh, ksh, etc.

PHP example (just one example of many):

Code:
    $handle = fopen($_FILES['filename']['tmp_name'], "r");

    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        $import="INSERT into importing(text,number)values('$data[0]','$data[1]')";

        mysql_query($import) or die(mysql_error());
    }

    fclose($handle);

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Programming

MYSQL merge csv data with exisiting table

I have a MYSQL table with demographic data. The addresses contained in the table were entered manually before the advent of online postcode lookup software and, hence, there are a lot of errors when using automated online mailing checking software. I plan to export the data as csv file for a 3rd... (1 Reply)
Discussion started by: barrydocks
1 Replies

2. Shell Programming and Scripting

I want query output to send on mail using table tag and output should be in table

#! /bin/ksh #] && . ./.profile 2>/dev/null if test -f '.profile'; then . ./.profile; fi; #. .profile LOG_DIR=/app/rpx/jobs/scripts/just/logs sendEmail() { pzCType="$1"; pzTitle="$2"; pzMsg="$3"; pzFrom="$4"; pzTo="$5"; pzFiles="$6"; pzReplyTo="$7" ( ... (21 Replies)
Discussion started by: ankit.mca.aaidu
21 Replies

3. Shell Programming and Scripting

Shell scripting unable to send the sql query data in table in body of email

I have written a shell script that calls below sql file. It is not sending the query data in table in the body of email. spool table_update.html; SELECT * FROM PROCESS_LOG_STATS where process = 'ActivateSubscription'; spool off; exit; Please use code tags next time for your code and data.... (9 Replies)
Discussion started by: Sharanakumar
9 Replies

4. Shell Programming and Scripting

Take Data From a table and send it through mail

Hi can anyone help me in writing a code for taking data from a table and need to send that data through mail using mail -x command.. (3 Replies)
Discussion started by: ginrkf
3 Replies

5. UNIX and Linux Applications

mysql table disappear

I have set a mysql file to excute everyday morning to generate a html file displayng 2 tables from the database. Sometime they cannot be shown, and it shows the tables are not existed. I have not drop any table, and those 2 tables are not used by any other excution. Anybody know what is happening?... (0 Replies)
Discussion started by: c203040
0 Replies

6. Shell Programming and Scripting

how to print out data from mysql table in perl script

I'm having trouble with this code. if i do .\read.pl -u user it prints out 2010-12-20 12:00:00 host1 <cmd>a 2010-12-20 12:00:01 host1 <cmd> <execute> 2010-12-20 12:00:02 host1 <cmd>b 2010-12-20 12:00:03 host1 <cmd>c however, if i enter .\read.pl -h host1 it should... (3 Replies)
Discussion started by: kpddong
3 Replies

7. Shell Programming and Scripting

Arrange Data in table and send by mail

Everybody, can you tell me how express about this; we have text data file as : parameter1 Parameter2 AA 55 BB 77 . . . . . . We want to draw table for this data as attached then send as body of Email (4 Replies)
Discussion started by: xjklop2009
4 Replies

8. Programming

API C MYSQL vs lock table ???

(sorry for my english) Hi, i have an app that uses MYSQL API C.. i trying do a timeout until the table is locked by an other thread , in the docs of Mysql i can see that MYSQL_OPT_READ_TIMEOUT is not implemented for linux ??.. any body knows a way to do a timeout until the table is locked by... (0 Replies)
Discussion started by: JEscola
0 Replies

9. Shell Programming and Scripting

Help Inserting data in mysql table

Cant understand the error #!/bin/bash temp="" A="" D=$(date +"%Y-%m-%d") H=$(date +"%R") temp=$(wget -q -O - website | grep -o "Temperature:]**" | grep \-E -o "+") mysql -D "weather_wise" -e "INSERT INTO weather (Date, Hour, Degrees) VALUES ($D,$H, $temp)"; my data types for... (11 Replies)
Discussion started by: vadharah
11 Replies

10. Shell Programming and Scripting

MySql: create table error

Hi, iam learning MySql. Iam trieing to create a table in the database "guestbook" at the command line in mysql heres what i type but i get a error mysql>create table guestbook ->( -> name varchar(40) null. -> url varchar(40) null. -> comments ... (3 Replies)
Discussion started by: perleo
3 Replies
Login or Register to Ask a Question

Featured Tech Videos