Parse a .txt file for adding to a MySQL DB

dru145 has asked for the wisdom of the Perl Monks concerning the following question:

Monks,

I need some help/suggestions. I have a .txt file that looks like this:


  + 192.168.89.1   acmeorp.acme.com
        |___    21  File Transfer Protocol [Control]
* + 192.168.31.3   ftp.acme.com
        |___    21  File Transfer Protocol [Control]
                |___ 220 ftp.acme.com FTP server ready...
* + 192.168.33.19   acmeftp.acme.com
        |___    21  File Transfer Protocol [Control]
                |___ 220-acme Secure FTP Server..    WarFTPd 1.71.02 (
+Feb 14 2000) Ready..    (C)opyright 1996 - 2000 by Jarle (jgaa) Aase 
+- all righ
* + 192.168.29.21   orcweb.acme.com
        |___    21  File Transfer Protocol [Control]
                |___ 220 sandbox FTP server (Version 1.1.214.7 Thu Aug
+ 10 09:57:38 GMT 2000) ready...
* + 192.168.11.22   commerce.acme.com
        |___    21  File Transfer Protocol [Control]
                |___ 220 commerce Microsoft FTP Service (Version 4.0).
+..
* + 192.168.19.24   webapp.acme.com
        |___    21  File Transfer Protocol [Control]
                |___ 220 WEBAPP1PRI Microsoft FTP Service (Version 5.0
+)...
[download]

I want to be able to upload this file (via CGI.pm), parse it, and add it to a MySQL database (via DBI) which looks like this:

+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| ip          | varchar(25) | YES  |     | NULL    |       |
| svr_name    | varchar(50) | YES  |     | NULL    |       |
| port_num    | varchar(6)  | YES  |     | NULL    |       |
| service     | varchar(25) | YES  |     | NULL    |       |
| description | varchar(50) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
[download]

I can handle the CGI, DBI, and Mysql portions, but I'm looking for help on how to parse this file for adding it to the database. Does Perl have a way to convert a .txt file to a .csv file? I looked at Text::CSV, but that only seems to parse a csv file, not create one.

TIA,

Thanks,
Dru
Another satisfied monk.

Comment on Parse a .txt file for adding to a MySQL DB Select or Download Code

Replies are listed 'Best First'.
Re: Parse a .txt file for adding to a MySQL DB by talexb (Chancellor) on Mar 12, 2002 at 17:35 UTC
I'd say you want to write a script to parse your input to produce an input file for the MySQL command `load data local infile [filename] into [tablename]`. Parsing the data looks simple enough .. the first line has an IP address and a server name, the second has a port number and a service, and the third (if it exists) has the description. Piece of cake; as you get enough info for each record, dump it out using tabs to separate fields (fewer issues than using commas). Check the output file for correctness then load it into the database. --t. alex "There was supposed to be an earth-shattering kaboom!" --Marvin the Martian	[reply] [d/l]

Replies are listed 'Best First'.

Re: Parse a .txt file for adding to a MySQL DB
by talexb (Chancellor) on Mar 12, 2002 at 17:35 UTC

load data local infile [filename] into [tablename]

Parsing the data looks simple enough .. the first line has an IP address and a server name, the second has a port number and a service, and the third (if it exists) has the description. Piece of cake; as you get enough info for each record, dump it out using tabs to separate fields (fewer issues than using commas). Check the output file for correctness then load it into the database.

--t. alex

"There was supposed to be an earth-shattering kaboom!" --Marvin the Martian

[reply]
[d/l]