Page 5 - Tab delimited parse

use strict;
use warnings;
use Data::Dumper;
use CGI qw/:standard *table/;
use CGI::Carp qw/fatalsToBrowser/;
use DBI;

$db="";
$host="";
$port="";
$userid="";
$passwd="";
$connectionInfo="DBI:mysql:database=$db;$host:$port";

my $dbh = DBI->connect($connectionInfo,$userid,$passwd)
						or die("Cannot connect: $DBI::errstr\n");

print header, start_html, start_table;

my $file = 'parseMore.txt';

{
	local $/ = "\r\n"; #not for you
	open my $fh, "<", $file or die "Can't open $file: $!";
	my $header = <$fh>;
	chomp $header;
	my @head = split /\t/, $header;
	
	while (<$fh>) {
		chomp;
		my %row;
		@row{@head} = split /\t/;
		$row{'column3'} =~ s/"//g;

		print start_table;
		my @repeat = split /\n/, $row{'column3'};		
		foreach (@repeat) {
			my @three = split /,/, $_, 2;
			my @data = map {$row{$_} || undef} qw/column1 column2 column4 column5 column6 column7 column8 column9 column10/;
			@data = (@data, @three);
			print Tr(td(@data)) if $_ !~ /^\s*$/;
			#$sth->execute(@data);
		}
		print end_table;
	}
}

print end_html;

#!/usr/bin/perl
use strict;
use warnings;
use Data::Dumper;
use CGI qw/:standard *table/;
use CGI::Carp qw/fatalsToBrowser/;

=pod
	Print a standard html header.

	If you want to use Data::Dumper to print data into the browswer, 
	then switch to text mode:

	print header('text/text');
=cut

print header, start_html;

my $file = 'tsv_parse.tsv';

{
=pod
	Tell perl to expect Windows line endings
	Necessary because perl on the server is expecting Unix line endings
	
	The special variable $/ is set to the OS line ending by default
	If you override it, you need to do it with a local scope so you 
	don't ruin things for others.
	
	That's why this section appears within the indented bracket;
	it provides a scope for the $/
	
	Outside of the blocks, the value will revert to the default automatically
=cut

	local $/ = "\r\n";
	
	# open the file
	open my $fh, "<", $file or die "Can't open $file: $!";
	
=pod
	The first line of your file is a header
	I just grab it here to use as field definitions later.
	You wouldn't want to insert this line anyway
=cut
	my $header = <$fh>;
	chomp $header;
	# split the header row into individual names
	my @head = split /\t/, $header;
	
	while (<$fh>) {
=pod
	Remove the line ending with chomp;
	Because you changed $/ to Windows mode
	the chomp command will remove \r\n from the line end
	rather than just the normal \n it would have otherwise
	
	That's not all $/ did for us though.
	Without it, the <$fh> command would have stopped reading the line
	in column3, when it came to a \n character
=cut
		chomp;
=pod
	Define a hash
	Then use a hash slice to automatically fill it in
	the keys to the hash will be the column names from the header row
	and the values will be assigned to the correct positions
	
	print Dumper \%row to see
=cut
		my %row;
		@row{@head} = split /\t/;
		$row{'column3'} =~ s/"//g;
=pod
	When inserting into a database, there will be no need to print to a table obviously
	This is just for your enjoyment
=cut
		print start_table;
=pod
	We want to split column3 (if it's called column3
	Otherwise change this name
=cut
		my @repeat = split /\n/, $row{'column3'};
=pod
	if there are no newlines, the split will move the only value it sees
	into $repeat[0], so the array will still contain a single value
	and the next section will proceed for a single interation
=cut
		foreach (@repeat) {
=pod
	The default variable $_ contains values like: VISHAY BC COMPONENTS, 2222036 Rule Based Upgrades
	We will split them on space
	
	One thing not tested here is that there are actually two values produced
	Because of the limit, there will be no more than two, but it's possible you 
	could only have one
=cut
			my @three = split /,/, $_, 2;
=pod
	The row hash is not in a predictable order
	You have to put the values in a set order for the insert
	The map is an easy way.  It will map out the columns in the order in which they are named.
	Also, you only get the values you want.  I skipped column3 because we will add it manually.
=cut
			my @data = map {$row{$_} || undef} qw/column1 column2 column4 column5 column6 column7 column8 column9 column10/;
			@data = (@data, @three); # combine the arrays.  The split column three is added to the end
			print Tr(td(@data)) if $_ !~ /^\s*$/;
			#$sth->execute(@data);  # insert it
		}
		print end_table;
	}
}

print end_html;

#!/usr/bin/perl -wT
use strict;
use CGI qw/:standard *table/;
use CGI::Carp qw/fatalsToBrowser/;
use DBI;

my $dbh = DBI->connect("dbi:Pg:dbname='test'", 'postgres', '', {'RaiseError'=>1}) or die "Unable to connect to db: $!";

$dbh->do('drop table some_stuff');
$dbh->do('create table some_stuff(column1 varchar(40),column2 varchar(40),column3 varchar(40),column4 varchar(40),column5 varchar(40),column6 varchar(40),column7 varchar(40),column8 varchar(40),column9 varchar(40),column10 varchar(40),columnU varchar(40))');

my $sth = $dbh->prepare('insert into some_stuff (column1, column2, column4, column5, column6, column7, column8, column9, column10, column3, columnU) values (?,?,?,?,?,?,?,?,?,?,?)');

print header, start_html;

my $file = 'tsv_parse.tsv';

{
	local $/ = "\r\n";
	open my $fh, "<", $file or die "Can't open $file: $!";
	my $header = <$fh>;
	chomp $header;
	my @head = split /\t/, $header;
	
	while (<$fh>) {
		chomp;
		my %row;
		@row{@head} = split /\t/;
		$row{'column3'} =~ s/"//g;

		print start_table;
		my @repeat = split /\n/, $row{'column3'};		
		foreach (@repeat) {
			my @three = split /, /, $_, 2;
			my @data = map {$row{$_} || undef} qw/column1 column2 column4 column5 column6 column7 column8 column9 column10/;
			@data = (@data, @three);
			if ($_ !~ /^\s*$/) {
				print Tr(td(@data));
				$sth->execute(@data);
			}
		}
		print end_table;
	}
}

print end_html;

test=# select * from some_stuff;
 column1 | column2 |       column3        | column4 | column5 | column6 | column7 | column8 | column9 | column10 |           columnu           
---------+---------+----------------------+---------+---------+---------+---------+---------+---------+----------+-----------------------------
 data1   | data2   | VISHAY BC COMPONENTS | data4   | data5   | data6   | data7   | data8   | data9   | data10   | 2222036 Rule Based Upgrades
 data1   | data2   | VISHAY BC COMPONENTS | data4   | data5   | data6   | data7   | data8   | data9   | data10   | 2222048 Rule Based Upgrades
 data1   | data2   | VISHAY BC COMPONENTS | data4   | data5   | data6   | data7   | data8   | data9   | data10   | 2222116 Rule Based Upgrades
 data1   | data2   | VISHAY BC COMPONENTS | data4   | data5   | data6   | data7   | data8   | data9   | data10   | 2222151 Rule Based Upgrades
(4 rows)

my @data = map {$row{$_} || undef} qw/column1 column2 column4 column5 column6 column7 column8 column9 column10/;

my @data = map {$row{$_} || undef} qw/column1 column2 column4 column5 column6 column7 column8 column9 column10 column11 column12 column13 column14 column15 column16/;

foreach (@repeat) {
	my @three = split /, /, $_, 2;
	my @data = map {$row{$_} || undef} @head;
	$data[2] = $three[0];
	push @data, $three[1];
	if ($_ !~ /^\s*$/) {
		#print Tr(td(@data));
		$sth->execute(@data);
	}
}