Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things
 
PerlMonks  

Dump data from unicode database

by sachin raj aryan (Acolyte)
on Jun 30, 2021 at 10:59 UTC ( #11134481=perlquestion: print w/replies, xml ) Need Help??

sachin raj aryan has asked for the wisdom of the Perl Monks concerning the following question:

hello monks I am using dbix::dump data from database Which is in utf-8. (When saving as excel or csv) The column data which is in utf8 is displaying as ???????. Is there any way i can dump data in proper format.

my $sql1 = "select deptid,to_char(a.epfno) epfno,a.ename ename,desig, +decode(b.status,'P','उपस्थि&#2340 +;','A','अवकाश','D','प्&#235 +2;तिनियुक्त&#23 +67;') status,b.rmk from Atnd_emp_mast a,Atnd_position b where repoid=1 and I +SACTIVE=0 and a.epfno=b.epfno and SUMM_HOD_FLAG=1 and ATTEN_DT=to_date('17-12-2020','DD-MM-YYYY') order by SUMM_SORTID"; + $sth =$dbh->prepare($sql1); $sth->execute(); $out = DBIx::Dump->new('format' => excel, # exce +l or csv 'output' => $f1, # file to save as 'sth' => $sth); $out->dump(); print "Report Generated with file name $f1 \n"; $f2 = join".",$f1,"xls"; system "copy $f1 $f2"; print "$f2 \n";

Out put is coming as

DEPTID EPFNO ENAME DESIG STATUS RMK 19 3346927 (...) +‰पस्थित 9 5222958 () + प्रतिनियु•्ति 10 2870266 ( ..) ‰ +स्थित 7 2874318 ( ) &#1 +37;पस्थित 9 2864045 ( + ) ‰पस्थित 15 4415744 ( ) + …व•ाश 17 2860538 ‰पस +थित

Replies are listed 'Best First'.
Re: Dump data from unicode database
by hippo (Bishop) on Jun 30, 2021 at 13:14 UTC
    I am using dbix::dump data from database Which is in utf-8. ... Is there any way i can dump data in proper format.

    DBIx::Dump appears to pre-date most of the unicode support in Perl. I would avoid it for such tasks. Here is an SSCCE which avoids it. Feel free to tweak for your own database engine.

    use strict; use warnings; use DBI; use Encode qw/find_encoding/; use Text::CSV qw/csv/; use Test::More tests => 2; my $enc = find_encoding ("UTF-8") or die 'Cannot set encoding'; my $str = "\N{ETHIOPIC SYLLABLE QAA}"; my $dbh = DBI->connect ("dbi:mysql:test", 'nobody') or die "No db"; $dbh->do ('DROP TABLE IF EXISTS stuff'); $dbh->do ('CREATE TABLE stuff ( yada varchar(255) charset utf8 );'); $dbh->do ('INSERT INTO stuff VALUES (?)', undef, $enc->encode ($str)); # Read it back from the DB my @fromdb; my $sth = $dbh->prepare ('SELECT yada FROM stuff'); $sth->execute; while (my $r = $sth->fetchrow_arrayref) { $_ = $enc->decode ($_) for @$r; push @fromdb, $r; } is $fromdb[0]->[0], $str, 'Back from DB'; # Write it out to a file csv (in => \@fromdb, out => 'outfoo.csv', encoding => 'utf-8' ); # Check the contents of the file my $fromfile = csv (in => 'outfoo.csv', encoding => 'utf-8'); is $fromfile->[0][0], $str, 'Back from file';

    🦛

Re: Dump data from unicode database
by bliako (Monsignor) on Jun 30, 2021 at 11:18 UTC

    try telling perl to use the correct encoding for STDOUT (which is where print "zbzb" prints) with binmode(STDOUT, ":utf8"); . Add to STDERR as needed.

      Please do not promote :utf8! Use the correct :encoding(utf-8) instead from now on.


      Enjoy, Have FUN! H.Merijn
      my $sql1 = "select deptid,to_char(a.epfno) epfno,a.ename ename,desi +g,decode(b.status,'P','उपस्थि&#23 +40;','A','अवकाश','D','प्&#2 +352;तिनियुक्त&# +2367;') status,b.rmk from Atnd_emp_mast a,Atnd_position b where repoid=1 and I +SACTIVE=0 and a.epfno=b.epfno and SUMM_HOD_FLAG=1 and ATTEN_DT=to_date('17-12-2020','DD-MM-YYYY') order by SUMM_SORTID"; + $sth =$dbh->prepare($sql1); $sth->execute(); my @rows =$sth->fetchrow_array; while (@rows=$sth->fetchrow_array) { binmode(STDOUT, ":utf8"); print join(",",@row),"\n"; }

      made changes its still not working

        ENOTENOUGHINFO: you have told us nothing about the database, its version, its encoding, its network layers etc etc.

        $ cat info.csv id,count,desc,asc,row 1,2,foo,bar,10 $ perl -MDBI -COE -wE'my$dbh=DBI->connect("dbi:CSV:",undef,undef,{f_ex +t=>".csv/r"});my$sth=$dbh->prepare("select id,\x27\x{20ac}\x27 from i +nfo");$sth->execute;while(my$r=$sth->fetch){say for@$r;}' 1

        That just worked, so it could be your database or your database driver, or your database driver version or ...


        Enjoy, Have FUN! H.Merijn

        is the DB set up to store unicode? is what you store in DB unicode? is what you get out from DB encoded as unicode?

        placing a binmode(STDOUT, ":encoding(utf-8)"); (thanks Tux) at the beginning of your script will display unicode to STDOUT and complain if it contains invalid bytes. But you may need to tell your db to store unicode (for mysql it used to be something like this during table construction: mysql_enable_utf8mb4) and make DBI::connect aware with mysql_enable_utf8 => 1. For some cases you need to explicitly encode the bytes received from DB into unicode using Encode::encode... or investigate if it's a case of double encoding using Encode::decode...

      A reply falls below the community's threshold of quality. You may see it by logging in.
Re: Dump data from unicode database
by sachin raj aryan (Acolyte) on Jul 06, 2021 at 07:18 UTC

    Issue resolved when i added ora_charset=>'AL32UTF8' in dbi->connect my $dbh = DBI->connect('dbi:Oracle:host=xx.x.x.x;service_name=shadow; port=1521;SERVER=DEDICATED',$user,$password, { PrintError => 1, RaiseError => 1, AutoCommit => 1, FetchHashKeyName => 'NAME_lc', TraceLevel => 1, ora_charset=>'AL32UTF8' }) or die "Canot create Databse Handle: $DBI::errstr() \n";

Re: Dump data from unicode database
by Anonymous Monk on Jun 30, 2021 at 14:27 UTC
    Try dumping the data in hexadecimal ... or just the first few bytes of it ... in order to see for yourself exactly what the database record contains. (You can use 'hexdump' on Linux.) Is it the correct byte sequences? If so, then "you have an encoding problem." Some player it could be Perl, or it could even be your shell does not recognize that the data is supposed to be Unicode. It's printing it as ASCII. Encoding options for a shell (window ...) might be a system preference.
Re: Dump data from unicode database
by Anonymous Monk on Jul 01, 2021 at 00:15 UTC

    First question: "What does the database row contain?" Based on what you've printed, I daresay that it really does contain Unicode information. Therefore, the most likely explanation is that either Perl or your command-line shell(!) does not know to display the information correctly. After satisfying yourself that Perl knows what to do, check the "settings" of your shell (window ...).

    If "Perl" settings did not do the trick as you describe then the most-likely culprit is your shell.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://11134481]
Approved by marto
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (3)
As of 2022-05-28 11:04 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Do you prefer to work remotely?



    Results (99 votes). Check out past polls.

    Notices?