Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Perl: split data by line and column in csv file

Status
Not open for further replies.
diera

diera

Programmer
Mar 21, 2011
28
DE
Hi,

i have data in csv file. i want to split it base on line and column.

my code so far:

Code: 
#!/usr/bin/perl use strict; 
use strict;
use warnings;
use Text::CSV_XS;

# Store our CSV file name
my $file = 'input.csv';

open( CSV_XS, '<', $file )
  or die( 'Unable to open csv file ', $file, "\n" );

open MYFILE, ">output.csv";
select MYFILE;

my $csv = new Text::CSV_XS;
foreach my $line (<CSV_XS>) {
    if ( $csv->parse($line) ) {

        my @data = $csv->fields();
        print $data[0], ',share,', $data[1], ',emotions,', $data[2],
          ',broadcast,', $data[3], ',social,',  $data[4], ',other,',   $data[5],
          ',feedback,',  $data[6], ',funding,', $data[7], ',recruit,', $data[8],
          ',promotion,', $data[9], "\n";
    }
    else {
        print 'Unable to parse CSV line: ', $line, "\n";
    }
}
close(CSV_XS);

-----data---
WorkerId,share,emotions,broadcast,social,other,feedback,funding,recruit,promotion
A1LWAFOQEFVU9K,20119,20116,,20111|20112|20113|20115|20117|20120,|||||||20118|Not in English||,,,,20114
A3ACYKMVJAEAIZ,20105,20104|20106,,20102|20104|20108|20109|20110,|||||||||,20101,,,20103|20105|20107|20110


----Current output-----
A1LWAFOQEFVU9K,share,20119,emotions,20116,broadcast,,social,20111|20112|20113|20115|20117|20120,other,|||||||20118|Not in English||,feedback,,funding,,recruit,,promotion,20114
A3ACYKMVJAEAIZ,share,20105,emotions,20104|20106,broadcast,,social,20102|20104|20108|20109|20110,other,|||||||||,feedback,20101,funding,,recruit,,promotion,20103|20105|20107|20110


what i need is:
A1LWAFOQEFVU9K share 20119
A3ACYKMVJAEAIZ share 20105
A3ACYKMVJAEAIZ emotions 20104
A3ACYKMVJAEAIZ emotions 20106
A1LWAFOQEFVU9K emotions 20116
A1LWAFOQEFVU9K broadcast ## this empty, i would like remove it.
A3ACYKMVJAEAIZ broadcast ## this empty, i would like remove it.
A1LWAFOQEFVU9K social 20111
A1LWAFOQEFVU9K social 20112
A1LWAFOQEFVU9K social 20113
A1LWAFOQEFVU9K social 20115
A1LWAFOQEFVU9K social 20117
A3ACYKMVJAEAIZ social 20102
A3ACYKMVJAEAIZ social 20104
A3ACYKMVJAEAIZ social 20108
A3ACYKMVJAEAIZ social 20109
A3ACYKMVJAEAIZ social 20110
.........


Any help is much appriciated. Thank you.
 
Sort by date Sort by votes
You need to create a data structure in a hash [tt]%alldata[/tt] like this:
Code: 
my%alldata; #at initialization
my@fields=('', 'share', 'emotions', 'broadcast', 'social', 'other', 'feedback', 'funding', 'recruit', 'promotion');
#...
  my @data = $csv->fields();
  for(my$i=1;$i<=$#fields;$i++){
    $alldata{$data[0]}[$i]=$data[$i];
  }
#... after the close
for(my$i=1;$i<=$#fields;$i++){
  for my$k(sort keys%alldata){
    if(index($alldata{$k}[$i],'|')>-1){
      for(split/|/,$alldata{$k}[$i]){
        print join(' ',$k,$fields[$i],$_),"\n";
      }
    }else{
      print join(' ',$k,$fields[$i],$alldata{$k}[$i]),"\n" if $alldata{$k}[$i];
    }
  }
}


Franco
: Online engineering calculations
: Magnetic brakes for fun rides
: Air bearing pads
 
Upvote 0 Downvote
Thanks prex.

i have tried your code but this error occured:

Use of uninitialized value $data[0] in hash element at C:\perlscript\tweet\split
.pl line 19.
Use of uninitialized value in index at C:\perlscript\tweet\split.pl line 24.

Code: 
#!/usr/bin/perl use strict; 
use strict;
use warnings;
use Text::CSV_XS;

my $csv  = new Text::CSV_XS;
my $file = 'input.csv';

open( CSV_XS, '<', $file ) or die( 'Unable to open csv file ', $file, "\n" );

open MYFILE, ">output.csv";
select MYFILE;

my %alldata;    #at initialization
my @fields = (
    'workerid', 'share',   'emotions', 'broadcast', 'social', 'other',
    'feedback', 'funding', 'recruit',  'promotion'
);

#...
my @data = $csv->fields();
for ( my $i = 1 ; $i <= $#fields ; $i++ ) {
    $alldata{ $data[0] }[$i] = $data[$i];
}

#... after the close
for ( my $i = 1 ; $i <= $#fields ; $i++ ) {
    for my $k ( sort keys %alldata ) {
        if ( index( $alldata{$k}[$i], '|' ) > -1 ) {
            for ( split /|/, $alldata{$k}[$i] ) {
                print join( ' ', $k, $fields[$i], $_ ), "\n";
            }
        }
        else {
            print join( ' ', $k, $fields[$i], $alldata{$k}[$i] ), "\n"
              if $alldata{$k}[$i];
        }
    }
}

your help is much appreciated.
 
Upvote 0 Downvote
I didn't repeat the full code, assuming you knew the structure of your program.
Before
[tt]my @data = $csv->fields();[/tt]
you need
[tt]my $csv = new Text::CSV_XS;
foreach my $line (<CSV_XS>) {
if ( $csv->parse($line) ) {[/tt]
as in your original code, and of course also the closeup
[tt] }
else {
print 'Unable to parse CSV line: ', $line, "\n";
}
}
close(CSV_XS);[/tt]
before the printing section.


Franco
: Online engineering calculations
: Magnetic brakes for fun rides
: Air bearing pads
 
Upvote 0 Downvote
my apology for my mistake. i already change the structure of my program, but it seem have an error structure in the output.

Code: 
#!/usr/bin/perl use strict;
use strict;
use warnings;
use Text::CSV_XS;

# Store our CSV file name
my $file = 'yuhuuuuu.csv';

open( CSV_XS, '<', $file )
  or die( 'Unable to open csv file ', $file, "\n" );

open MYFILE, ">1304.csv";
select MYFILE;

my $csv = new Text::CSV_XS;
my %alldata;    #at initialization
my @fields = (
    '',         'share',   'emotions', 'broadcast', 'social', 'other',
    'feedback', 'funding', 'recruit',  'promotion'
);

foreach my $line (<CSV_XS>) {
    if ( $csv->parse($line) ) {
        my @data = $csv->fields();
        for ( my $i = 1 ; $i <= $#fields ; $i++ ) {
            $alldata{ $data[0] }[$i] = $data[$i];
        }
    }
    else {
        print 'Unable to parse CSV line: ', $line, "\n";
    }
}
close(CSV_XS);

for ( my $i = 1 ; $i <= $#fields ; $i++ ) {
    for my $k ( sort keys %alldata ) {
        if ( index( $alldata{$k}[$i], '|' ) > -1 ) {
            for ( split /|/, $alldata{$k}[$i] ) {
                print join( ' ', $k, $fields[$i], $_ ), "\n";
            }
        }
        else {
            print join( ' ', $k, $fields[$i], $alldata{$k}[$i] ), "\n"
              if $alldata{$k}[$i];
        }
    }
}

--------output---------
A1LWAFOQEFVU9K share 20119
A1NLUK451L1PNS share 20092
A1VTTBJKZSYD9G share 2
A1VTTBJKZSYD9G share 0
A1VTTBJKZSYD9G share 0
A1VTTBJKZSYD9G share 7
A1VTTBJKZSYD9G share 4
A1VTTBJKZSYD9G share |
A1VTTBJKZSYD9G share 2
A1VTTBJKZSYD9G share 0
A1VTTBJKZSYD9G share 0
A1VTTBJKZSYD9G share 7
A1VTTBJKZSYD9G share 9
A2ACAAX02A5P2W share 20098
A3ACYKMVJAEAIZ share 20116
A3GPF7PJS5B6FZ share 20080
ARWU2SK84M8HM share 20119
WorkerId share share
A1LWAFOQEFVU9K emotions 20116
A1NLUK451L1PNS emotions 20098
A1VTTBJKZSYD9G emotions 2
A1VTTBJKZSYD9G emotions 0
A1VTTBJKZSYD9G emotions 0
A1VTTBJKZSYD9G emotions 7
A1VTTBJKZSYD9G emotions 2
A1VTTBJKZSYD9G emotions |
A1VTTBJKZSYD9G emotions 2
A1VTTBJKZSYD9G emotions 0
A1VTTBJKZSYD9G emotions 0
A1VTTBJKZSYD9G emotions 8
A1VTTBJKZSYD9G emotions 0

thanks again.
 
Upvote 0 Downvote
Make this correction (in red)
[tt]for ( split /\|/, $alldata{$k}[$i] ) {[/tt]
However note that to write and manage such a fairly simple program, you need a deeper (though elementary) knowledge of the language. As it has been said numerous times here, this forum is not a free coding service.

Franco
: Online engineering calculations
: Magnetic brakes for fun rides
: Air bearing pads
 
Upvote 0 Downvote
YEs, its worked now. Thanks prex1. I'm totally new to perl. i learn it myself and started only a month ago.

Thank you again for your kindly support.
 
Upvote 0 Downvote
Hi,

when i run use small data, the result is correct. but the problem when i run 324 line of data, the ouput split only up to 368 line which is suppose to up to a few thousand line of output.

please help me....

Code: 
#!/usr/bin/perl use strict;
use strict;
use warnings;
use Text::CSV_XS;

# Store our CSV file name
my $file = 'resultmturk.csv';

open( CSV_XS, '<', $file )
  or die( 'Unable to open csv file ', $file, "\n" );

open MYFILE, ">1304.csv";
select MYFILE;

my $csv = new Text::CSV_XS;
my %alldata;    #at initialization

#category data '' workerID
my@fields=('', 'share', 'emotions', 'promotion', 'social', 'feedback', 'other','broadcast', 'recruit', 'funding');

#for each line data
foreach my $line (<CSV_XS>) {
    if ( $csv->parse($line) ) {
        my @data = $csv->fields();
        for ( my $i = 1 ; $i <= $#fields ; $i++ ) {
            $alldata{ $data[0] }[$i] = $data[$i];
        }
    }
    else {
        print 'Unable to parse CSV line: ', $line, "\n";
    }
}
close(CSV_XS);

#for each column data seperate by |
for ( my $i = 1 ; $i <= $#fields ; $i++ ) {
    for my $k ( sort keys %alldata ) {
        if ( index( $alldata{$k}[$i], '|' ) > -1 ) {
            for ( split /\|/, $alldata{$k}[$i] ) {
                print join( ' ', $k,",",$fields[$i],",", $_ ), "\n";
            }
        }
        else {
            print join( ' ', $k, ",", $fields[$i], ",", $alldata{$k}[$i] ), "\n"
              if $alldata{$k}[$i];
        }
    }
}
 
 http://www.mediafire.com/?sakyfsekyd1l24z
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

rob54321
  • Locked
  • Question
Perl how to read line from file with interpolation 1
Replies
6
Views
378
rob54321
rob54321
newbie1238585
  • Locked
  • Question
Perl how to read string in csv file * as wildcard operator
Replies
2
Views
219
mikrom
mikrom
ss_ham
  • Locked
  • Question
in Perl find call looking to exclude folder and ignore duplicate finds.
Replies
2
Views
212
ss_ham
ss_ham
Limbomusic
  • Locked
  • Question
Perl files getting accessed directly
Replies
0
Views
181
Limbomusic
Limbomusic
Dino_1000
  • Locked
  • Question
Use the perl debugger to dump all the data in a string variable
Replies
0
Views
164
Dino_1000
Dino_1000

Part and Inventory Search

Sponsor

Back
Top