Oracle to CSV to XLS

Shell Programming and Scripting
1

I would like to know if have one way with read table from oracle converter in CSV o TXT and After converter in XLS or spreedsheet

Thanks so much
JAvier

2

CSV should be fine to open in excel, just create a .csv extension when spooling or such.
There are many choices, from sqlplus with formatting options (it really has alot of options :slight_smile: ) to perl, python (which can actual make xls blobs) etc.

3

As Peasant commented, Windows associates CSV extension to Excel (by default), so Excel will open it.

To extract the data we have some options:

# File_1.sql - Using COLSEP
SET LINESIZE 700 -- This may change, depends on your output line size
SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SET COLSEP ","
-- Example query
Select 1,
         2
From Dual;

# File_2.sql - Using "CONCAT"
SET LINESIZE 700 -- This may change, depends on your output line size
SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
-- Example query
Select 1 || ',' ||
         2
From Dual;

Just one more comment, be sure that there are no "," in the fields you are extracting, otherwise you may face problems or, in every field you extract, you can use the Oracle REPLACE function:

REPLACE(TABLE_COLUMN, ',', ' ')

I hope it helps!

4

more unix beacuase the files is unix:

#!/usr/bin/perl -w
###############################################################################
#
# Example of how to use the WriteExcel module
#
# Program to convert a CSV comma-separated value file into an Excel file.
# This is more or less an non-op since Excel can read CSV files.
# The program uses Text::CSV_XS to parse the CSV.
#
# Usage: csv2xls.pl file.csv newfile.xls
#
# reverse(''), March 2001, John McNamara, jmcnamara@cpan.org
#
use strict;
use Spreadsheet::WriteExcel;
use Text::CSV_XS;
# Check for valid number of arguments
if (($#ARGV < 1) || ($#ARGV > 2)) {
   die("Usage: csv2xls csvfile.txt newfile.xls\n");
};
# Open the Comma Separated Variable file
open (CSVFILE, $ARGV[0]) or die "$ARGV[0]: $!";
# Create a new Excel workbook
my $workbook  = Spreadsheet::WriteExcel->new($ARGV[1]);
my $worksheet = $workbook->add_worksheet();
# Create a new CSV parsing object
#my $csv = Text::CSV_XS->new;
#binmode CSV;
#my $csv = Text::CSV_XS->new({
my $csv = Text::CSV_XS->new({
        'quote_char'  => '"',
        'escape_char' => '',
        'sep_char'    => '|',
        'binary'      => 1,
});

# Row and column are zero indexed
my $row = 0;

while (<CSVFILE>) {
    if ($csv->parse($_)) {
        my @Fld = $csv->fields;
        my $col = 0;
        foreach my $token (@Fld) {
            $worksheet->write($row, $col, $token);
            $col++;
        }
        $row++;
    }
    else {
        my $err = $csv->error_input;
        print "Text::CSV_XS parse() failed on argument: ", $err, "\n";
    }
}