Shell Programming and Scripting

This problem is only from the Excel side.
If you simply create a sample excel file, with leading zeroes, they will disappears as useless in type number.
Then try to change the cells format to text , and add extra leadings zeroes it's ok. But once this sheet is saved as csv and reloaded , what happens ? Nomore leading zeroes again.
The trouble is the leading zeroes are a matter of format, not saved with CSV file type.

A simple workaround is to save the file as .txt . Excel will import it interactively, so you can select each column format.
But all this is completely outside the scope of this forum .

Might be double quoting the leading zeroes number fields would help?

the sed code seems to be working fine but its throwing me off when the data contains double quotes within them .I have attached picture of what I am talking about.

Code:

sed 's/|/","/g; s/^/"/; s/$/"/' A4.txt > A5.csv

I know the sed code protectively quotes the fields with double quotes to help converting the pipe delim to commas delimited when there are commas within the fields.

I need a solution or change in the sed command to accommodate the situation where my input file A4.txt (attached file) contains fields which are encased as

Code:

="0123"

inorder to prevent dropping of leading zeroes.

I have attached the picture for same (A4.txt) .I can send the input file as well..

As I explained previously, CSV files don't preserve the cells formats, and you need them to keep leading zeroes.
So I suggest as a workaround : generate Excel XML files from your application.
The required converting script can be easily done with awk.

You can easily get a template by exporting a excel sheet .
Under Excel, just save as "Excel 2003 xml sheet" , or something close to this .( I'm using Excel 2003 in French )
Starting from this file,you can create a good template by removing useless tags.

See the attached file, I just create using Excel et Notepad++.

Of course you may have to change some file associations if you wish to open it directly under Excel.

Jean-Paul

thanks blastit but unfortunately my requiremenst dont allow me to do a xml and thhe conversion..I ahve figured out how to prevent the dropping of leading zeroes but I need a SED code that would work when data contains double quotes and commas within them to convert the pipe delim to csv.

It would be great if we can come up with any sed/awk solution.