Import Mysql data into a table in excel

[aks2trip]

I am using perl to connect to a mysql db.
How do I export the data returned from the sql command into a nice looking table in excel?

 

[LawnBoy]

You could write to file as CSV and import that into excel.

 

[varakal]

Or you can use Spreadsheet::WriteExcel to create an excel sheet.

 

[aks2trip]

i am trying to use Spreadsheet::WriteExcel. But how do I break it down and put it in rows? How do i create a table and also how can i link the data from two different queries to enter all values in one table?

Sorry for my ignorance but i am new at perl and dont have much experience with it.

 

[LawnBoy]

Spreadsheet::WriteExcel

I should have known. I'm continually amazed at the sheer volume of the modules available to perl.

 

[dmazzini]

You can create a new excel file using the module and write on it.

Actually you can create a file an import it to excel..

use Spreadsheet::WriteExcel;
$output_file="myfile.xls";

my $workbook = Spreadsheet::WriteExcel->new($output_file);
die "Problems creating new Excel File: $! "unless defined $workbook;
my $worksheet = $workbook->add_worksheet();

open INPUT,"/m/home/dmazzini/myresult.log" or die "Couldn't open file: $!";
$worksheet->write($.-1,0,[split]) while <INPUT>;



dmazzini
GSM System and Telecomm Consultant

 

[TonyGroves]

As an alternative to using Perl, Excel might have a facility similar to the more advanced OpenOffice, where you can save an SQL query and run it any time, importing the data into a spreadsheet with drag-n-drop.

 

[aks2trip]

What I did was stored the result from the query straight into a hash of array and then printed it in excel using WriteExcel.
foreach $record (@output) {

$i=2;
while ( $i <= $#{$record->{tableref}} ) {
$j=0;
while ($j < $record->{field_count}) {
${$record->{tableref}[$i]}[$j] =~ s/\r//g;
$worksheet->write($i+1, $j, ${$record->{tableref}[$i]}[$j], $format1 );
$j++;
}
$i++;
}
}

But this method, I have to run each query and then separately store the result in another hash and write it out again, Hence the code becomes very repetitive.

Also, the result of my first query looks something like this

A 1
B 2
C 3
D 4

And the result of my second query looks like this

A 1
A 2
A 3
B 1
B 4
B 5
C 2
C 3
C 4
D 1
D 2
D 4

How do I merge the results to show the following

A 1 1
2
3
B 2 1
4
5
C 3 2
3
4
D 4 1
2
4

Any suggestions welcome...
Thanks

 

[aks2trip]

I have to use perl since its a unix environment...

 

[dmazzini]

It should be faster generate a csv file with the result of the queries.

Header1,Header2,header3.....headern
value1,value2,value3,.......valuen
value1,value2,value3,.......valuen.
.
.
.
.valuen,valuen,valuen,.......valuen

and then using spreadheet::writeexcel module to convert it to excel:

#!/opt/nokianms/bin/perl
# From csv to excel

use Spreadsheet::WriteExcel;

open (CSVFILE, "myoutput.csv") or die "myoutput.csv: $!";
my $excel = Spreadsheet::WriteExcel->new("myoutput.xls");
my $worksheet = $excel->add_worksheet();
my $row = 0;
my $col;

while (<CSVFILE>) {
chomp;
my @Fld = split('\,', $_);
$col = 0;
foreach my $token (@Fld) {
$worksheet->write($row, $col, $token);
$col++;
}
$row++;
}





dmazzini
GSM System and Telecomm Consultant

 

[aks2trip]

ok so how would i import my data from 3 MySQL queries into a csv file? Sorry, I don't know how to do that.

Would i run query 1
append to myoutput.csv file

query 2
append again?

query 3
append again?

the problem is that they are three separate queries as opposed to one. SO initially I was putting them in three different worksheets, but now i want to be able to put them all in one worksheet in One table.

 

[dmazzini]

Exactly as you said...

Append to the file the different outputs.

valuequery1,valuequery1,valuequery1
valuequery1,valuequery1,valuequery1
.
.
.
.
valuequery2,valuequery2,valuequery2
valuequery2,valuequery2,valuequery2
.
.
.
.
valuequery3,valuequery3,valuequery3
valuequery3,valuequery3,valuequery3

So the problem is that you need to group by A.B,C or D etc. You could create your own structure (hash of arrays where key is the group and values the other variables) where you can group the result.

Other solution:

I guess that you could use SQL and make a relation between tables. It seems to be that group is the primary key. And then it will produce the output already grouped.


dmazzini
GSM System and Telecomm Consultant

 

[aks2trip]

OK so when i run the query, how do i get the results in a csv file?

I will try and group everything in One table.

While browsing cpan, i also found a module called, Spreadsheet::WriteExcel::FromDB
This takes the entire table and prints it in excel. You may choose to ignore columns or put where conditions.
Its pretty useful.

 

[dmazzini]

In order to generate a csv file, just you need to open a filehandle a start writing on it:
e.g.

#!/opt/nokianms/bin/perl

unlink ($RESULTS) and print "$RESULTS deleted" or print "$RESULTS does not yet exist\n";

open DCN, ">>$RESULTS" or die "Can't open $RESULTS: $!\n";
print DCN "BSC,CNUMBER,UNIT,INTERFACE,IP ADDRESS,CONNECTION,STATE";

.
.
.
.
.

first query

inside the loop of your query

{

print DCN "$bsc,$cnumber,$unit,$interface,$ip,$connection,$status";
}

second query
{

print DCN "$bsc,$cnumber,$unit,$interface,$ip,$connection,$status";
}

close DCN

or even you can have the results of each query in different arrays and then print the content of the array to the filehandle.

print DCN @queryresults;



dmazzini
GSM System and Telecomm Consultant

 

[dmazzini]

I forgot to tell you that you must use \n for new line after each print

print DCN "$bsc,$cnumber,$unit,$interface,$ip,$connection,$status\n";

or define

$\="\n"; at the beginning of the script


dmazzini
GSM System and Telecomm Consultant

 

[aks2trip]

I can get link tables through mysql and get everything into one table and print it to excel using Spreadsheet::WriteExcel::FromDB

It becomes very straightforward. My only problem now is that it would repeat the data for some of the columns because of the linking. So i get results like that

A B C D E F
hockey 16 5 2 /home 69
hockey 16 5 2 /apps 38
javafea 59 10 14 / 24
javafea 59 10 14 /var 16
javahab 9 1 3 / 42
javahab 9 1 3 /var 12

How do i get rid of repetitive results in excel using perl?
Any ideas?