Removing special characters,spaces,nulls in multiple columns

developeretl · Post by **developeretl** » Tue Jun 28, 2011 12:45 pm

Hi,

I have flat files , transformed and moved to staging.
As output files are moved from datastage server to staging folder. when we are trying to load the data we are seeing special characters.
For this we have used pad char environment variable and trim d but we have atleast 10 columns .
My question is that is there any simple way to remove the special characters in datastage using sed or awk or shell script.

kpsita · Post by **kpsita** » Tue Jun 28, 2011 1:14 pm

You can remove special characters in datastage using Convert function.

developeretl · Post by **developeretl** » Tue Jun 28, 2011 1:34 pm

Can you please tell me whether I can use it for mutiple columns. I have atleast 20 coloumns and it is time consuming for me to change in each job. Is there any way to make it global or any unix command to be used.

mhester · Post by **mhester** » Tue Jun 28, 2011 1:53 pm

There are really many ways to do this, but I am not certain that convert would be the best method. You could -

Write a C routine to remove unprintable characters
Write a sed/awk command that will do the same and invoke via a sequencer or during sourcing when using a sequential file
You could import your sequential file into one column by using a delimiter not present in any row and then operate on that one column and then write it out using the correct metadata.

I have used all of these methods and it really depends on what you require. Is it just 1 or 2 occurrences of an unprintable character or is it random and you need to check for all unprintable characters.

developeretl · Post by **developeretl** » Tue Jun 28, 2011 1:57 pm

All the columns have special characters.

mhester · Post by **mhester** » Tue Jun 28, 2011 2:04 pm

Right, but is it 1 or 2 special characters that keep showing up or are they random?

developeretl · Post by **developeretl** » Tue Jun 28, 2011 2:20 pm

Can you please provide me any sed or awk command?

mhester · Post by **mhester** » Tue Jun 28, 2011 2:55 pm

Maybe something like -

Code: Select all

 sed "s/[^a-z|0-9]//g;" file1 > file2

or the tr command will work, awk as well, but you will likely have to call a sub process

Again, there are a lot of ways to do this - this is just one of many.

ray.wurlod · Post by **ray.wurlod** » Tue Jun 28, 2011 3:11 pm

What makes them "special"? There are NO special characters. Every character is a character.

developeretl · Post by **developeretl** » Tue Jun 28, 2011 5:53 pm

For me the special characters are # ,@,!,%,&,* . I'm seeing this data in the file

ray.wurlod · Post by **ray.wurlod** » Tue Jun 28, 2011 6:24 pm

What's special about them? Nothing. You use @ every time you use an email address!

developeretl · Post by **developeretl** » Tue Jun 28, 2011 6:33 pm

Hi Ray,
Iam not experienced like to discuss more on that. Iam sorry if the terms used by me caused any concerns or my question is confusing. Iam new to the datawarehousing so i do not know what terms to be used in this forum so that it wont raises more questions while answering it. Please let me know if you have any list so that I should not follow in this forum.

mhester · Post by **mhester** » Tue Jun 28, 2011 8:18 pm

Well, characters like that have been called "special" since I started working in IT back in 1984 so I know what you mean and I doubt there is anyone who frequents this forum that did not understand what you meant.

You can still use the sed command I gave above and that will work with some tweaks. If you do a google search on removing/replacing "special" or unprintable characters there will be pages of examples which remove printable as well as unprintable.

The characters you want to remove are special

chulett · Post by **chulett** » Tue Jun 28, 2011 10:46 pm

I think that falls into the category of "pet peeve".

DSguru2B · Post by **DSguru2B** » Wed Jun 29, 2011 7:50 am

mhester wrote:Well, characters like that have been called "special" since I started working in IT back in 1984

1984 huh. I was still carrying around a sippi cup.

So many special character, that too in every column is weird. I havent been in IT for as long as Mike but even I can say that you need to investigate the origin of this data.
Nevertheless, you can use the CONVERT() function to remove these characters.
In some fields these characters maybe ok, like an email address or comment fields so use a scalpel approach rather than a machete.