KSH - Text from input file truncated while converting it to excel


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting KSH - Text from input file truncated while converting it to excel
# 1  
Old 07-25-2011
KSH - Text from input file truncated while converting it to excel


Dear Members,

I am using the attached script to convert a input file delimited by '|' to excel.
However, while processing the attribute change_reason, the whole content of the text under change_reason is not displayed completely in the cell in excel. It is truncated after only first few words. May be this is because the attribute change_reason has new line character or I am not processing it properly.

This is the query which is extracting the records from a couple of tables:
Code:
        SELECT    'Start_rec:'        || request_id
         || ':'
         || request_action
         || ':'
         || employee.emp_first_name
         || ' '
         || employee.emp_family_name
         || ':'
         || Vvdisp
         || ':'
         || request_date || ':' || change_reason,
         REQUEST_INFO
    FROM requests, employee, validvalue
   WHERE requests.requested_by = employee.employee_id
            AND requests.request_status = validvalue.vvvalue
            AND validvalue.dmid = 'REQSTATUS'
            AND request_in_version = '880'
            AND request_action NOT IN ('DL','OP','ME','NE','NI','NS', 'DS')
            AND request_status = 'N'
       ORDER BY request_id;

Following is a sample from the first field (from start rec to change_reason):

Code:
Start_rec:2302:ND:Yoodit Didit:New Request:24-JUL-11:new domain for reports - copy from self 
Constant - value 56

In excel only this is what is displayed, for change_reason:
Code:
new domain for repo

Please help me resolve the above issue as I want the whole information irrespective of how many tabs or new line characters are present in the request.

Thank you all in anticipation.
# 2  
Old 07-25-2011
just use sqlplus to create a tab-delimited file for you. You can send the tab-delimited file directly to users. Excel will convert it. Correctly
Code:
set colsep '  '

The "space" between the ' ' came from pushing the tab key.

This avoids the problem you are having - users entering wierd characters into description fileds. Oracle takes them fine. Your code breaks when someone uses a '|' character, or maybe a & character.
This User Gave Thanks to jim mcnamara For This Post:
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Shell Script for converting file to Excel or CSV

Hi I have a dat file which has "n" number of columns. The file is delimited. The number of columns keep varying as the file is generated out of DB queries. Could you please help me in writing a script which will generate a XLS or CSV file out of the dat file. (5 Replies)
Discussion started by: Vee
5 Replies

2. Shell Programming and Scripting

Converting txt file to Excel file

Hi All, I have a text file with below content. TradeDate Name SecurityMnc ReasonDesc ======================================= 20120501 Robin ABC FO System Defect 20120502 Robin ABC FO System Defect I would want this in an excel file in 4 columns,... (3 Replies)
Discussion started by: robinbannis
3 Replies

3. Shell Programming and Scripting

Converting specific Excel file tabs to CSV in Python

Hi list, This is probably something really simple, but I am not particularly familiar with Python so I thought I would ask as I know that python has an excel module. I have an excel document with multiple tabs of data and graphs. One of the tabs is just data which I require to have dumped to... (8 Replies)
Discussion started by: landossa
8 Replies

4. Shell Programming and Scripting

Converting excel to '|' delimiter

Hi All, Can you please suggest me how to convert excel format to ‘|' delimiter file. I need to use perl script Thanks Rama (5 Replies)
Discussion started by: murari83.ds
5 Replies

5. UNIX for Dummies Questions & Answers

Converting a text file with irregular spacing into a space delimited text file?

I have a text file with irregular spacing between values which makes it really difficult to manipulate. Is there an easy way to convert it into a space delimited text file so that all the spaces, double spaces, triple spaces, tabs between numbers are converted into spaces. The file looks like this:... (5 Replies)
Discussion started by: evelibertine
5 Replies

6. Shell Programming and Scripting

Input from a file and converting the answer

Hello all, I am new to unix and i am having probelms. If anyone could help. I am trying to input numbers from a file called data.file The problem i am having is I can input the file but when I hit enter to get the answer onto my output file i get a error message (standard_in) 1: parse error. ... (3 Replies)
Discussion started by: nfsrider
3 Replies

7. UNIX for Dummies Questions & Answers

Converting binary file to readable format in Ksh

In Unix/Ksh, when I try to look inside a file it says that the file may be a binary file and if I want to see it anyway. When i say 'yes', it shows me the content filled with unreadable symbols (looks like binary). Is there a command that I can run from the Unix prompt to convert/translate that... (3 Replies)
Discussion started by: arthurs
3 Replies

8. UNIX for Advanced & Expert Users

Problem in converting password protected excel file to csv file in unix

I need to convert a password protected excel file which will be in UNIX server to a comma separated file. For this I need to open the excel file in UNIX box but the UNIX box doesn't prompt for password instead it is opened in an encrypted manner. I could manually ftp the excel file to local... (2 Replies)
Discussion started by: Devivish
2 Replies

9. Shell Programming and Scripting

text file to excel or csv

Hi, I have a text file like ---------------------- aaa|bbb|ccc|ddd|eee fff|gggg|hhhhhh|kkkk -------------------- I have a script which will transfer(thourgh FTP) this text file to windows system. But I want to covert it into excel or CSF then upload into windows system.. thanks (9 Replies)
Discussion started by: srikanthus2002
9 Replies

10. Shell Programming and Scripting

Converting excel to text

Is it possible to convert an excel file into a text file using standard HP UNIX commands? Possibly in K Shell? (5 Replies)
Discussion started by: stevefox
5 Replies
Login or Register to Ask a Question