Ho to remove leading zeros from a csv file which is sent from a UNIX script

Hi All,

I am using a informatica job to create a csv file and a unix script the mail the generated file.Everything is working fine but I am not seeing leading zeros in the csv file sent in the mail.These zeros were present when the .csv file was generated by informatica procees.

Is there any command which I can use in shelll script which will dfisplay that?Please help

fdate=`tail -1 abc.csv | cut -c1-8`
SUBJECT="execp : ${ENV} for ${fdate}"
destname=file_$fdate
sed 's/#BUSINESS/BUSINESS/1' abc.csv > abc2.csv
mv abc2.csv abc.csv
line_count=`wc -l abc.csv|awk '{print $1}'`
echo $line_count
if [ $line_count -gt 1 ]
then
(echo "Hi,Team";uuencode abc.csv $destname.csv) | /usr/bin/mailx -s  "${SUBJECT}"  ${TO} ${CC}

As there are no steps eating leading zeroes in above script, where are you "not seeing leading zeros"? In $destname.csv ? On the destination host? In a spread sheet progeam?

Hi Rudi,

This unix script mails the .csv file which is already created by a informatica process.I am not seeing leading zeros when the script mails the .csv file and when we open the mail attachement.
Those zeros are present when the csv was created

and how do you open the mailed file?

it comes in as an attachement.Double click

Please post (shrunk but meaningful) samples of abc.csv , $destname.csv , and the target file on the remote host.

heh - good answer.
You open it with Excel?
Try opening with the notepad and see

EX:000256 in abc.csv becomes 256 when i open the mail attachement.

I do not get an option to open the attachement using notepad using outlook

I believe it's Excel eating up the leading zero-s.
Right click on attachment and open with something other than Excel.
Or save the attachment and then edit the saved file.

Could you create a sed script with two commands?

s/^0*//
s/,00*/,/g

That should take out leading zeros at the beginning of the line or following any comma.

Shortly:
You have csv file which is something like:

00001,0012
...

And when you open in Excel by clicking your email attachment, the looks like:

1,12
...

It's Excel import default. I have not found any setup in Excel where you can change those defaults. So: M$ knows better what you need as you :). If it looks number then it's number and you don't need leading 0's. If excel import string which include only numbers then it can't be string ...

If you like to keep those leading 0's in numbered string then you need import file to Excel:

• save your Attachment to some.csv
• Import a text file by opening it in Excel
• use wizard
• setup delimiter, setup those columns to text type, not default
  And you have leading 0's. So they I have planned in Seattle. It have to be difficult to say that this is string, not number.

You can use example ssconvert to convert csv to xls including some defaults ..., but if you change those fields to excel string formattadding ' before values '00001,'00012 then ssconvert ex2.csv ex2.xls 2>/dev/null fields look nice including 0's and send it.

ssconvert can be installed to the Ubuntu and Debian:

apt-get install gnumeric

The best solution:

easy install csv2xlsx  xlsx2csv
csv2xlsx --delimiter "," 'utf-8' SheetName < ex1.csv > ex1.xlsx

Of course, adjusting the fields so that it looks correct in Excel (or other sreadsheet programs which do the same) means that you won't be able to do any calculations on them.

You might be better to import the data as real data without any adjustments and then format the cells (or the whole sheet if you want) to fix the width of the number including leading zeros. That way the display will be correct and the value will be still numeric so you can perform calculations.

Does that help?

Robin