Visit Our UNIX and Linux User Community


Issues in the Format in excel file generated from unix machine


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Issues in the Format in excel file generated from unix machine
# 1  
Old 12-11-2007
Issues in the Format in excel file generated from unix machine

Hi,

I have generated a report that contains many columns and since I need ir in excel format.. I just renamed te file to excel as follows:

cp vijay.txt vijay.xls

I have just attached this spreadsheet in the mail and I am getting it to my mail id. But, in the output excel, the columns that have text xharacters are displayed correctly. Example:

Office name Office number employee name emp number
----------- -------------- --------------- --------------
trichy 012345 vijay 82389
chennai 008393 arun 00123
madurai 037489 vaas 82738
salem 134567 allen 92389

here...
the excel shows the second column as

12345
8393
37489
134567

excluding the zeros .. also in the last column

00123 as 123.

I need these zeros to be included in the excel output..

Is there any way this can be obtained?
Please help me to resolve this issue.

Thanks,
Vijayakumar.
# 2  
Old 12-11-2007
You could either tell excel that the column is text or just put " symbols round the number.
# 3  
Old 12-11-2007
Quote:
Originally Posted by Vijay06
cp vijay.txt vijay.xls
I give up. Smilie

You should be doing "cp vijay.txt vijay.csv"

XLS is for Microsoft Excel proprietary binary format files.
# 4  
Old 12-12-2007
Quote:
00123 as 123.

I need these zeros to be included in the excel output..
You got to specify the Column data format of the field as "Text" and not as "General"

that would consider the numerals as strings and the leading zeroes would remain as such
# 5  
Old 12-12-2007
Any Unix commands for formatting excel columns???

Thanks for the reply.
Yeah.. U r right....!!
But, every time the report gets generated i need to open it and change the format....rite?

that does not solve the issue... I am going o schedule it in cron..

is there any way to format that excel colums as "text" or "general" in the unix machines itself.

If there are any such, it would help me....


Thanks,
Vijay.
# 6  
Old 12-12-2007
You can force excel to treat numbers as text by using...
Code:
chennai,="008393",arun,="00123"

# 7  
Old 12-12-2007
Quote:
Originally Posted by Smiling Dragon
You could either tell excel that the column is text or just put " symbols round the number.
I think Dragon is on to the right answer.

if Excel finds stuff in quotes it should keep it as text.

Previous Thread | Next Thread
Test Your Knowledge in Computers #435
Difficulty: Medium
Angular and AngularJS are web application frameworks to use for developing multi-page web applications.
True or False?

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

How to change the format of an Excel from exponential to text through UNIX command?

How to change the format of an excel from exponential to text through UNIX command We have a pipe delimited file in which one particular A column is a combination of number+text and while converting into excel using tr command it is generating a exponential data for the A column. Kindly... (2 Replies)
Discussion started by: AbiramiRaja
2 Replies

2. Shell Programming and Scripting

Script to generate Excel file or to SQL output data to Excel format/tabular format

Hi , i am generating some data by firing sql query with connecting to the database by my solaris box. The below one should be the header line of my excel ,here its coming in separate row. TO_CHAR(C. CURR_EMP_NO ---------- --------------- LST_NM... (6 Replies)
Discussion started by: dani1234
6 Replies

3. UNIX for Advanced & Expert Users

How to export Result to Excel Tabular format from UNIX?

Hi I am working on a script in which I am firing a query on database through Unix and getting the result set. I want to export that in an excel file. I am able to do so nut the result are exported horizontally one below the other. Can anyone plss help me out in exporting the Result in Tabular... (4 Replies)
Discussion started by: Saritau3
4 Replies

4. Shell Programming and Scripting

Perl :Is it possible to read the excel 2007 sheet on unix machine using spredsheet::xlsx module

I have an Excel 2007 excel sheet on windows machine and using Spreadsheet::XLSX I had written a script to read the excel sheet and was successful. My requirement is I need to generate another excel sheet from the old excel 2007 sheet on unix machine. Now is it possible to read the excel... (2 Replies)
Discussion started by: giridhar276
2 Replies

5. Shell Programming and Scripting

Help with selecting column with awk for a txt file generated by excel

I am new to scripting/programming, so I apologize for any novice questions. I have a tab delimited text file that was saved from excel xls file. I am trying to select only the third column using awk command. My command line is as below: cat test.txt | awk '{print $3}' However, above... (8 Replies)
Discussion started by: SangLad
8 Replies

6. Shell Programming and Scripting

Retaining the Unix CSV format in Excel format while exporting

Hi All, I have created a Unix Shell script whch creates a *.csv file and export it to Excel. The problem i am facing is that Users wants one of the AMOUNT field in comma separted values. Example : if the Amount has the value as 3000000 User wants to be in 3,000,000 format. This Amount format... (2 Replies)
Discussion started by: rawat_me01
2 Replies

7. UNIX for Dummies Questions & Answers

Save Excel file as .txt in UNIX format

I have some files created in Excel that have to be saved as .txt files in order to load them into our accounting system. I can save the files as .txt files through Excel, but I then have to open them in TextPad and do a save as to change the Format from PC to UNIX. Is there a way to skip this step... (2 Replies)
Discussion started by: jroyalty
2 Replies

8. HP-UX

Conver Excel file to another format ( text)

I can convert a excel file to another format , for example text type ( TXT) whit anoter caracter from delimiter a rows ??? I can change the default delimiter " | " ( or space ) of cell to another caracter ( for example @@ or ## ) without change the other space ??? (6 Replies)
Discussion started by: ZINGARO
6 Replies

9. Programming

how to add CRLF support for CSV file generated in unix

Helo, my server side system is running on a redhat linux o.s. I have c program on the server which export list file into CSV format. now, I want that my program on server side is able to add support for CRLF(carriage return Line feed)into csv file format. so how do i write C program whcih... (3 Replies)
Discussion started by: amitpansuria
3 Replies

10. Shell Programming and Scripting

How to format excel sheets in UNIX??

Hi, I have generated an excel sheet using a shell script. i have converted the output text file to an excel and got the desired output. However, in a particular column in the excel the values of the numbers start with 0. e.g. 078393343, 00342442, etc. But, in the resulting excel I get as... (2 Replies)
Discussion started by: Vijay06
2 Replies

Featured Tech Videos