Solved

Bash script to insert text into MYSQL

Posted on 2011-02-24
3
741 Views
Last Modified: 2012-05-11
Hi,

I have a table with the following fields:

PDI Number, Category, Reference, Description, Status, Example, Script Version, UNIX Checklist Page.

Now the list is going will have several PDI's and each one will have all of the fields above.

I look forward hearing from you.

Regards,

Michael
PDI Number: EXA000260
Category: FAT II
Reference: LINUX 3.1
Description: A shared account is not documented by the IAO.
Status: Open

For example:
ZEN000260: The following accounts require justifying documentation from the IAO.
ACCT     LOGGED IN FROM
-------   --------------
sysadmin Fri
sysadmin Mon
sysadmin Sat
sysadmin Sun
sysadmin Thu
sysadmin Tue
sysadmin Wed

Script Version: Jan2009
UNIX Checklist Page: 30

Open in new window

0
Comment
Question by:dpoper1
  • 2
3 Comments
 
LVL 4

Expert Comment

by:MarioAlcaide
ID: 34977416
Hello,

You can use the command LOAD DATA for that purpose. For example: mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;

http://dev.mysql.com/doc/refman/5.0/en/loading-tables.html
0
 
LVL 4

Expert Comment

by:MarioAlcaide
ID: 34978156
Was this helpful for you?
0
 
LVL 24

Accepted Solution

by:
mankowitz earned 500 total points
ID: 34980184
There's not a super-easy way to do this, but you would probably start with a perl script

makesql.pl
while (<>)
{
m/PDI Number: ([^\n]*)\nCategory: ([^\n]*)\nReference: ([^\n]*)\nDescription: ([^\n]*)\nStatus: ([^\n]*)\n\nFor example:\s*(.*?)\nScript Version: ([^\n]*)\nUNIX Checklist Page: ([^\n]*)/s;
print "INSERT table (pdi,category,reference,description,status,example,script_version,page) VALUES ('$1', '$2', '$3', '$4', '$5', '$6', '$7', '$8');\n";
}

Then you could do something like

perl makesql.pl < textfile.txt |mysql -uUSER -pPASS DATABASE
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

Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Creating and Managing Databases with phpMyAdmin in cPanel.
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

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

11 Experts available now in Live!

Get 1:1 Help Now