Square like special characters in .csv file

Hi All -

I am new to unix and scripting. I was asked to export a sql output into .csv format and email the attachment. I could do this however i see some square like boxes or special char at the end of the headings and data values in .csv file. I could not fix this and will need your help.

the .ksh script i am using for your reference:

 
#!/bin/ksh
# Sample script 
# Author: xyz
# Initial - 11/07/2012
 
. ~prod/dba/oracle_connection_info
. ~prod/xyz-Feeds/common/setenv_common_cm.ksh
 
OUT_FILE_FEEDS=./FeedAlert.csv
 
LOG_FILE=$RS_LOG/FeedAlert.log
 
 
echo "Oracle SID : " $ORACLE_SID
echo "out file feeds windows service : " $OUT_FILE_FEEDS
 
sqlplus -s ${ORA_ID}/${ORA_PASS}@$ORACLE_SID < ~prod/xyz-Feeds/sql/Records.sql > FeedAlert.csv
 
cat FeedAlert.csv |uuencode FeedAlert.csv FeedAlert.csv | mailx -r ${EMAIL_FROM_ADDRESS} -s \
'Alert : Data Feed - Missing Data for Today' xyz@yahoo.com 
echo sent mail.
 
echo ending script
# End of script
 

Records.sql goes like this in another file:

SET LINESIZE 1500
SET PAGESIZE 100
SET COLSEP ','
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING ON;
 
 
SELECT * from emp;
 

I will really appreciate it if you can help me fix this . Thanks!

What UNIX are you working on? Can you show us a sample of what you mean? For from your script I see nothing wrong (but its friday afternoon just before leaving work...)
(Is it visible in unix or only in windows when looking at attachment...)

Thanks for your prompt reply. I donot expect you to reply only today . Enjoy your weekend.

This is Sun Solaris sun-Fire-V490 .

EMP_ID
UNQ_ID
WORK_CITY 
E
MAJOR_
UPDATED_O
UPDATEDON_TI
TEAM_END_
---------
-----------
--------------------
-
----------
---------
------------
---------

123456


888888888

NEW YORK 
N
E 

25-Jun-12

11:53:34 AM

25-Jun-12


567890


222222222

LONDON 
N
EQ 

27-Jul-12

12:37:30 PM

27-Jul-12

[/COLOR][/FONT]

The above is what i copied from .csv file attachment. The square like things are on there but when i copy they dont get copied over. Like here they are present after EMP_WORK_CITY(a square box) and the data values also have these boxes.

In unix : this is how it looks

EMP_ID,UNQ_ID,WORK_CITY ,E,CVG_MAJOR_,UPDATED_O,UPDATEDON_TI,TEAM_END_
---------,-----------,--------------------,-,----------,---------,------------,---------
000123456, 888888888,LONDON ,N,E ,23-JUL-12, 15:41:33 PM,15-MAY-08
000567890, 222222222,MONTREAL ,N,E ,23-JUL-12, 15:41:33 PM,15-MAY-06

unfortunately i cannot take a snapshot and paste here.hope this helps. I am doing the same routine for many other sqls and end up having same problem .Thanks!

I have a question, are you seeing these characters in file: FeedAlert.csv in Solaris as well or you are seeing them only when you are getting them in your inbox as attachment?

Can you please run below commands in Solaris & post results:-

od -c FeedAlert.csv 

OR

cat -Ev FeedAlert.csv 

Hi bipinajith -

Thanks for your reply.

I get these special characters only when i open the attachment from my mailbox not in solaris.

I could run od -c FeedAlert.csv and get something like this
Output :
0051140 - 1 2 , 1 2 : 3 7 : 3 0 P M
0051160 , 2 7 - J U L - 1 2 \r \n 0 0 0 8
0051200 9 3 5 3 8 , 9 9 9 7 3 8 0 2

I could not run cat -Ev FeedAlert.csv because i get an error cat : illegal option --E

I do see combination of carriage return and newline characters \r\n which is really strange because you said this file was generated in Solaris!

You can try running dos2unix on your CSV file to get these removed.

Also why are you running cat while sending email along with uuencode?

cat FeedAlert.csv |uuencode FeedAlert.csv FeedAlert.csv | mailx -r ${EMAIL_FROM_ADDRESS} -s \
'Alert : Data Feed - Missing Data for Today' xyz@yahoo.com 

This cat is unnecessary and can be avoided.

Also add -m option with mailx:

uuencode FeedAlert.csv FeedAlert.csv | mailx -r ${EMAIL_FROM_ADDRESS} -m -s \
'Alert : Data Feed - Missing Data for Today' xyz@yahoo.com