Solved

Joining rows in a csv file using Perl

Posted on 2006-11-16
3
267 Views
Last Modified: 2010-05-19
Below is an example of a exported data file from an application that I am working with.  I would like skip the first row and then join the rows so that I can load them into an Oracle external table.

Exported file:
(spsku),item,location
30759@ROCK,30759,ROCK
,indmd,Case
30759@RK07,30759,RK07
,indmd,Case
,,MIN_TIME,MAX_TIME,0.000000
750@GTWN,750,GTWN
,indmd,Case
468@GTWN,468,GTWN
,indmd,Case
779@GTWN,779,GTWN
,indmd,Case
780@GTWN,780,GTWN
,indmd,Case
85338@GTWN,85338,GTWN
,indmd,Case
,,10/ 2/2006  0:00:00,10/ 3/2006  0:00:00,3564.000000
,,10/ 3/2006  0:00:00,10/ 4/2006  0:00:00,4316.000000
,,10/ 4/2006  0:00:00,10/ 5/2006  0:00:00,8128.000000
,,10/ 5/2006  0:00:00,10/ 6/2006  0:00:00,8382.000000
,,10/ 6/2006  0:00:00,10/ 7/2006  0:00:00,5974.000000


Desired format:
30759@ROCK,30759,ROCK,indmd,Case
30759@RK07,30759,RK07,indmd,Case,,MIN_TIME,MAX_TIME,0.000000
750@GTWN,750,GTWN,indmd,Case
468@GTWN,468,GTWN,indmd,Case
779@GTWN,779,GTWN,indmd,Case
780@GTWN,780,GTWN,indmd,Case
85338@GTWN,85338,GTWN,indmd,Case,,10/ 2/2006  0:00:00,10/ 3/2006  0:00:00,3564.000000,,10/ 3/2006 0:00:00,10/ 4/2006  0:00:00,4316.000000,,10/ 4/2006  0:00:00,10/ 5/2006  0:00:00,8128.000000,,10/ 5/2006  0:00:00,10/ 6/2006  0:00:00,8382.000000,,10/ 6/2006  0:00:00,10/ 7/2006  0:00:00,5974.000000
0
Comment
Question by:ewgf2002
3 Comments
 
LVL 84

Expert Comment

by:ozo
Comment Utility
$n=0;
while( <DATA> ){
    next unless $n++;
    print "\n" if $n>3 && !/^,/;
    chomp;
    print;
}
print "\n";
__DATA__
(spsku),item,location
30759@ROCK,30759,ROCK
,indmd,Case
30759@RK07,30759,RK07
,indmd,Case
,,MIN_TIME,MAX_TIME,0.000000
750@GTWN,750,GTWN
,indmd,Case
468@GTWN,468,GTWN
,indmd,Case
779@GTWN,779,GTWN
,indmd,Case
780@GTWN,780,GTWN
,indmd,Case
85338@GTWN,85338,GTWN
,indmd,Case
,,10/ 2/2006  0:00:00,10/ 3/2006  0:00:00,3564.000000
,,10/ 3/2006  0:00:00,10/ 4/2006  0:00:00,4316.000000
,,10/ 4/2006  0:00:00,10/ 5/2006  0:00:00,8128.000000
,,10/ 5/2006  0:00:00,10/ 6/2006  0:00:00,8382.000000
,,10/ 6/2006  0:00:00,10/ 7/2006  0:00:00,5974.000000
0
 

Author Comment

by:ewgf2002
Comment Utility
Here is what I have and I am not getting any results.  I pretty much new to perl.

##########################################################################
#
#   Function:       process_records
#
#   Description:      This function adds the records to an array.
#
#   Returns:      @RecordList
#
##########################################################################

sub process_records
{
    if ($ProdCount == 0)
    {
        while ($CurrentValue)
        {
           
             next unless $ProdCount++;
             print "\n" if $ProductCount>3 && !/^,/;
             chomp;

            $RecordList[$ProdCount][0] = $Header;
            $RecordList[$ProdCount][1] = $Item;
            $RecordList[$ProdCount][2] = $Location;
          $RecordList[$ProdCount][3] = $IndDmd;
            $RecordList[$ProdCount][4] = $Uom;
            $RecordList[$ProdCount][5] = $Garbage;
            $RecordList[$ProdCount][6] = $StartDate;
          $RecordList[$ProdCount][7] = $EndDate;

            #$CurrentValue = <INPUTFILE>;
        } # End while
    } # End if

   
    return @RecordList;

} # End sub process_records

##########################################################################
#
#   Function: print_list
#
#   Description:      This sub routine prints the list of row values in the
#                  proper format:
#                  ProductCode~Location~Date~Qty
#
#   Returns:      @RecordList
#
##########################################################################

sub print_list
{
    # Print the records to the first 'wpl' file
    for $ProcessRow (1..$#RecordList)
    {
       print OUTPUTFILE "$RecordList[$ProcessRow][0]~$RecordList[$ProcessRow][1]~$RecordList[$ProcessRow][2]~$RecordList[$ProcessRow][3]~$RecordList[$ProcessRow][4]~$RecordList[$ProcessRow][5]~$RecordList[$ProcessRow][6]~$RecordList[$ProcessRow][7]\n";      
    } # End for

       close (OUTPUTFILE2);

    return @RecordList;

} # End sub print_list
0
 
LVL 3

Accepted Solution

by:
nishayr earned 500 total points
Comment Utility
Hi there,

try this... (might need to update the path to your perl script at the top line...)
-------------------------------------------------------------------------------------------------
#!/usr/bin/perl

use strict;
use warnings;

my $dataFile = "data.txt";
my $outputFile = "formattedData.txt";

open (IN, "$dataFile") || die ("Could not open data file: $dataFile\n");
open (OUT, ">$outputFile") || die ("Could not open data file: $outputFile\n");

my $out;
my $line;

$line = <IN>; #skip first line

# initialise
$line = <IN>;
chop($line);
print OUT $line;

while ($line = <IN>)
{
      chop ($line); # get rid of the newline
      print OUT "\n" if ($line !~ /^,/);
      print OUT $line;
}
print OUT "\n";

close (IN);
close (OUT);
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Purpose To explain how to place a textual stamp on a PDF document.  This is commonly referred to as an annotation, or possibly a watermark, but a watermark is generally different in that it is somewhat translucent.  Watermark’s may be text or graph…
This is an explanation of a simple data model to help parse a JSON feed
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now