Thursday, November 25, 2010

How to Export without junk characters in FastExport?

How to Export without junk characters in FastExport?

Solution1: Create a small OUTMOD to strip off the two-byte VARCHAR length field.

Solution2: Another alternative if you are in a UNIX environment is to use AWK or SED or CUT the file after it has been output to strip the leading to characters from each record.

STEP1: Write the query in FASTEXPORT file as follows

SELECT '|'||Col1||';'||Col2 from TAB1; -- Here '|' pipe operator is used as delimiter.
So result generated will be as follows
^A|Col1;Col2

"^A" is a junk value generated during EXPORT.

STEP2: Write UNIX script and pass filename as first parameter

#####UNIX SOLUTION #############
##create a file trim.ksh ############
## Run the file trim.ksh <user_filename>###

FileName=$1
cat $FileName | cut -f2- -d '|' > $FileName.tmp
mv $FileName.tmp $Join_Export.txt1

The Result after cutting all junk characters till occurrence of PIPE operation will be as follows
Col1;Col2

Solution 3: by casting Entire result to CHAR

When we are exporting a table in record mode , Please make sure that data exported does not contain extra 2 characters by casting entire result to CHAR().

For example: if table1 has 2 columns, Col1 is of type char (6), Col2 is of type char (2)
Write the query as follows:

Select cast (c1 || c2) as char (8) from table1;

Note: If we write query like “select c1 || c2 from table1",
The records exported will contain 2 byte length at the beginning, because concatenation returns VARCHAR () type not CHAR () type.

6 comments:

  1. The following provides result without any junk values.

    Step 1: use Format 'TEXT' and MODE 'RECORD'
    example:
    .export outfile fexp_ACH_DOM.txt Format TEXT MODE RECORD;
    Step 2:cast the individual fields to char(n) and finally entire list of concatenated columns to char(n)
    example:Select CAST(CAST (A_Number AS CHAR(50))||
    CAST (B_Number AS CHAR(48))) AS CHAR(98)) From Table;

    ReplyDelete
  2. thanks for suggestions deep, the scenario i have mentioned is for unix systems . In windows it is straight and simple!!

    best regards
    vinay SHet

    ReplyDelete
  3. how to remove extra characters in windows??

    ReplyDelete
    Replies
    1. Hi,

      You will have to implement Fastexport in windows Perl script and implement the same logic.

      Also make sure to "SET INDICDATA OFF"

      Delete
  4. Hi, I found your post helpful, and I tried your command cat $FileName | cut -f2- -d '|' > $FileName.tmp to eliminate junk characters.

    But for some rows, I can see beginning column name appended with "|". Could you please provide me help to resolve the issue to avoid junk characters in fexp data.

    Thanks In Advance.

    ReplyDelete