Solved

Inserting file details into MySQL in CentOS

Posted on 2013-11-09
4
312 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 78

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

Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

Question has a verified solution.

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

Suggested Solutions

The purpose of this article is to demonstrate how we can use conditional statements using Python.
Creating and Managing Databases with phpMyAdmin in cPanel.
The viewer will learn how to dynamically set the form action using jQuery.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

739 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