Square like special characters in .csv file


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Square like special characters in .csv file
# 1  
Old 12-21-2012
Question 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:
Code:
 
#!/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:
Code:
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!

Moderator's Comments:
Mod Comment Please view this code tag video for how to use code tags when posting code and data.

Last edited by vbe; 12-21-2012 at 12:31 PM..
# 2  
Old 12-21-2012
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...)
This User Gave Thanks to vbe For This Post:
# 3  
Old 12-21-2012
Hi vbe -
Thanks for your prompt reply. I donot expect you to reply only today . Enjoy your weekend.

Code:
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
Code:
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!

Moderator's Comments:
Mod Comment Please use code tags for code and data

Last edited by Scrutinizer; 12-21-2012 at 04:46 PM.. Reason: code tags; removed excess font formatting
# 4  
Old 12-21-2012
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:-
Code:
od -c FeedAlert.csv

OR
Code:
cat -Ev FeedAlert.csv

This User Gave Thanks to Yoda For This Post:
# 5  
Old 12-28-2012
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
# 6  
Old 12-28-2012
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?
Code:
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:
Code:
uuencode FeedAlert.csv FeedAlert.csv | mailx -r ${EMAIL_FROM_ADDRESS} -m -s \
'Alert : Data Feed - Missing Data for Today' xyz@yahoo.com

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

Naming file with special characters ?

Hi all, I have a problem with file naming in linux. I have to create a file named like 11/22.csv but shell displays error: no such file or dir. Problem is / character in the file name. I searched unix linux naming concepts and it's restricted in OS. Please tell me if there's any other chance? OS... (3 Replies)
Discussion started by: sembii
3 Replies

2. Red Hat

Special control characters in file

Hi Guys, We receive some huge files on to Linux server. Source system use FTP mechanism to transfer these files on our server. Occasionally one record is getting corrupted while transfer, some control characters are injecting into the file. How to fix this issue ? please advice ? Sample... (2 Replies)
Discussion started by: srikanth38
2 Replies

3. Shell Programming and Scripting

Substitute special Characters into a file

Hi experts :) I need to replace special characters into a file , in the followiing way : " --> "" ' --> '' _--> \_ I tried with the sed command but I'm getting and error ksh: $: not found. ksh: $: not found. sed: Function s/\/\/ cannot be parsed. Any idea ? Thanks , KOLAS... (2 Replies)
Discussion started by: Kolas79
2 Replies

4. Red Hat

Special character ^@ in CSV file

All, I am having a tough time with Linux and CSV file. My CSV file gets generated from Cognos on Linux machine that contains special characters. At first instance when I do vi <filename> to that file, I can't see anything. I did tail -2 and redirected to another temp file and did vi <filename>,... (2 Replies)
Discussion started by: donadarsh
2 Replies

5. Shell Programming and Scripting

File containing special characters

Hello All, I am facing challenges in order to transfer a file from windows to unix box,the file contains a special character '×' ,now when I am transferring the file from windows to unix that special character converted to something else like 'Ã' ,another thing I have noticed that the hardware is... (1 Reply)
Discussion started by: prarat
1 Replies

6. Shell Programming and Scripting

Problem with Special characters in file

Hi, I am facing a below problem. Inorder to mak sure the below file is fixed width i am using the following command awk '{printf("%-375s\n", $0) } so as to add trailing spaces at the end for records of length less than 375. Input file > inp.txt 1©1234 1234 123©1 The output file is... (1 Reply)
Discussion started by: marcus_kosaman
1 Replies

7. Shell Programming and Scripting

Is there anyway to grep any special characters from a file ?

Is there any command or shell script to grep any special character from a file ? I have a huge file containing millions of user names; the requirement is to find names containing special characters. #!/bin/bash for i in `cat username.txt` do #COMMAND to grep special character done ... (3 Replies)
Discussion started by: poga
3 Replies

8. Shell Programming and Scripting

Removing special characters in file

I have file special.txt with the following data. <header info> 123$ty5%98&0asd 1@356fgbv78 09*&^5jkns43( ...........some more rows. In my output file, I want to eliminate all the special characters in my file and I want all other data. need some help. (6 Replies)
Discussion started by: srivsn
6 Replies

9. UNIX for Dummies Questions & Answers

Replace Special characters in a file

Hi, I have a data like this in a file, 402003279034002000100147626030003300010000000000002000029000000 ær^M^\MÍW^H I need to replace those special char to some other char like # or $ Is there any ways to do it... I tried commands tr,sed and many but it was not able to replace because... (1 Reply)
Discussion started by: solai
1 Replies

10. UNIX for Dummies Questions & Answers

how to see special characters in a file using vi

Hi, I have a file which has special characters. I can't see them when I "vi" the file. But I am sure there are some special un seen characters. How can I see them? Please help. Thx (6 Replies)
Discussion started by: jingi1234
6 Replies
Login or Register to Ask a Question