Re^2: Sequential data read in MySQL/Perl

[justin423]

the table is a temp table that has the fields that are common across all of the rows coming in. (REC1 to RECZ above). the common identifier for REC1 to RECZ only appears on REC1, so i want to update the rec2, rec3, rec4 rows with the value in DATAX, which only appears in REC1. I was also trying to show that the value needs to be reset when the program encounters a RECZ, so that a new value is applied for the next group. How do I read the value from the database into a variable using DBI?

[Marshall]

I am also confused about what you are doing. If these 9 values "go together" somehow, then perhaps a table like what I show below is what you need? Each row is a "group" and each column contains "like data". Without some descriptive story re: post from Grandfather, its hard to say what you really need (which may be different than what you are asking for).

1  DATAX DATA2 DATA3 DATA4 DATA5 DATA6 DATA7 DATA8 
2  DATAY DATA2 DATA3 DATA4 DATA5 DATA6 DATA7 DATA8

[justin423]

That is just about what I am asking for. A script to convert each group I encounter on the source file to be stored in a single row like you have done.

[Marshall]

Ok, does this work for you? Row number would be an auto increment field, so doesn't need to be a field in the SQL INSERT statement.

#!/usr/bin/perl
use strict;
use warnings;

my @row;

# SQL prepare statement goes here.

while (my $line = <DATA>)
{
    next if $line =~ /^\s*$/; #skip blank lines
  
    if ($line =~ /^\s*RECZ/)  #end of record
    {
       print "@row\n";  #would be DB row insert
       @row = ();       #start new row
    }
    else
    {
       my @data = (split(' ',$line))[1,2];
       push @row,@data;
    }
}

=prints
DATAX DATA2 DATA3 DATA4 DATA5 DATA6 DATA7 DATA8
DATAY DATA2 DATA3 DATA4 DATA5 DATA6 DATA7 DATA8
=cut



__DATA__

REC1 DATAX DATA2 1
REC2 DATA3 DATA4 2
REC3 DATA5 DATA6 3
REC4 DATA7 DATA8 4
RECZ 5
REC1 DATAY DATA2 6
REC2 DATA3 DATA4 7
REC3 DATA5 DATA6 8
REC4 DATA7 DATA8 9
RECZ 10

Update: be careful with that submit button (labeled "stumbit") You made a duplicate post that just causes "noise" that a Janitor has to clean up.

[justin423]

That is just about what I am asking for. A script to convert each group I encounter on the source file to be stored in a single row like you have done.