i have some multiple value fields (with values separated by ~~); otherwise the delimiter is the default pipe |

i want to create a new database by copying each record to the new database. i want to read each record and if it contains multiple values, i want to create a separate record in the new database for each record. each record in the new database should have only one value.

simplified example:
fields: ID name color city
record 1 :
1|john|red~~white~~blue|atlanta

i want the new database to contain the following 3 records:
1|john|red|atlanta
2|john|white|atlanta
3|john|blue|atlana

i can't figure out how to break apart the multi value and create a separate record for each value. can anyone help?

after more thought, i would be happy with a different solution: instead of copying the records to a new file, can they be sorted so that they look like 3 separate records?

Is this a "one time" thing? If so, here is what I was thinking...


Doesn't quite work, and I'm not sure if you can do a loop within a loop (?) - but I gotta get back to work and can't play with it anymore (and I suck at arrays and loops )
You may be able to glean some useful ideas from studying db.cgi and see how Alex handled the ~~ within the records

.

thanks!!! i'll try it now!

almost working. i even have it replacing the value of the multi-valued field properly and updating the record counter. problem is, for some reason it is dropping the first field of each record and adding a field at the end.

sub copy_records {
my ($line, @values, %newline, $tmp, @newvalues, $newvalue, $counter);
my $succstr = "see if it worked";

open(NEWDBFILE, ">$db_archive_name");
open (DB, "<$db_file_name") or &cgierr("error in archive_records. unable to open db file: $db_file_name.\nReason: $!");
$counter = 1;
LINE: while (<DB>) {
(/^#/) and next LINE; # Skip comment Lines.
(/^\s*$/) and next LINE; # Skip blank lines.
$line = $_;
chomp ($line); # Remove trailing new line.
# $tmp = $line;
@values = &split_decode($line);

@newvalues = split (/\|/,$values[20]);
foreach $newvalue (@newvalues) {
$values[1] = $counter;
$values[20] = $newvalue;
%newline = &array_to_hash(@values);
print NEWDBFILE &join_encode(%newline);
$counter++;
}
}
close DB;
close NEWDBFILE;
&html_archive_success($succstr);
}

it's working now. i tried to use array_to_hash, etc to make it look more like dbman code but i've wasted too much time. so i'll just use what works:

sub copy_records {

my ($line, @values, %newline, $tmp, @newvalues, $newvalue, $counter);
my $succstr = "see if it worked";

open(NEWDBFILE, ">$db_archive_name");
open (DB, "<$db_file_name") or &cgierr("error in archive_records. unable to open db file: $db_file_name.\nReason:
$!");
$counter = 1;
LINE: while (<DB>) {
(/^#/) and next LINE; # Skip comment Lines.
(/^\s*$/) and next LINE; # Skip blank lines.
$line = $_;
chomp ($line); # Remove trailing new line.
@values = &split_decode($line);

@newvalues = split (/\|/,$values[20]);
foreach $newvalue (@newvalues) {
if (!$values[0]) { $values[0] = 'zzz'; }
$values[1] = $counter;
$values[20] = $newvalue;
$values[21] =~ s/\Q$db_delim\E/~~/og;
%newline = &array_to_hash(@values);
$tmp = join("|",@values);
print NEWDBFILE $tmp;
print NEWDBFILE "\n";
$counter++;
}
}
close DB;
close NEWDBFILE;
&html_archive_success($succstr);
}