Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    20
    Rep Power
    0

    How to add comma after every row values


    How to add a comma after every row values FROM the result set

    here is my code


    # Script to be used to extract the data from
    use strict;
    use DBI;
    use Cwd;
    my $sth;
    # To know your Present Working Directory
    my $pwd = cwd();
    my $filename = 'albie.txt';

    my $dbh = DBI->connect('DBI:mysql:classicmodels', 'root') || die "Could not connect to database: $DBI::errstr";
    print "Connection Successful \n";

    $sth = $dbh->prepare( "SELECT *FROM employees");

    #execute the query
    $sth->execute( );
    ## Retrieve the results of a row of data and print
    print "\tQuery results:\n================================================\n";

    while ( my @row = $sth->fetchrow_array( ) ) {

    open (FILE, ">>" ,$filename) or { die " Unable to create .$filename\n " } ;

    print FILE "@row\n";

    }
    warn "Problem in retrieving results", $sth->errstr( ), "\n"
    if $sth->err( );

    exit;

    print "Hi your File $filename has been placed \n in the directory called $pwd \n " ;
    $sth -> finish(); $dbh->disconnect();
    print "Goodbye\n";
  2. #2
  3. 'fie' on me, allege-dly
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2003
    Location
    in da kitchen ...
    Posts
    12,889
    Rep Power
    6444
    perl Code:
    print FILE join (",", @row)."\n";
    --Ax
    without exception, there is no rule ...
    Handmade Irish Jewellery
    Targeted Advertising Cookie Optout (TACO) extension for Firefox
    The great thing about Object Oriented code is that it can make small, simple problems look like large, complex ones


    09 F9 11 02
    9D 74 E3 5B
    D8 41 56 C5
    63 56 88 C0
    Some people, when confronted with a problem, think "I know, I'll use regular expressions." Now they have two problems.
    -- Jamie Zawinski
    Detavil - the devil is in the detail, allegedly, and I use the term advisedly, allegedly ... oh, no, wait I did ...
    BIT COINS ANYONE
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Apr 2009
    Posts
    1,923
    Rep Power
    1225
    How to add a comma after every row values FROM the result set
    Is that what you really want, or do you want the comma between each array element as Ax shows?

    The method Ax shows is the most obvious and common approach, but a better approach for writing csv files would be to use the Text::CSV module.

    In addition to the module, I made a few other minor adjustments, which you should look over.
    Code:
    #!/usr/bin/perl
    
    use warnings;
    use strict;
    use DBI;
    use Text::CSV;
    use Cwd;
    
    
    # To know your Present Working Directory
    my $pwd = cwd();
    my $filename = 'albie.txt';
    
    open my $FILE, ">" , $filename or die "Unable to create $filename <$!>\n";
    my $csv = Text::CSV->new( {eol => "\n" });
    
    # RaiseError forces all errors to be fatal without explicit checks
    my $dbh = DBI->connect('DBI:mysql:classicmodels', 'root', 'password',
    		       { RaiseError => 1 } )
            or die "Could not connect to database: $DBI::errstr";
    print "Connection Successful \n";
    
    my $sth = $dbh->prepare( "SELECT * FROM employees");
    
    #execute the query
    $sth->execute( );
    ## Retrieve the results of a row of data and print
    print "\tQuery results:\n================================================\n";
    
    while ( my @row = $sth->fetchrow_array( ) ) {
    
        print $csv->print($FILE, \@row);
    
    }
    #warn "Problem in retrieving results", $sth->errstr( ), "\n"
    #if $sth->err( );
    
    close $FILE;
    print "Hi your File $filename has been placed \n in the directory called $pwd \n " ;
    $sth -> finish();
    $dbh->disconnect();
    print "Goodbye\n";
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2012
    Posts
    828
    Rep Power
    496
    Just another quick way without the CSV module:

    Code:
    { 
         $" = ",";
         print FILE "@row", "\n";
    }
    $" is the output list separator for interpolated lists.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    20
    Rep Power
    0

    Thumbs up Row Headers


    Originally Posted by FishMonger
    Is that what you really want, or do you want the comma between each array element as Ax shows?

    The method Ax shows is the most obvious and common approach, but a better approach for writing csv files would be to use the Text::CSV module.

    In addition to the module, I made a few other minor adjustments, which you should look over.
    Code:
    #!/usr/bin/perl
    
    use warnings;
    use strict;
    use DBI;
    use Text::CSV;
    use Cwd;
    
    
    # To know your Present Working Directory
    my $pwd = cwd();
    my $filename = 'albie.txt';
    
    open my $FILE, ">" , $filename or die "Unable to create $filename <$!>\n";
    my $csv = Text::CSV->new( {eol => "\n" });
    
    # RaiseError forces all errors to be fatal without explicit checks
    my $dbh = DBI->connect('DBI:mysql:classicmodels', 'root', 'password',
    		       { RaiseError => 1 } )
            or die "Could not connect to database: $DBI::errstr";
    print "Connection Successful \n";
    
    my $sth = $dbh->prepare( "SELECT * FROM employees");
    
    #execute the query
    $sth->execute( );
    ## Retrieve the results of a row of data and print
    print "\tQuery results:\n================================================\n";
    
    while ( my @row = $sth->fetchrow_array( ) ) {
    
        print $csv->print($FILE, \@row);
    
    }
    #warn "Problem in retrieving results", $sth->errstr( ), "\n"
    #if $sth->err( );
    
    close $FILE;
    print "Hi your File $filename has been placed \n in the directory called $pwd \n " ;
    $sth -> finish();
    $dbh->disconnect();
    print "Goodbye\n";

    Would Appreciate if i get to know that how to append the row header on the result file

    Thanks,
    Srini
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Apr 2009
    Posts
    1,923
    Rep Power
    1225
    What are the header fields?

    Since you're querying an employee table, I'll keep the example simple with 3 fields.

    After opening the file and prior to the while loop, you simply print the fields.

    Code:
    open my $FILE, ">" , $filename or die "Unable to create $filename <$!>\n";
    my $csv = Text::CSV->new( {eol => "\n" });
    
    my @emp_fields = qw(name position salary);
    
    print $csv->print($FILE, \@emp_fields);
  12. #7

  13. Join Date
    Jul 2013
    Location
    Melbourne
    Posts
    36
    Rep Power
    0
    Originally Posted by Laurent_R
    Just another quick way without the CSV module:

    Code:
    { 
         $" = ",";
         print FILE "@row", "\n";
    }
    $" is the output list separator for interpolated lists.
    This is the best method.
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    20
    Rep Power
    0

    more than 100 rows ????


    Originally Posted by PeterSullivan
    This is the best method.
    Hi ,

    In case if we have more than 100 rows in a table .. if's hard to give name to each of them .

    So how can handle in such situation ? [code]
  16. #9
  17. !~ /m$/
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    May 2004
    Location
    Reno, NV
    Posts
    4,252
    Rep Power
    1810
    You can pull the names from the database table along with the data if you use DBI's array of hash-references structure.
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    20
    Rep Power
    0

    example


    Originally Posted by keath
    You can pull the names from the database table along with the data if you use DBI's array of hash-references structure.
    mind if see any example code ???
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2012
    Posts
    828
    Rep Power
    496
    Originally Posted by srinidelite
    In case if we have more than 100 rows in a table .. if's hard to give name to each of them .
    Why would you want to give names to the rows?

    You usually retrieve the rows one at a time, process them as you need and output them, and then only proceed with the next row. The same name can be used for all rows, as it is just a temporary convenient way to handle the row. Just as you might label $line the current record of a file that you are reading from a filehandler; $line will be the name for the current line and will successively contain each of the lines of the file, even if the file has millions of lines.
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Apr 2009
    Posts
    1,923
    Rep Power
    1225
    Originally Posted by srinidelite
    Hi ,

    In case if we have more than 100 rows in a table .. if's hard to give name to each of them .

    So how can handle in such situation ? [code]
    You don't "give names" to rows in csv files. The first row in the csv file is the header which lists the column names. The column names correspond to the database fields/columns that you retrieved from the database.

    Do you know what those database fields are that you're selecting?
  24. #13
  25. !~ /m$/
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    May 2004
    Location
    Reno, NV
    Posts
    4,252
    Rep Power
    1810
    I think he means columns rather than rows.


    I'm at work, so this isn't fully implemented. If you can't work out the details from this, I'll follow up later.

    Code:
    #!/usr/bin/perl
    use strict;
    use warnings;
    
    use DBI;
    use Data::Dumper;
    
    my $dbh = DBI->connect('DBI:mysql:classicmodels', 'root', 'password', { RaiseError => 1 } ) or die "Could not connect to database: $DBI::errstr";
    my $sth = $dbh->prepare( "SELECT * FROM employees");
    $sth->execute( );
    
    my $employees = $sth->fetchall_arrayref({});
    
    my @columns = keys ${ $employees}[0];
    print Dumper \@columns;
    
    foreach my $e (@$employees) {
       print Dumper $e;
    }
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2012
    Posts
    828
    Rep Power
    496
    Originally Posted by keath
    I think he means columns rather than rows.
    I have carefully considered this possibility before posting my previous answer, but finally came to the opposite conclusion.

    We'll see when the OP comes back.
    Last edited by Laurent_R; August 13th, 2013 at 01:42 PM.
  28. #15
  29. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    20
    Rep Power
    0
    Originally Posted by keath
    I think he means columns rather than rows.


    I'm at work, so this isn't fully implemented. If you can't work out the details from this, I'll follow up later.

    Code:
    #!/usr/bin/perl
    use strict;
    use warnings;
    
    use DBI;
    use Data::Dumper;
    
    my $dbh = DBI->connect('DBI:mysql:classicmodels', 'root', 'password', { RaiseError => 1 } ) or die "Could not connect to database: $DBI::errstr";
    my $sth = $dbh->prepare( "SELECT * FROM employees");
    $sth->execute( );
    
    my $employees = $sth->fetchall_arrayref({});
    
    my @columns = keys ${ $employees}[0];
    print Dumper \@columns;
    
    foreach my $e (@$employees) {
       print Dumper $e;
    }
    I couldn't be able to get the expected result from the given code ! What's wrong with the code ?
    use warnings;
    use strict;
    use DBI;
    use Text::CSV;
    use Cwd;


    # To know your Present Working Directory
    my $pwd = cwd();
    my $filename = 'albie.csv';

    open my $FILE, ">" , $filename or die "Unable to create $filename <$!>\n";
    my $csv = Text::CSV->new( {eol => "\n" });

    # RaiseError forces all errors to be fatal without explicit checks
    my $dbh = DBI->connect('DBI:mysql:classicmodels', 'root', 'sudhersan',
    { RaiseError => 1 } )
    or die "Could not connect to database: $DBI::errstr";
    print "Connection Successful \n";

    my $sth = $dbh->prepare( "SELECT * FROM orderdetails");

    #execute the query
    $sth->execute( );
    ## Retrieve the results of a row of data and print
    print "\tQuery results:\n================================================\n";

    my $employees = $sth->fetchall_arrayref({});

    my @columns = keys ${$employees}[0];

    print $csv->print($FILE, \@columns);

    while ( my @row = $sth->fetchrow_array( ) ) {

    print $csv->print($FILE, \@row);

    }
    warn "Problem in retrieving results", $sth->errstr( ), "\n"
    if $sth->err( );

    close $FILE;
    print "Hi your File $filename has been placed \n in the directory called $pwd \n " ;
    $sth -> finish();
    $dbh->disconnect();
    print "Goodbye\n";
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo