Unix Command to Convert Xls File into Xlsx File

Unix command to convert xls file into xlsx file?

As far as I know, this is not something you can easily do with standard unix tools.

You can use LibreOffice:

libreoffice --convert-to xlsx my.xls --headless

Or maybe find an online converter and submit your file to it.

shell script to convert .xls to .csv and then delete the .xls files that are converted?

You can edit this line:

for i in /reports/cctv_xls/*.xls; do libreoffice --headless --convert-to csv "$i" --outdir /cctv_reports/cctv_csv/; done

to be

for i in /reports/cctv_xls/*.xls; do libreoffice --headless --convert-to csv "$i" --outdir /cctv_reports/cctv_csv/ && rm $i; done

this will delete the xls file only if its successfully converted to csv

Perl script to convert xls to xlsx is generating blank xlsx and error message

As per user requirements the script is performing following operations.

1. writing CSV data to xlsx file
2. user needs to provide the input directory path (where multiple csv files are going to present)
3. user needs to provide the output directory (where xlsx files are going to be created)
4. script is performing following verification
   
   
   • if input directory not specified as command line argument, show error
   • if output directory is not specified as command line argument, show error
   • checking for directory exists
   • checking for csv file and also time tag information in the file name
   • only valid csv file is processed to create xlsx file.

Code

use strict;
use warnings;

use Getopt::Long;
# this module helps to read csv data from file
use Text::CSV_XS qw( csv );
# this module helps to create xlsx file
use Excel::Writer::XLSX;
use File::Basename;

# get files from input directory
sub get_files {
    my $dir = shift // '.';
    my @all_files = ();
    opendir my $dh, $dir or die "Could not open '$dir' for reading '$!'\n";
    my @files = readdir $dh;
    foreach my $file (@files) {
        if ($file eq '.' or $file eq '..') {
            next;
        }
        # check for file .csv
        if ((( split /\./, $file )[-1]) eq "csv") {
            # winodws os syntax
            push @all_files, "$dir\\$file";
            # linux os syntax
            #push @all_files, "$dir/$file";
        }
    }
    closedir $dh;
    return @all_files;
}

# get digit from string
sub get_digit {
    my ($string) = @_;
    my ($digit) = $string =~ /(\d+)/;
    return ((defined $digit) ? $digit : '');
}

# write data to excel file
sub write_excel {
    my ($worksheet,$aoa) = @_;
    my $row = 0;
    foreach my $internal_array_ref (@$aoa) {
        my $col=0;
        foreach my $element (@$internal_array_ref) {
            # if element is not having any value
            $element = '' unless defined $element;
            $worksheet->write_string( $row, $col++, $element );
        }
        $row++;
    }
}

### Main ##
my $csv_dir;
my $output_dir;
# command line arguments
# input directory (--idir)which going to contain csv files
# ouput directory (--odir) where xlsx files going to be created
GetOptions(
    'idir=s' => \$csv_dir,
    'odir=s' => \$output_dir,
) or die "Usage: $0 --idir Directory PATH -odir DIRECTORY PATH \n";

# if input directory is not specified  as an input argument, show error
if (not defined $csv_dir) {
    print "\n Argument 'idir' is mandatory \n";
    print "for example $0 --ifile DIRPATH";
    exit 1;
}

# if output directory is not specified as an input argument, show error
if (not defined $output_dir) {
    print "\n Argument 'odir' is mandatory \n";
    print "for example $0 --odir DIRECTORY PATH";
    exit 1;
}

# check for input and output directory path
if ((-d $csv_dir) && (-d $output_dir)) {
    # get all csv files from input directory
    my @csv_files = get_files($csv_dir);
    # read csv file data and create xlsx file
    foreach my $csv_file (@csv_files) {
        # Check for file contain date digit
        my $date_digit = get_digit($csv_file);
        if ($date_digit eq '') {
            print "\n $csv_file not contain date information \n";
        } else {
            # excel file name with date digit
            # get file name from given file path (except extension)
            my $filename = basename("$csv_file",  ".csv");
            # this syntax is for windows to create xlsx file path
            my $excel_file = "$output_dir\\$filename.xlsx";
            # this syntax is for windows to create xlsx file path
            #my $excel_file = "$output_dir/$filename.xlsx";

            #  Read whole file in memory (as array of array)
            # change seperate char as per your csv file data
            # change quote char as per your csv file data (else just mentioned undef)
            # change escape char as per your csv file data (else just mentioned undef)
            eval {
                my $aoa = csv (in => $csv_file,     
                        encoding => "UTF-8",
                        sep_char    => ',',
                        quote_char  => '"',
                        escape_char => undef);

                if (scalar @$aoa) {
                    # Create a new Excel workbook
                    my $workbook = Excel::Writer::XLSX->new( $excel_file );
                    # Add a worksheet
                    my $worksheet = $workbook->add_worksheet();
                    # write csv data to excel file
                    write_excel($worksheet,$aoa);
                    $workbook->close();
                    print "\n The $excel_file created sucessfully. \n";
                }
            };

            if ($@) {
                print "\n Invalid CSV file or check CSV file format \n";
            }
        }
    }
}  else {
    print "\n Please provide valid directiory path: $csv_dir or Please provide valid directory path $output_dir";
    exit 1;
}

Output and execution syntax

### Windows
C:\Users\batman\source\repos>perl csv_xlsx.pl --idir C:\Users\batman\source\repos\csv --odir C:\Users\batman\source\repos\excel_out

 The C:\Users\batman\source\repos\excel_out\sample_dept1_20200609.xlsx created sucessfully.

 The C:\Users\batman\source\repos\excel_out\sample_dept2_20200610.xlsx created sucessfully.

### Linux
perl csv_xlsx.pl --idir /Users/batman/source/repos/csv --odir /Users/batman/source/repos/excel_out

---

Related Topics