2

I'm using the xls2csv binary in order to translate XLS doc to CSV on my Red Hat Linux machine.

For example: (from the man page):

 xls2csv -x "1252spreadsheet.xls" -b WINDOWS-1252 -c "ut8csvfile.csv" -a UTF-8

But I noticed the following things, which cause problems in my Bash script:

  1. CSV out includes unnecessary spaces (on the left side of word or on the right side of word)

    Example of wrong syntax in CSV:

     ,"/var/adm/sys ldd/all  /Comm/logs   ","WORD "," WORD"
    

    Example of right syntax in CSV:

     ,"/var/adm/sys ldd/all  /Comm/logs",WORD,WORD
    
  2. Quotation marks appear in the CSV when they are not required:

    Example of wrong syntax in CSV:

     ," WORD ",
    

    Example of right syntax in csv

     ,WORD,
    

How can I change the output to create a "clean" CSV file?


I'm looking for a awk/sed/perl one liner, or any other solution that will work in a Bash script.

Example of CSV file before the fix:

 1,"/var/adm/sys ldd/all  /Comm/logs",34356,"234245 ",24245
 2,"/var/adm/sys ldd/all
 /Comm/debugs.txt"," 45356",435,"  578 58976  "
 3,"   add this line in crontab    :",34356,"234245 ",24245
 4,"1.0348    54 35.5"," 45356","   435","578 "
 4,"1 2 "," 45356 95857 ","   435","578 "
 5,"1 2 "," 45356 95857 ","   "435","578" "
 6,"1.0348    54 35.5"," 45356"," "4"""    ""35","578 "
 7,"1.0348    54 35.5",""45356",""4"""""35,"578 "

Example of corrected CSV file (after the fix):

 1,"/var/adm/sys ldd/all  /Comm/logs",34356,234245,24245
 2,"/var/adm/sys ldd/all
 /Comm/debugs.txt",45356,435,"578 58976"
 3,"add this line in crontab    :",34356,234245,24245
 4,"1.0348    54 35.5",45356,435,578 
 4,"1 2","45356 95857",435,578
 5,"1 2","45356 95857","435,578" 
 6,"1.0348    54 35.5",45356,"4"""    ""35,578
 7,"1.0348    54 35.5",""45356",""4"""""35,578

Commas cannot appear within fields.

Note the explicit newline contained within a field of line 2.

When a field is within double quotes and contains no white space (e.g. line 7 ""45356"), those double quotes must not be removed because that whole field including those quotes is an encoded password.

closed as off-topic by lgeorget, GAD3R, countermode, xhienne, Tim Kennedy Mar 28 '17 at 14:12

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "This question has been posted on multiple sites. Cross-posting is strongly discouraged; see the help center and community FAQ for more information." – lgeorget, GAD3R, countermode, xhienne, Tim Kennedy

  • Probably not the solution to your problem, but I think Excel does a better job exporting xls to CSV, did you tried that? – RSFalcon7 Jul 28 '14 at 16:11
  • I use the xls2csv in my bash script , this is the only way to translete XLS to CSV from shell script ( I cant do manual from XLS doc ) the concept here is to automate the proccess – maihabunash Jul 28 '14 at 16:14
  • the problem or the source of the problem here is because the original XLS file have space near the word/s so later I get the space also in CSV , other solution can be with the xls2csv command , but I dont know how to flag this command in order to disable spaces before or after the word , – maihabunash Jul 28 '14 at 16:19
  • 1
    Why can't you accept this syntax on your CSV? – RSFalcon7 Jul 28 '14 at 16:26
  • 4
    I think your problem is that you're processing CSV in bash. You should be using a proper CSV parser that can handle these "problems" you're having. For example, reading the file in a "while read" loop will make it hard to handle embedded newlines (line 2) or lines with commas inside quotes (not present in your example data but likely in the real world). – glenn jackman Jul 28 '14 at 16:38
0

This perl code produces almost exactly the expected output:

use Text::CSV;

my $csv = Text::CSV->new({ binary => 1, eol => $/, allow_loose_quotes => 1, escape_char => undef });

open my $io, "<", $ARGV[0] or die;

while (my $row = $csv->getline ($io)) {
        my @o = map { $_ =~ s,^\s*,,; $_ =~ s,\s*$,,; $_; } @{$row};
        $csv->print(STDOUT, \@o);
}

output is

1,"/var/adm/sys ldd/all  /Comm/logs",34356,234245,24245
2,"/var/adm/sys ldd/all
/Comm/debugs.txt",45356,435,"578 58976"
3,"add this line in crontab    :",34356,234245,24245
4,"1.0348    54 35.5",45356,435,578
4,"1 2","45356 95857",435,578
5,"1 2","45356 95857",""435","578""
6,"1.0348    54 35.5",45356,""4"""    ""35",578
7,"1.0348    54 35.5",""45356",""4"""""35,"578"
  • 1
    Don't actually need map and the intermediate variable here. Can s,(^\s+|\s+$),,g for @$row; instead – Sobrique Feb 10 '16 at 20:29

Not the answer you're looking for? Browse other questions tagged or ask your own question.