Solved

Bash script to insert text into MYSQL

Posted on 2011-02-24
3
745 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

776 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