Home » Open Source » Programming Interfaces » export the query into csv file (os)
export the query into csv file [message #687322] Tue, 21 February 2023 14:18 Go to next message
arun888
Messages: 100
Registered: June 2015
Location: INDIA
Senior Member
I am expecting the query to be output to the csv format. currently, i am getting error in the below code.

can you let me know what is the changes need to be done.

#!/usr/bin/perl

use DBI;

    my $dbh = DBI->connect ('dbi:Oracle:projv6t', 'data', 'domi', {PrintError => 1, AutoCommit => 0 });
    my $sth = $dbh->prepare (qq{ SELECT data FROM data.QC_PREVIOUS_DATA_VIEW MINUS SELECT data FROM data.QC_CURRENT_DATA_VIEW });
    $sth->execute;
	open(OUTPUT,">prakash.csv") || die "can not open file";
    while(@row = $dbh->fetchrow_array) {
    print OUTPUT "tname\n";
}

Re: export the query into csv file [message #687323 is a reply to message #687322] Tue, 21 February 2023 14:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What error?

Re: export the query into csv file [message #687324 is a reply to message #687323] Tue, 21 February 2023 14:35 Go to previous messageGo to next message
arun888
Messages: 100
Registered: June 2015
Location: INDIA
Senior Member

Looking to export the query into the csv file.

Can't locate object method "execute" via package

[Updated on: Tue, 21 February 2023 14:37]

Report message to a moderator

Re: export the query into csv file [message #687325 is a reply to message #687324] Tue, 21 February 2023 15:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

1/ It is not "$dbh->fetchrow_array" but "$sth->fetchrow_array"
2/ You have this error on "execute" if "$sth" is not set
3/ Program should end with a "disconnect" to avoid an error on exit

Fixing that it "works":
E:\Temp\Listing>type t.pl
use DBI;

    my $dbh = DBI->connect ('dbi:Oracle:mikb2', 'michel', 'michel', {PrintError => 1, AutoCommit => 0 })
    my $sth = $dbh->prepare (qq{ SELECT deptno FROM dept MINUS SELECT deptno FROM emp});
    $sth->execute;
    open(OUTPUT,">prakash.csv") || die "can not open file";
    while(@row = $sth->fetchrow_array) {
    print OUTPUT "tname\n";
}
$dbh->disconnect;

E:\Temp\Listing>perl t.pl

E:\Temp\Listing>type prakash.csv
tname
Re: export the query into csv file [message #687326 is a reply to message #687325] Wed, 22 February 2023 04:55 Go to previous messageGo to next message
arun888
Messages: 100
Registered: June 2015
Location: INDIA
Senior Member
I have recieved the expected output.
Not sure how to email the prakash.csv attachment to the specific email adddress.
Re: export the query into csv file [message #687327 is a reply to message #687326] Wed, 22 February 2023 05:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I don't understand your question.
Do you ask how you can send an email from a Perl program?

Re: export the query into csv file [message #687328 is a reply to message #687327] Thu, 23 February 2023 05:36 Go to previous messageGo to next message
arun888
Messages: 100
Registered: June 2015
Location: INDIA
Senior Member
Currently, in the csv file i could see all the columns are combined into one column. I am expecting the column to be separated and the header of the file to be included as well.
Re: export the query into csv file [message #687329 is a reply to message #687328] Thu, 23 February 2023 05:39 Go to previous messageGo to next message
arun888
Messages: 100
Registered: June 2015
Location: INDIA
Senior Member

SELECT deptno, deptname, deptid  FROM dept MINUS SELECT deptno, deptname, deptid FROM emp

Output
1IT003
2CS002
3EC003

Expected output in csv file
depno depname deptid
1     IT      003
2     CS      002
3     EC      003

[Updated on: Thu, 23 February 2023 05:40]

Report message to a moderator

Re: export the query into csv file [message #687330 is a reply to message #687328] Thu, 23 February 2023 06:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
in the csv file i could see all the columns are combined into one column
It is what you program.
Post your code to see what does not fit your need.

Re: export the query into csv file [message #687331 is a reply to message #687330] Thu, 23 February 2023 06:27 Go to previous messageGo to next message
arun888
Messages: 100
Registered: June 2015
Location: INDIA
Senior Member
#!/usr/bin/perl
 use DBI;
 my $dir = '/data/QC';
 my $dbh = DBI->connect ('dbi:Oracle:projv6t', 'data', 'domi', {PrintError => 1, AutoCommit => 0 });
 my $sth = $dbh->prepare(qq{SELECT data1, data2, data3  FROM data.QC_CURRENT_DATA_VIEW MINUS SELECT data1, data2, data3 FROM data.QC_PREVIOUS_DATA_VIEW});
 $sth->execute;
 open(OUTPUT,">$dir/QC_NEW_DATA.csv") || die "can not open file";
 while(my @row = $sth->fetchrow_array) {
     print OUTPUT join ("\t", @row), "\n";
 }
 my $sth = $dbh->prepare(qq{SELECT data1, data2, data3 FROM data.QC_PREVIOUS_DATA_VIEW MINUS SELECT data1, data2, data3 FROM data.QC_CURRENT_DATA_VIEW});
 $sth->execute;
 open(OUTPUT,">$dir/QC_CLOSED_DATA.csv") || die "can not open file";
 while(my @row = $sth->fetchrow_array) {
     print OUTPUT join ("\t", @row), "\n";
 }
 $dbh->disconnect;

Shell Script :

echo "Reports Generated Sucessfully" | mailx -s "Reports Generated Sucessfully" -a "$dir/QC_NEW_DATA.csv" -a "$dir/QC_CLOSED_DATA.csv" prakash@gmail.com

Output :

All the columns are combined into one fields.

T10187693MCCOLLS M DAILY
T1019949SPAR BLAKEMORE

Expected Output in CSV:

Data1 Data2  Data3
T101  87693  "MCCOLLS M DAILY"
T101  99949  "SPAR BLAKEMORE"
Re: export the query into csv file [message #687332 is a reply to message #687331] Thu, 23 February 2023 08:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Problem is not in Perl:
E:\Temp\Listing>type t.pl
use DBI;

 my $dbh = DBI->connect ('dbi:Oracle:mikb2', 'michel', 'michel', {PrintError => 1, AutoCommit => 0 });
 my $sth = $dbh->prepare (qq{ SELECT deptno, dname, loc FROM dept});
 $sth->execute;
 open(OUTPUT,">prakash.csv") || die "can not open file";
 while(my @row = $sth->fetchrow_array) {
     print OUTPUT join ("\t", @row), "\n";
 }
$dbh->disconnect;

E:\Temp\Listing>perl t.pl

E:\Temp\Listing>type prakash.csv
10      ACCOUNTING      NEW YORK
20      RESEARCH        DALLAS
30      SALES   CHICAGO
40      OPERATIONS      BOSTON
Are you sure your files do not contain the tabulations?
Post result of:
head -1 $dir/QC_NEW_DATA.csv | od -xa
Re: export the query into csv file [message #687333 is a reply to message #687332] Thu, 23 February 2023 09:39 Go to previous messageGo to next message
arun888
Messages: 100
Registered: June 2015
Location: INDIA
Senior Member
Reports is generated correctly in the backend.
In the report recieved in email it is not correct.

mailx -s 'Reports Generated Sucessfully' -a "QC_CLOSED_DATA.csv" prakash@gmail.com
Output in linux :
C13    057  STAR IND
C13    057  VIVO ESSENTIALS
Output CSV received in email:
C13057STAR IND
C13057VIVO ESSENTIALS
Re: export the query into csv file [message #687334 is a reply to message #687333] Thu, 23 February 2023 10:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So problem is neither with Oracle nor Perl.

Check mailx parameters and options for "special" characters...
Try with sendmail instead.


[Updated on: Thu, 23 February 2023 10:06]

Report message to a moderator

Re: export the query into csv file [message #687335 is a reply to message #687334] Thu, 23 February 2023 11:00 Go to previous messageGo to next message
arun888
Messages: 100
Registered: June 2015
Location: INDIA
Senior Member
I have tried this and got the output. Can you let me know how i can add the column name field in the data.csv file?
echo "Subject: hello" | sendmail -v prakash@gmail.com < data.csv
Re: export the query into csv file [message #687336 is a reply to message #687334] Thu, 23 February 2023 20:46 Go to previous messageGo to next message
arun888
Messages: 100
Registered: June 2015
Location: INDIA
Senior Member
I have tried to use set heading on and to get the column name. But still i am unable to populate the column name
Do we have any other alternative method.
SET HEADING ON
Re: export the query into csv file [message #687337 is a reply to message #687336] Fri, 24 February 2023 00:35 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You program the output, you have to add the column name yourself: print OUTPUT...

Previous Topic: perl database access (7 merged)
Next Topic: sql query
Goto Forum:
  


Current Time: Fri Mar 29 02:42:22 CDT 2024