Parse text file for mysqlimport

[wali2003]

Hi,

I'm totally new to perl and have a question:

I would like to import a textfile into a mysql db, using mysqlimport. The problem I have is the badly formated text and I'm wondering if perl could help me to parse the file and prepare it for mysqlimport.
The file contains x lines of information, whereas every single line represents a row in the db.
Every line -can- contain up to 42 different tags and for every tag there is exactly one value.
The problem is the -can-. If every line would always contain -all- tags in always the same order then I could easily import it into mysql.
Given conditions:
- Every of the 42 tags is unique (mi, mo, ti...)
- A tag only can exist once in a line
- A tag always has an attributed value -> (mi=12345)
- tag&values are terminated by a '/'

Here's an example of one single line:
(12)pc=+12345678987/(6)mi=281445/ti=0/po=23/no=0/(12)cg=+12345678987/(5)ni=relay/ty=12/it=5/et=1/(12)de=+98765432198/sz=98/(25)id=1050566425245192168207130/ds
=19700101T000030Z/dt=20030417T080025Z/st=20030417T075955Z/ex=20030424T075955Z/mp=1/mc=1/(10)mo=text/plain/(6)gi=281445/nn=1/(36)in=My System/(10)rt=MRSRrecord/fw=0/(1)ac=0/pm=2/ed=6/(3)oi=MM1/(3)ii=MM1/

The values in () can be ignored. They do not have to be parsed.
The result after parsing of a line should be like:
+12345678987,281445,0,23,0,+12345678987,relay,12,5,1,+98765432198, 98,1050566425245192168207130...,MM1,MM1

Since I need to match fieldnames from columns in mysql the missing tags should be inserted as well containing the value "0".

So the perl script should be able to check every single line, parse the line and add the missing tags to the formatted output.

Can anybody follow me? And is this possible to solve with perl?
/Wali

 

[smackdab]

Perl can do this, but it is a bit of work if you don't know anything about Perl yet...Get the Perl Cookbook and it will help with everything ;-)

Here is some untested code that will help you start:

use strict; use warnings;
my %data;
$_='(12)pc=+12345678987/(6)mi=281445/ti=0/po=23/no=0/(12)cg=+12345678987/(5)ni=relay/ty=12/it=5/et=1/(12)de=+98765432198/sz=98/(25)id=1050566425245192168207130/ds=19700101T000030Z/dt=20030417T080025Z/st=20030417T075955Z/ex=20030424T075955Z/mp=1/mc=1/(10)mo=text/plain/(6)gi=281445/nn=1/(36)in=My System/(10)rt=MRSRrecord/fw=0/(1)ac=0/pm=2/ed=6/(3)oi=MM1/(3)ii=MM1/';
my @each = split(/\//);
foreach (@each)
{
my ($key, $val) = $_ =~ m/(\w+)=([\+\d]+)/;
$data{$key} = $val;
}

while (my ($key, $val) = each (%data))
{
print "$key=$val\n";
}

 

[wali2003]

Hi,

thanks for you answer! I'm currently starting to learn perl...
I'm trying to understand what would be the best way to reformat the output (as from your script) into a comma seperated output. Expecially, the script has to place values from a tag (i.e. mi=463748) at the right position in a comma seperate line. Since the tag names can change I think the best would be to use a template (giving the correct order of the calues) that can be easily modified:

id,mi,po,cl,vs,...

So the script should check the template.

To be honest I'm impressed by anybody (like you) who is capable to program a script like that one. For me it'll take ages to get to that level... But I'm trying hard ;o)

cheers
Wali