Inserting file details into MySQL in CentOS

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.
MuhajreenAsked:
Who is Participating?
 
arnoldConnect With a Mentor Commented:
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
 
GaryCommented:
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
 
MuhajreenAuthor Commented:
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
 
MuhajreenAuthor Commented:
Thank you arnold. I have just tried your solution and it worked perfectly.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.