Inserting file details into MySQL in CentOS

Hello experts,

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


All file names are like this:


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.
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'"

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.
I'm sure you can manage to add the rest of the fields

for file in /var/source/*.gsm
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

Open in new window

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:


The give values above don't present date nor time nor file.
MuhajreenAuthor Commented:
Thank you arnold. I have just tried your solution and it worked perfectly.
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.