Page 3 - 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!

#31

April 24th, 2008, 04:50 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

The newline isn't used in the printing. It's supposed to be the separator inside the field, and it works for me on this computer in either form. The trick is going to be finding out what it is for you. Here's the raw data. It shouldn't be blank for you:

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";
	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/;
		my @data = map {$row{$_} || undef} @head;
		print Tr(td(@data));
	}
}

print end_table, end_html;

#32

April 24th, 2008, 04:53 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

One thing that should work, is to just split on whitespace. This is the lazy way, and it could cause problems if you are using whitespace within values:

Code:

	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 /\s/, $row{'column3'};
		foreach (@repeat) {
			my @data = map {$row{$_} || undef} @head;
			$data[2] = $_;
			print Tr(td(@data));
		}
	}

#33

April 24th, 2008, 04:54 PM

electron_89

Contributing User

Join Date: Aug 2006

Posts: 83

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

incredible. you are right. here is what outputted:

Code:

data1 data2 "data3a  
data3b  
data3c  
data3d" data4 data5 data6 data7 data8 data9 data10 data11 data12 data13 data14

Quote:

Originally Posted by keath

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";
	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/;
		my @data = map {$row{$_} || undef} @head;
		print Tr(td(@data));
	}
}

print end_table, end_html;

#34

April 24th, 2008, 04:55 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

See above. I want you to try the \s as a separator

#35

April 24th, 2008, 05:00 PM

electron_89

Contributing User

Join Date: Aug 2006

Posts: 83

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

what i got was "ready to split" four times from top to bottom.

Quote:

Originally Posted by keath

See above. I want you to try the \s as a separator

#36

April 24th, 2008, 05: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

It doesn't see your separator as whitespace, but again it does for me. Give me a minute to try and determine what this character is. I hope it is the same one you are using in the files you posted.

#37

April 24th, 2008, 05: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

attached is the raw excel file which hasn't been converted to text (tab delimited)

the other file was manually generated by me. but i think since we are dealing with an unknown separator here...i rather give u the raw file to take a look.

thanks!

Quote:

Originally Posted by keath

It doesn't see your separator as whitespace, but again it does for me. Give me a minute to try and determine what this character is. I hope it is the same one you are using in the files you posted.

Attached Files

raw.zip (3.7 KB, 6 views)

#38

April 24th, 2008, 05:13 PM

electron_89

Contributing User

Join Date: Aug 2006

Posts: 83

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

if you see the excel file, in those cells in column3, i think i should also parse those commas into a new column next to it.

sorry for making it even more complicated now.

Quote:

Originally Posted by electron_89

#39

April 24th, 2008, 05:25 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 going back to my original suggestion. Export the Excel files as CSV. These Excel tab files are junk

#40

April 24th, 2008, 05:27 PM

electron_89

Contributing User

Join Date: Aug 2006

Posts: 83

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

i never worked with csv before but then again i am not that great with tsv as well. will it be easier for you to help me if we go csv route?

thanks

Quote:

Originally Posted by keath

I'm going back to my original suggestion. Export the Excel files as CSV. These Excel tab files are junk

#41

April 24th, 2008, 05:30 PM

electron_89

Contributing User

Join Date: Aug 2006

Posts: 83

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

just looked at csv conversion. i now see a square symbol when i open it in notepad. but when i paste here it does what it does in excel.

Code:

column1,column2,column3,column4,column5,column6,column7,column8,column9,column10,column11,column12,c  olumn13,column14,column15,column16
data1,data2,"VISHAY BC COMPONENTS, 2222036 Rule Based Upgrades
VISHAY BC COMPONENTS, 2222048 Rule Based Upgrades
VISHAY BC COMPONENTS, 2222116 Rule Based Upgrades
VISHAY BC COMPONENTS, 2222151 Rule Based Upgrades
 ",data4,data5,data6,data7,data8,data9,data10,data11,data12,data13,data14,data15,data16

Quote:

Originally Posted by electron_89

i never worked with csv before but then again i am not that great with tsv as well. will it be easier for you to help me if we go csv route?

thanks

#42

April 24th, 2008, 05:37 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'd like to try it, but don't have Excel on this computer. Would be easier if you passed me an Excel created CSV file. Otherwise I'll move to another computer.

#43

April 24th, 2008, 05: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

thank you

please see attached.

sorry lil confusing with tsv_parse lol it is csv as u'll be able to tell when u unzip it.

Quote:

Originally Posted by keath

I'd like to try it, but don't have Excel on this computer. Would be easier if you passed me an Excel created CSV file. Otherwise I'll move to another computer.

Attached Files

tsv_parse.zip (290 Bytes, 7 views)

Last edited by electron_89 : April 24th, 2008 at 05:45 PM.

#44

April 24th, 2008, 06:14 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

Oh, I bet I know what the trouble is. In you 'real files', you probably don't have a column named 'column3', do you?