Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

hey i have a text file in which i have delimiter as ';;' so i want to convert it to csv file . this means that when you separate out the column entry in csv viewed in excel the seperator should be a ';;' not comma . let me give you an example

say if this is the line of the file given as input abd;;sasa;;trre,dsa;;sdas;;dsss output in csv viewed in excel should look like abd|sasa|trre|dsa|sdas|dsss , where | is a column seperator as is in excel

Replies are listed 'Best First'.
Re: to csv file
by CountZero (Bishop) on Jun 29, 2013 at 12:19 UTC
    If you are absolutely positively sure and certain that there will be no ';;' anywhere inside the fields, then you can use split to good effect
    use Modern::Perl;
use Text::CSV;

my $csv = Text::CSV->new( { eol => "\n" } )
  or die "Cannot use CSV: " . Text::CSV->error_diag();
open my $fh, '>', './new.csv' or die "new.csv: $!";

while (<DATA>) {
    chomp;
    $csv->print( $fh, [ split /;;/ ] );
}

close $fh or die "new.csv: $!";

__DATA__
abd;;sasa;;trre,dsa;;sdas;;dsss
123;;456,789;;include some spaces and ; once;;55555+;;abc

    [download]
    Output:
    abd,sasa,"trre,dsa",sdas,dsss
123,"456,789","include some spaces and ; once",55555+,abc

    [download]

    CountZero

    A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

    My blog: Imperial Deltronics
[reply]
[d/l]
[select]
Re: to csv file
by poj (Abbot) on Jun 29, 2013 at 07:10 UTC

    Do you want trre,dsa to appear in one column or two ?. Your post shows two.

    poj
[reply]
[d/l]
      i am sorry it was a mistake , i want them as one
[reply]

        One way is to take advantage of excel's ability to open tab delimited text files with .xls extension

        #!perl
use strict;
open OUT,">test.xls" or die "$!";
while (<DATA>){
  s/;;/\t/g;
  print OUT;
}
close OUT;

__DATA__
abd;;sasa;;trre,dsa;;sdas;;dsss
abd;;sasa;;trre,dsa;;sdas;;dsss
abd;;sasa;;trre,dsa;;sdas;;dsss

        [download]
        poj
[reply]
[d/l]
Re: to csv file
by hdb (Monsignor) on Jun 29, 2013 at 07:54 UTC

    Excel can open text files with semikolon as separator! You can also specify two semikolons as separators! No need to change your file.

[reply]
[d/l]
      Actually I think Text::CSV and the others only work with single character separators. From Text::CSV:
      sep_char
      The char used for separating fields, by default a comma. (,). Limited to a single-byte character, usually in the range from 0x20 (space) to 0x7e (tilde).

      The separation character can not be equal to the quote character. The separation character can not be equal to the escape character.
      If you spot any bugs in my solutions, it's because I've deliberately left them in as an exercise for the reader! :-)
[reply]
        output in csv viewed in excel should look like abd|sasa|trre|dsa|sdas|dsss

        I just thought that if the input would be opened in Excel specifying two semicolons as separators it would look exactly like described in the OP.

[reply]
[d/l]

      Re hdb's observation that "Excel can open text files with semikolon as separator! You can also specify two semikolons as separators!"

      True, but at least with Excel 2000, you'll find you have numerous hoops (popup selection choices) to jump through to open a text file with a doubled semi-colon as the separator. (Dunno 'bout other, more-recent versions. Too cheap to own'em).

      If you didn't program your executable by toggling in binary, it wasn't really programming!

[reply]

Back to Seekers of Perl Wisdom