Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Fixed-Width file from Oracle

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Fixed-Width file from Oracle
# 1  
Old 04-20-2010
Fixed-Width file from Oracle
Hi All,

I have created a script which generates FIXED-WIDTH file by executing Oracle query.

Code:
 
SELECT RPAD(NVL(col1,CHR(9)),20)||NVL(col2,CHR(9))||NVL(col3,CHR(9) FROM XYZ

It generates the data file with proper alignment. But if same file i transfer to windows server or Mainframe server, it comes with improper alignment. I have tried to change from CHR(9) to ' '. But still problem exist. Can someone help me to solve it.

Regards,
ACE
# 2  
Old 04-20-2010
try to put a delimiter , say a pipe in between the columns and try to replace it with a space once the file is transfered to the wondows machine.
# 3  
Old 04-20-2010
Quote:
Originally Posted by Amit.Sagpariya
...
I have created a script which generates FIXED-WIDTH file by executing Oracle query.

Code:
 
SELECT RPAD(NVL(col1,CHR(9)),20)||NVL(col2,CHR(9))||NVL(col3,CHR(9) FROM XYZ

It generates the data file with proper alignment....
Well I don't see a properly aligned output at all -

Code:
SQL> 
SQL> 
SQL> desc t
 Name                       Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                            VARCHAR2(10)
 COL2                            VARCHAR2(10)
 COL3                            VARCHAR2(10)

SQL> 
SQL> select rownum, t.* from t;

    ROWNUM COL1       COL2     COL3
---------- ---------- ---------- ----------
     1 a          b      c
     2 a          b
     3 a             c
     4          b      c
     5 a
     6          b
     7             c
     8

8 rows selected.

SQL> 
SQL> SELECT RPAD(NVL(col1,CHR(9)),20)||NVL(col2,CHR(9))||NVL(col3,CHR(9)) x FROM t;

X
--------------------------------------------------------------------------------
a            bc
a            b    
a                c
               bc
a                    
               b    
                   c
                       

8 rows selected.

SQL> 
SQL>

Maybe you wanted to do something like this ?

Code:
SQL> 
SQL> var filler varchar2(1)
SQL> 
SQL> exec :filler := '~';

PL/SQL procedure successfully completed.

SQL> 
SQL> SELECT RPAD(NVL(col1,:filler),20)||RPAD(NVL(col2,:filler),20)||RPAD(NVL(col3,:filler),20) x FROM t;

X
--------------------------------------------------------------------------------
a            b            c
a            b            ~
a            ~            c
~            b            c
a            ~            ~
~            b            ~
~            ~            c
~            ~            ~

8 rows selected.

SQL> 
SQL> exec :filler := ' ';

PL/SQL procedure successfully completed.

SQL> 
SQL> SELECT RPAD(NVL(col1,:filler),20)||RPAD(NVL(col2,:filler),20)||RPAD(NVL(col3,:filler),20) x FROM t;

X
--------------------------------------------------------------------------------
a            b            c
a            b
a                    c
            b            c
a
            b
                    c


8 rows selected.

SQL> 
SQL> exec :filler := '#';

PL/SQL procedure successfully completed.

SQL> 
SQL> SELECT RPAD(NVL(col1,:filler),20)||RPAD(NVL(col2,:filler),20)||RPAD(NVL(col3,:filler),20) x FROM t;

X
--------------------------------------------------------------------------------
a            b            c
a            b            #
a            #            c
#            b            c
a            #            #
#            b            #
#            #            c
#            #            #

8 rows selected.

SQL>

tyler_durden

NB: this textbox's formatting options mess up the 2nd case when the bind variable is a blank space; it should work fine on command line sqlplus. Of course, the font in your viewer application (text editor or sqlplus terminal window etc.) must be fixed width.
# 4  
Old 04-20-2010
Sorry, initially i forgot to give data length of each column

Code:
COL1 VARCHAR2(20)
COL2 VARCHAR2(1)
COL3 VARCHAR2(1)

# 5  
Old 04-20-2010
Quote:
Originally Posted by Amit.Sagpariya
Sorry, initially i forgot to give data length of each column

Code:
COL1 VARCHAR2(20)
COL2 VARCHAR2(1)
COL3 VARCHAR2(1)

That doesn't matter, the idea is still the same. Adapt the query to your table structure.

tyler_durden
# 6  
Old 04-20-2010
FTP transfers aside, Oracle's default colsep character is usually a space, but could vary on your system if the DBA modified it. Meanwhile, tab is sometimes replaced by 3 spaces in some terminals, which only compounds the problem. Is there any reason you'd want to use nvl() to impose a tab character on a blank column? As tyler suggested, use a character to represent a NULL value, but I would recommend not using a typical delimiter, such as tab, space or pipe...
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Replace using awk on fixed width file.

All, I used to use following command to replace specific location in a fixed width file. Recently looks like my command stopped working as intended. We are on AIX unix. awk 'function repl(s,f,t,v) { return substr(s,1,f-1) sprintf("%-*s", t-f+1, v) substr(s,t+1) } NR<=10 {... (3 Replies)
Discussion started by: pinnacle
3 Replies

2. Shell Programming and Scripting

Fixed Width file creation from csv

Hello All, I'm able to achieve my goal of creating a fixed width file from a comma delimited but I know I'm not doing it as efficiently as possible. Original File checksab 004429876883,O,342040,981.98,10232014 004429876883,O,322389,2615.00,10232014... (6 Replies)
Discussion started by: aahlrich
6 Replies

3. Shell Programming and Scripting

Alter Fixed Width File

Thank u so much .Its working fine as expected. ---------- Post updated at 03:41 PM ---------- Previous update was at 01:46 PM ---------- I need one more help. I have another file(fixed length) that will get negative value (ex:-00000000003000) in postion (98 - 112) then i have to... (6 Replies)
Discussion started by: vinus
6 Replies

4. UNIX for Dummies Questions & Answers

Length of a fixed width file

I have a fixed width file of length 53. when is try to get the lengh of the record of that file i get 2 different answers. awk '{print length;exit}' <File_name> The above code gives me length 50. wc -L <File_name> The above code gives me length 53. Please clarify on... (2 Replies)
Discussion started by: Amrutha24
2 Replies

5. Shell Programming and Scripting

Comparing two fixed width file

Hi Guys I am checking the treads to get the answer but i am not able to get the answer for my question. I have two files. First file is a pattern file and the second file is the file i want to search in it. Output will be the lines from file2. File1: P2797f12af 44751228... (10 Replies)
Discussion started by: anshul_er
10 Replies

6. Shell Programming and Scripting

sorting a fixed width seq file

I have a file like this... 2183842512010-11-25 15379043 453130325 2386225062010-11-30 4946518 495952336 2386225062010-11-30 4946518 495952345 2386225062010-11-25 262066688 -516224026 2679350512010-11-25 262066688 -516224124 3196089062010-11-25 262066688 203238229... (5 Replies)
Discussion started by: issaq84mohd
5 Replies

7. Shell Programming and Scripting

summing up the fields in fixed width file

Hi, I have a fixed width file with some records as given below: " 1000Nalsdjflj243324jljlj" "-0300Njfowjljl309933fsf" " 0010Njsfsjklj342344fsl" I want to sum-up first field values(i.e from 2nd character to 6th character)of each record. so for the above file i want to add (1000 - 300+... (2 Replies)
Discussion started by: srilaxmi
2 Replies

8. UNIX Desktop Questions & Answers

Help with Fixed width File Parsing

I am trying to parse a Fixed width file with data as below. I am trying to assign column values from each record to variables. When I parse the data, the spaces in all coumns are dropped. I would like to retain the spaces as part of the dat stored in the variables. Any help is appreciated. I... (4 Replies)
Discussion started by: sate911
4 Replies

9. Shell Programming and Scripting

adding delimiter to a fixed width file

Hi , I have a file : CSCH74000.00 CSCH74000.00 CSCH74100.00 CSCH74000.00 CSCH74100.00 CSCH74000.00 CSCH74000.00 CSCH74100.00 CSCH74100.00 CSCH74100.00 I have to put a delimiter( say comma) in between after 6th character: CSCH74,000.00 CSCH74,000.00 CSCH74,100.00 (2 Replies)
Discussion started by: sumeet
2 Replies

10. UNIX for Dummies Questions & Answers

Fixed Width file using AWK

I am using the following command at the Unix prompt to make my 'infile' into a fixed width file of 100 characters. awk '{printf "%-100s\n",$0}' infile > outfile However, there are some records with a special character "©" These records are using 3 characters in place of one and my record... (2 Replies)
Discussion started by: alok.benjwal
2 Replies
Login or Register to Ask a Question