Shell Programming and Scripting

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?

Code:

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:

Code:

00001,0012
...

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

Code:

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:

Code:

apt-get install gnumeric

The best solution:

Code:

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