CSV to complex HTML table: variable rowspan, depending on duplicates. 1

[mlg4035]

Good morning all,

I'm having a rough time handling the output/display of CSV data into HTML tables. The way the CSV file is formatted, several fields can be duplicated over 2 or more rows to indicate that the value should span over that many rows in the HTML table. So,

Cow, Dog, Lion, Tiger, Eagle
Cow, Dog, Lion, Tiger, Eagle
Cow, Dog, Cat, Tiger, Crow
Cow, Dog, Cat, Tiger, Crow
Air, Water, Winter, Earth, Fall
Air, Water, Winter, Earth, Fall
Air, Diamond, Fire, Earth, Wind
Air, Diamond, Fire, Earth, Wind

would indicate a five-column/two-row table, that splits into three rows, and then four rows after the Dog column. Cow, Dog, and Tiger would represent cells with rowspan='4', while Lion, Eagle, Cat, and Crow represent cells with rowspan='2'. The next HTML table then would start at the Air row.

I have not, for the life of me, been able to get this data formatted into neat tables. The customer doesn't want to install any modules that might be of help. And the data isn't consistent/uniform or predictable, so my strategy thus far has been to embed HTML in my CGI and try to parse one row of data at a time into an HTML template.

Can anyone recommend a better way of doing this? I'd like to simply count the number of times the same value appears in each column ("$AoA[$row][0], $AoA[$row][1], $AoA[$row][2], etc...) but I can't figure out how to do this(I've never been any good with hashes or arrays of arrays...).

How can I output HTML with dynamic rowspans, Please help!!!



'Well, it's one louder, isn't it?...
What we do is, if we need that extra push over the cliff, you know what we do?...Eleven. Exactly. One louder.'

 

[MikeLacey]

Ok....

Sorry to sound stupid here but....

*how* do you want the output from that data formatted exactly? Mike

Want to get great answers to your Tek-Tips questions? Have a look at faq219-2884

 

[mlg4035]

Not stupid at all, sorry I was very unclear. I misspoke so let me rephrase, please. Each HTML table will have a title cell, the value taken from the first column of the CSV file.

Please take a look at the following URL for actual data. It's for a Japanese company putting school exam results on the internet so it's in Japanese(Shift-JIS). This html represents one school's results($AoA[$row][0] = School name).

http://www.natwo.com/~michael/CSVdata/csvdata.html

Ignoring the column title cells, wherever you see spanned rows, the rowspan value `4` or `2` indicates that this data was repeated in the CSV file. I don't have a sample of the data for you right now, but I'll get you all one soon.

Thanks for any help, in advance.

'Well, it's one louder, isn't it?...
What we do is, if we need that extra push over the cliff, you know what we do?...Eleven. Exactly. One louder.'

 

[icrf]

Just to make sure I got this right, your example should create the following (but with other data instead of numbers at right):

<table border=1>
<tr>
<td rowspan=4>Cow</td>
<td rowspan=4>Dog</td>
<td rowspan=2>Lion</td>
<td rowspan=4>Tiger</td>
<td rowspan=2>Eagle</td>
<td>1</td>
</tr>

<tr>
<td>2</td>
</tr>

<tr>
<td rowspan=2>Cat</td>
<td rowspan=2>Crow</td>
<td>3</td>
</tr>

<tr>
<td>4</td>
</tr>

<tr>
<td rowspan=4>Air</td>
<td rowspan=2>Water</td>
<td rowspan=2>Winter</td>
<td rowspan=4>Earth</td>
<td rowspan=2>Fall</td>
<td>5</td>
</tr>

<tr>
<td>6</td>
</tr>

<tr>
<td rowspan=2>Diamond</td>
<td rowspan=2>Fire</td>
<td rowspan=2>Wind</td>
<td>7</td>
</tr>

<tr>
<td>8</td>
</tr>

</table>

It's been a couple weeks since I've played with perl, I need a little project, and this sounds like something that would work well. ----------------------------------------------------------------------------------
...but I'm just a C man trying to see the light

 

[icrf]

Try this. It puts the output in $out and then throws it in a file in the end. I imagine you'd want to read in @CSV from a file, too, so that should be a fairly simple change from this. It's just the way I first throught to do this, as always, there's probably a simpler way. Lemme know what you think.

use strict;

# @CSV stores each line in the comma separated values file
# as an element in the array
my @CSV = (
'Cow, Dog,     Lion,   Tiger, Eagle',
'Cow, Dog,     Lion,   Tiger, Eagle',
'Cow, Dog,     Cat,    Tiger, Crow',
'Cow, Dog,     Cat,    Tiger, Crow',
'Air, Water,   Winter, Earth, Fall',
'Air, Water,   Winter, Earth, Fall',
'Air, Diamond, Fire,   Earth, Wind',
'Air, Diamond, Fire,   Earth, Wind',
);

# a 2D array to store the CSV table
my @data;

# removes all the white space (or not, whatever)
# splits the values on the comma and makes it a row in @data
foreach my $entry (@CSV)
{
	$entry =~ s/ //g;
	push @data, [split /,/,$entry];
}

# store the dimentions of the table
my $width = scalar(@{$data[0]});
my $height = scalar(@data);

# an array the size of the width of the table storing the remaining 
# row span for each column
my @span;

# start the current row
my $currentRow = 0;

# start the html table output
my $out = &quot;<table border=1>\n<tr>\n&quot;;

# start off by storing the row span of each cell in @span and
# outputting the table html and data for the first row
for(my $i=0;$i<$width;$i++)
{
	$span[$i] = rowspan($currentRow,$i);
	$out .= &quot;<td rowspan=$span[$i]>$data[$currentRow][$i]</td>\n&quot;;
	$span[$i]--;
}

# end the first row and increment the current row
$out .= &quot;</tr>\n\n&quot;;
$currentRow++;

# loop until we've checked every row in the CSV listing
while($currentRow < $height)
{
	# start the html for the row
	$out .= &quot;<tr>\n&quot;;
	
	#for each cell in the current row
	for(my $i=0;$i<$width;$i++)
	{
		# check if the row span for the current cell has ended
		unless($span[$i] > 0)
		{
			# store new row span and output the table data
			$span[$i] = rowspan($currentRow,$i);
			$out .= &quot;<td rowspan=$span[$i]>$data[$currentRow][$i]</td>\n&quot;;
		}
		# decrement the remaining span for the cell
		$span[$i]--;
	}
	# end the html for the current row
	$out .= &quot;</tr>\n\n&quot;;
	$currentRow++;
}
# end the html for the table and deal with the output
$out .= &quot;</table>&quot;;
open FILE, &quot;>test.html&quot;;
print FILE $out;
close FILE;

sub rowspan
{
	my $row = shift;
	my $col = shift;
	# store the table cell to span
	my $value = $data[$row][$col];
	my $count = 0;
	
	# loop while the end of the table hasn't been reached
	# and the original cell value is still spanning
	while($row < $height && $data[$row][$col] eq $value)
	{
		$row++; $count++;
	}
	return $count;
}

----------------------------------------------------------------------------------
...but I'm just a C man trying to see the light

 

[mlg4035]

icrf,

Your table looks right on the money. I haven't had a chance to test your code, but if it creates the table you drew up in your first reply, I think it will work!

I have to apologize to everyone for the vagueness of my posts in this thread. I was in a rush to get out the door to get to an appointment. But I'll be up all night working on this (it's 16:30, 12/23 Japan time here now and I'm a slow learner!). I'll let you know how it goes and shower you with stars if I can get it to work.

Any and all other suggestions are welcome also!

Thank you in advance!
'Well, it's one louder, isn't it?...
What we do is, if we need that extra push over the cliff, you know what we do?...Eleven. Exactly. One louder.'

 

[mlg4035]

icrf,

This can work! However, one thing: I don't need to display columns 1 through 3, is there an easy way to weed those out? Other than that I can make any other necessary adjustments. Thank you so much for your help! 'Well, it's one louder, isn't it?...
What we do is, if we need that extra push over the cliff, you know what we do?...Eleven. Exactly. One louder.'

 

[icrf]

The places where it loops through each cell in a row, such as:

for(my $i=0;$i<$width;$i++)

goes from column zero through column width-1. If you want to skip the first three(0,1,2), it could probably be changed to:

for(my $i=3;$i<$width;$i++)

I think that for loop is in there twice. ----------------------------------------------------------------------------------
...but I'm just a C man trying to see the light

 

[mlg4035]

This works beautifully for the whole file! One question for anyone who has the time: how can I modify this to process the file in groups of lines at a time instead of the whole file?

I need a table for every unique value in field on of the data file...? I can write out the first x number of rows, but then I don't understand how to go back and start the while loop again starting at the row after we leave the loop each time...?

I know these are bonehead questions but I'm pretty new to all this. Thanks to anyone who can push me in the right direction. 'Well, it's one louder, isn't it?...
What we do is, if we need that extra push over the cliff, you know what we do?...Eleven. Exactly. One louder.'