Page 2 - Tab delimited parse

Dev Shed Forums Sponsor:

Stop making mediocre tutorials.The best tutorials are video! Camtasia Studio makes it easy to create engaging, buzz-building screen videos at any size, in any popular format. Download the free trial!

#16

April 24th, 2008, 02:52 PM

keath

!~ /m$/

Join Date: May 2004

Location: Leawood, Kansas

Posts: 2,513

Time spent in forums: 1 Week 4 Days 9 h 9 m
Reputation Power: 527

Getting null fields? You mean there is an error that you are trying to insert null data into fields that are defined to not allow nulls?

You will have to look at your database table definition. You can map the @data values to something permitted, but we would need to know what values are expected for the fields. For example, I have this code in one of my projects:

Code:

# string fields have to set to undefined if empty: ' '
map {$row{$_} = $row{$_}||undef} @order;
# integer fields have to be set to zero if undefined
map {$row{$_} = $row{$_}||'0'} qw/gaa_single gaa_multi gaa_jet gaa_heli gliders military ultralight commercial_num commuter_num taxi_num local_num itinerant_num mil_num/;
# set the order for insert
my @data = map {$row{$_}} @order;

#print Dumper \@data;
$apt_h->execute(@data);
if ($DBI::err) {
	$self->db_error('APT',$DBI::errstr,\@data,0);
}

First I clear out empty strings so that I have undefined fields instead of inserting ' '. But integer fields won't accept undefined values, so I have to map undefined integers to 0 instead.

This code won't work for you as is. You have no %row hash as I do. It's just to let you know that the problem can be fixed, but it requires specific knowledge of your database structure.

Also, I strongly agree with OmegaZero that your Excel file suggests that your data is not properly normalized. There shouldn't be a column with multiple values inserted into a table. That data should be in a table of it's own, inserted in multiple database rows, related to it's parent table by key.

#17

April 24th, 2008, 03:22 PM

electron_89

Contributing User

Join Date: Aug 2006

Posts: 83

Time spent in forums: 18 h 18 m 21 sec
Reputation Power: 2

yeah there is an error so i get empty fields but 2 rows has been created everytime i execute the script. that is even a problem because there should be a total of 4 rows/records.

how about doing this in a different approach? is it possible to assign the results to a variables so i can specifically insert them into specific fields?

just to straigten up few things...the excel file data is what i have to work with. ten's if not hundreds of thousands of data are in that format, where a specific column and each cell in that column has many data separated by Alt+Enter. By saving it into tab delimited, i thought i could somehow parse it...and I am trying to write a lil script to fix that problem and have it in a database so it can be searched.

the database format is simple exactly how the excel spreadsheet version is setup. same number of columns.

i will look into normalizing after i complete this step because i need this asap.

thx keath

Quote:

Originally Posted by keath

Code:

# string fields have to set to undefined if empty: ' '
map {$row{$_} = $row{$_}||undef} @order;
# integer fields have to be set to zero if undefined
map {$row{$_} = $row{$_}||'0'} qw/gaa_single gaa_multi gaa_jet gaa_heli gliders military ultralight commercial_num commuter_num taxi_num local_num itinerant_num mil_num/;
# set the order for insert
my @data = map {$row{$_}} @order;

#print Dumper \@data;
$apt_h->execute(@data);
if ($DBI::err) {
	$self->db_error('APT',$DBI::errstr,\@data,0);
}

#18

April 24th, 2008, 03:36 PM

keath

!~ /m$/

Join Date: May 2004

Location: Leawood, Kansas

Posts: 2,513

Time spent in forums: 1 Week 4 Days 9 h 9 m
Reputation Power: 527

Are you running this script on Windows?

#19

April 24th, 2008, 03:56 PM

electron_89

Contributing User

Join Date: Aug 2006

Posts: 83

Time spent in forums: 18 h 18 m 21 sec
Reputation Power: 2

yea using a popular editor and for mysql using mysql browser.

Quote:

Originally Posted by keath

Are you running this script on Windows?

#20

April 24th, 2008, 04:03 PM

keath

!~ /m$/

Join Date: May 2004

Location: Leawood, Kansas

Posts: 2,513

Time spent in forums: 1 Week 4 Days 9 h 9 m
Reputation Power: 527

I hope your editor isn't as popular as MS Word.

Try this script. Put it in the same directory as one of your files, and set $file to point at one of your files. See if it parses the data properly:

Code:

#!/usr/bin/perl
use strict;
use warnings;
use Data::Dumper;

my $file = 'tsv_parse.txt';

open my $fh, "<", $file or die "Can't open $file: $!";
my $header = <$fh>;
chomp $header;
my @head = split /\t/, $header;

#print Dumper \@head;
{
	while (<$fh>) {
		chomp;
		my %row;
		@row{@head} = split /\t/;
		$row{'column3'} =~ s/"//g;
		my @repeat = split /\n/, $row{'column3'};
		foreach (@repeat) {
			my @data = map {$row{$_} || undef} @head;
			$data[2] = $_;
			print Dumper \@data;
		}
	}
}

There should be no need for binmode or changing the line separator.

#21

April 24th, 2008, 04:09 PM

electron_89

Contributing User

Join Date: Aug 2006

Posts: 83

Time spent in forums: 18 h 18 m 21 sec
Reputation Power: 2

haha no lol Crimson Editor

Going to give that a shot. Thank you... I will reply soon

Quote:

Originally Posted by keath

I hope your editor isn't as popular as MS Word.

Try this script. Put it in the same directory as one of your files, and set $file to point at one of your files. See if it parses the data properly:

Code:

#!/usr/bin/perl
use strict;
use warnings;
use Data::Dumper;

my $file = 'tsv_parse.txt';

open my $fh, "<", $file or die "Can't open $file: $!";
my $header = <$fh>;
chomp $header;
my @head = split /\t/, $header;

#print Dumper \@head;
{
	while (<$fh>) {
		chomp;
		my %row;
		@row{@head} = split /\t/;
		$row{'column3'} =~ s/"//g;
		my @repeat = split /\n/, $row{'column3'};
		foreach (@repeat) {
			my @data = map {$row{$_} || undef} @head;
			$data[2] = $_;
			print Dumper \@data;
		}
	}
}

There should be no need for binmode or changing the line separator.

#22

April 24th, 2008, 04:17 PM

electron_89

Contributing User

Join Date: Aug 2006

Posts: 83

Time spent in forums: 18 h 18 m 21 sec
Reputation Power: 2

blank page, no print at all...strange..

here is what i did:

Code:

use strict;
use DBI;
use Data::Dumper;

print "Content-type: text/html\n\n";

my $file = 'tsv_parse4.txt';

open my $fh, "<", $file or die "Can't open $file: $!";
my $header = <$fh>;
chomp $header;
my @head = split /\t/, $header;

#print Dumper \@head;
{
	while (<$fh>) {
		chomp;
		my %row;
		@row{@head} = split /\t/;
		$row{'column3'} =~ s/"//g;
		my @repeat = split /\n/, $row{'column3'};
		foreach (@repeat) {
			my @data = map {$row{$_} || undef} @head;
			$data[2] = $_;
			print Dumper \@data;
		}
	}
}

Quote:

Originally Posted by electron_89

haha no lol Crimson Editor

Going to give that a shot. Thank you... I will reply soon

#23

April 24th, 2008, 04:23 PM

keath

!~ /m$/

Join Date: May 2004

Location: Leawood, Kansas

Posts: 2,513

Time spent in forums: 1 Week 4 Days 9 h 9 m
Reputation Power: 527

When you say, blank page; do you mean web page? Because this isn't a web script.

#24

April 24th, 2008, 04:24 PM

electron_89

Contributing User

Join Date: Aug 2006

Posts: 83

Time spent in forums: 18 h 18 m 21 sec
Reputation Power: 2

yes web browser (ie7) because i don't have access to anything else or i don't know how to program from the command prompt.

Quote:

Originally Posted by keath

When you say, blank page; do you mean web page? Because this isn't a web script.

#25

April 24th, 2008, 04:28 PM

keath

!~ /m$/

Join Date: May 2004

Location: Leawood, Kansas

Posts: 2,513

Time spent in forums: 1 Week 4 Days 9 h 9 m
Reputation Power: 527

Web version expects the file to be in the cgi-bin directory with the script. Can change that part later:

Code:

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

print header, start_html, start_table;

my $file = 'tsv_parse.txt';

{
	local $/ = "\r\n";
	open my $fh, "<", $file or die "Can't open $file: $!";
	my $header = <$fh>;
	chomp $header;
	my @head = split /\t/, $header;

	#print Dumper \@head;
	while (<$fh>) {
		chomp;
		my %row;
		@row{@head} = split /\t/;
		$row{'column3'} =~ s/"//g;
		my @repeat = split /\n/, $row{'column3'};
		foreach (@repeat) {
			my @data = map {$row{$_} || undef} @head;
			$data[2] = $_;
			print Tr(td(@data));
		}
	}
}

print end_table, end_html;

#26

April 24th, 2008, 04:32 PM

electron_89

Contributing User

Join Date: Aug 2006

Posts: 83

Time spent in forums: 18 h 18 m 21 sec
Reputation Power: 2

did the test. i added print "hello"; and that was the only thing that was displayed on my browser.

Code:

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

print header, start_html, start_table;
print "hello";

my $file = 'tsv_parse4.txt';

{
	local $/ = "\r\n";
	open my $fh, "<", $file or die "Can't open $file: $!";
	my $header = <$fh>;
	chomp $header;
	my @head = split /\t/, $header;

	#print Dumper \@head;
	while (<$fh>) {
		chomp;
		my %row;
		@row{@head} = split /\t/;
		$row{'column3'} =~ s/"//g;
		my @repeat = split /\n/, $row{'column3'};
		foreach (@repeat) {
			my @data = map {$row{$_} || undef} @head;
			$data[2] = $_;
			print Tr(td(@data));
		}
	}
}

print end_table, end_html;

Quote:

Originally Posted by keath

Web version expects the file to be in the cgi-bin directory with the script. Can change that part later:

Code:

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

print header, start_html, start_table;

my $file = 'tsv_parse.txt';

{
	local $/ = "\r\n";
	open my $fh, "<", $file or die "Can't open $file: $!";
	my $header = <$fh>;
	chomp $header;
	my @head = split /\t/, $header;

	#print Dumper \@head;
	while (<$fh>) {
		chomp;
		my %row;
		@row{@head} = split /\t/;
		$row{'column3'} =~ s/"//g;
		my @repeat = split /\n/, $row{'column3'};
		foreach (@repeat) {
			my @data = map {$row{$_} || undef} @head;
			$data[2] = $_;
			print Tr(td(@data));
		}
	}
}

print end_table, end_html;

#27

April 24th, 2008, 04:39 PM

keath

!~ /m$/

Join Date: May 2004

Location: Leawood, Kansas

Posts: 2,513

Time spent in forums: 1 Week 4 Days 9 h 9 m
Reputation Power: 527

I'm guessing that it's not finding the file, but we want it to die if that's the case.

One note. I forgot to remove or comment out this line:

Code:

local $/ = "\r/\n";

That's in there because I'm not using Windows. You don't need it.

Try this. Let's find the problem:

Code:

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

print header, start_html, start_table;

my $file = 'tsv_parse.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;

	#print Dumper \@head;
	while (<$fh>) {
		chomp;
		my %row;
		@row{@head} = split /\t/;
		$row{'column3'} =~ s/"//g;
		print h3('ready to split');
		my @repeat = split /\n/, $row{'column3'};
		foreach (@repeat) {
			my @data = map {$row{$_} || undef} @head;
			$data[2] = $_;
			print Tr(td(@data));
		}
	}
}

print end_table, end_html;

#28

April 24th, 2008, 04:41 PM

electron_89

Contributing User

Join Date: Aug 2006

Posts: 83

Time spent in forums: 18 h 18 m 21 sec
Reputation Power: 2

"ready to split" is what i see

Quote:

Originally Posted by keath

I'm guessing that it's not finding the file, but we want it to die if that's the case.

One note. I forgot to remove or comment out this line:

Code:

local $/ = "\r/\n";

That's in there because I'm not using Windows. You don't need it.

Try this. Let's find the problem:

Code:

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

print header, start_html, start_table;

my $file = 'tsv_parse.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;

	#print Dumper \@head;
	while (<$fh>) {
		chomp;
		my %row;
		@row{@head} = split /\t/;
		$row{'column3'} =~ s/"//g;
		print h3('ready to split');
		my @repeat = split /\n/, $row{'column3'};
		foreach (@repeat) {
			my @data = map {$row{$_} || undef} @head;
			$data[2] = $_;
			print Tr(td(@data));
		}
	}
}

print end_table, end_html;