Need Help in converting

Shell Programming and Scripting
1

I have Excel file with the below three columns, i need your expertise in converting this to .csv file delimiter "|"

Excel -

Serial Number	Serial Name	   Serial Brand
111                   test                     sample
123                   test2                   sample1
134                  134_test3             sample2
135                  135_test4            sample3

Reuested this to converted to .csv file with delimiter "|" and for the column Serial name.. if any of the records having serial number with Serial name, the serial number should be ignored... and the desired csv file should be like below

Serial Number|Serial Name	|Serial Brand
111|test|sample
123|test2|sample1
134|test3|sample2
135|test4|sample3
2

Which is it? Excel, CSV, or flatfile? Looks like a flatfile to me, a comma-separated values file tends to have its values separated by commas. What's the delimiter?

3

Taking a wild guess that it's tabs which may or may not contain carriage returns:

awk -F"\t" -v OFS="|" '{ gsub(/\r/,""); $1=$1 }1' inputfile > outputfile

outputfile must not be the same as inputfile.

4

Thanks for the quick reply, Inputs are in Excel. and i need your help in converting this excel to .csv file.

--- Post updated at 05:30 PM ---

After converting the Excel File to .csv file

Excel -

Serial Number	Serial Name	   Serial Brand
111                   test                     sample
123                   test2                   sample1
134                  134_test3             sample2
135                  135_test4            sample3

Desired output in CSV File - ( | delimiter)

Serial Number|Serial Name	|Serial Brand
111|test|sample
123|test2|sample1
134|test3|sample2
135|test4|sample3

if you see the row 3 and row4, the "serial number_ " is ignored.. if any.

5

CSV means "comma separated values". None of those files contains a single comma.

Once again I'm left to wild-guess that your text files are tab-separated.

awk -F"\t" -v OFS="|" '{ sub("^" $1 "_", "", $2); $2=$2 } 1' inputfile > outputfile
6

Well that's true. But if the separator is some other character than it's still called CSV regularly. Nevertheless, the shown source data is nothing I ever heard to be called "csv".

7

Attached is the sample Excel, How do i convert this Excel in to pipe delimiter flat file (.csv) through the script? looking to automate this.

8

What kind of excel? XLS or XLSX? Perl has common solutions for automating XLS, while XLSX is really difficult.

9

XLS. Thanks

10

I have this tool for converting XLS files to flatfiles:

#!/usr/bin/perl -w

use Spreadsheet::ParseExcel;
use strict;

my $filename = shift || "Book1.xls";
my $e = new Spreadsheet::ParseExcel;
my $eBook = $e->Parse($filename);
my $sheets = $eBook->{SheetCount};
my ($eSheet, $sheetName);

foreach my $sheet (0 .. $sheets - 1) {
    $eSheet = $eBook->{Worksheet}[$sheet];
    $sheetName = $eSheet->{Name};
#    print "#Worksheet $sheet: $sheetName\n";
    next unless (exists ($eSheet->{MaxRow}) and (exists ($eSheet->{MaxCol})));
    foreach my $row ($eSheet->{MinRow} .. $eSheet->{MaxRow}) {
        foreach my $column ($eSheet->{MinCol} .. $eSheet->{MaxCol}) {
            if (defined $eSheet->{Cells}[$row][$column])
            {
                print $eSheet->{Cells}[$row][$column]->Value . "|";
            } else {
                print "|";
            }
        }
        print "\n";
    }
}

You will have to install the Perl module Spreadsheet::ParseExcel. Sorry. No matter how you do it you have to install something to handle XLS in UNIX.

Using it:

$ ./xlstoflat.pl ./xlstest.xls | awk -F"|" -v OFS="|" '{ sub("^" $1 "_", "", $2); } 1'
Serial Number|Serial Name|Serial Brand|
111|test|sample|
123|test2|sample1|
134|test3|sample2|
135|test4|sample3|

$
1 Like