Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Inserting file details into MySQL in CentOS

Posted on 2013-11-09
4
Medium Priority
?
325 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 80

Accepted Solution

by:
arnold earned 2000 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

The Windows functions GetTickCount and timeGetTime retrieve the number of milliseconds since the system was started. However, the value is stored in a DWORD, which means that it wraps around to zero every 49.7 days. This article shows how to solve t…
In this article, we’ll look at how to deploy ProxySQL.
The viewer will learn how to dynamically set the form action using jQuery.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Suggested Courses

927 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