Solved

Inserting file details into MySQL in CentOS

Posted on 2013-11-09
4
306 Views
Last Modified: 2013-11-10
Hello experts,

I have thousands of files stored in this location in a CentOs server:

/var/source/

All file names are like this:

123_45678_2013_11_29_15_45_30.gsm

This file name represents two numbers (123 and 45678) and a full date (2013/11/29) and a time (15:45:30)

I have a MySQL database named (filedb). A table named (files) has the following columns:

id =====  is a sequential number
source ===== is 123
destination ===== is 45678
date ==== is 11/29/2013
time ==== is 15:45:30
file ==== is the file name 123_45678_2013_11_29_15_45_30.gsm
note ==== is an empty field

I need a script which should insert an entry to the (filedb) database for each file in the /var/source/ directory.

Note that the first two sections (123) and (45678) can vary in length. They may be 3,4,5 or more digits. The only wan to recognise sections is by underscores.

I have already setup a username and a password for accessing the MySQL database.

Would any body write the required script for me?

Thanks in advance.
0
Comment
Question by:Muhajreen
  • 2
4 Comments
 
LVL 58

Expert Comment

by:Gary
ID: 39636253
I'm sure you can manage to add the rest of the fields

for file in /var/source/*.gsm
do
IFS='_' read -a myvar <<< "${file}"
echo "insert into files (id,source,destination) values (null,'${myvar[0]}','${myvar[1]}','${myvar[2]}')"|mysql -u Username -pPassword filedb
done

Open in new window

0
 
LVL 77

Accepted Solution

by:
arnold earned 500 total points
ID: 39636361
ls /var/source | while read a; do
set `echo $a | tr '_' ' ' | sed -e 's/.gsm$//' `
if ( /usr/bin/test "$#" -eq "8" ) ; then
echo "insert into files (source, destiantion, date, time, file) values ('$1','$2','$3/$4/$5','$6:$7:$8','$a'"
fi
done

Open in new window

if the output is what you want add to the end of the above
| mysql -u username -ppassword databasename

Presumably the id table is set to autoincrement.

If you want detection for insert errors using perl might be the way to go.
0
 

Author Comment

by:Muhajreen
ID: 39636625
Thanks to both of you.

For ease of understanding I will try the script of  GaryC123 which came first.

Can anybody please give me the exact ${myvar[?]} values which should give the following values in order:

null
source
destination
date
time
file
note

The give values above don't present date nor time nor file.
0
 

Author Closing Comment

by:Muhajreen
ID: 39636672
Thank you arnold. I have just tried your solution and it worked perfectly.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now