Send Data to MySQL Table Columns

Shell Programming and Scripting
1

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.

#!/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

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 ?

1 Like
3

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

You are better off to use PHP for this kind of processing.

See PHP fgetcsv

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):

    $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);