Flat file-make field length equal to header length


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Flat file-make field length equal to header length
# 1  
Old 03-28-2012
Flat file-make field length equal to header length

Hello Everyone,

I am stuck with one issue while working on abstract flat file which i have to use as input and load data to table.

Input Data-

------ ------------------------ ---- -----------------
WFI001 Xxxxxx Control Work Item A Number of Records

------ ------------------------ ---- -------------------------
WFI001 Xxxxxx Control Work Item B Count of Distinct Hook Id

------ ------------------------ ---- -------------------
WFI001 Xxxxxx Control Work Item C Sum of Workitem Num

------ ---------------------- ---- -----------------
WFI008 Xxxxxx Control DocTABA A Number of Records

You can consider ----- as header for a field.
I cant starighforward load this file because if you check column values -they have spaces in them ;so i need to enclose indivisual column in double quotes.

Output i expect is
"WFI001" "Xxxxxx Control Work Item" "A" "Number of Records"
"WFI001" "Xxxxxx Control Work Item" "B" "Count of Distinct Hook Id "
"WFI001" "Xxxxxx Control Work Item" "C" "Sum of Workitem Num "
"WFI008" "Xxxxxx Control DocTABA" "A" "Number of Records "

Any help or suggestions in this query will be of great help.

Thanks!!!
# 2  
Old 03-28-2012
Code:
# sed '/^-\|^$/d' data
WFI001 Xxxxxx Control Work Item A Number of Records
WFI001 Xxxxxx Control Work Item B Count of Distinct Hook Id
WFI001 Xxxxxx Control Work Item C Sum of Workitem Num
WFI008 Xxxxxx Control DocTABA A Number of Records

# 3  
Old 03-28-2012
seems difficult without having a column delimiter in data file...
# 4  
Old 03-28-2012
I know;if delimeter would have been there it would have been peice of cake --
@ygemici,any idea how to put double quotes around column 2 and column 4
# 5  
Old 03-28-2012
awk

Hi,

Check out this,

Code:
awk '/^-/{split($0,a," ");}$0 !~ /^-/{for(i=1;i<=length(a);i++){t=substr($0,0,length(a[i]));if ( t ~ / / && length(t) == 4 ){split(t,b," ");t=b[1];}gsub(t,"");sub(/^ /,"");r="\""t"\"";temp=temp" "r;}sub(/^ /,"",temp);print temp;}' file

Cheers,
RangaSmilie
# 6  
Old 03-28-2012
Quote:
Originally Posted by sonali.s.more
I know;if delimeter would have been there it would have been peice of cake --
@ygemici,any idea how to put double quotes around column 2 and column 4
try this Smilie
Code:
# sed '/^-\|^$/d;$!s/\([^ ]*\) \([^ ]* [^ ]* [^ ]* [^ ]*\) \([^ ]*\) \(.*\)/"\1" "\2" "\3" "\4"/
;$s/\([^ ]*\) \([^ ]* [^ ]* [^ ]*\) \([^ ]*\) \(.*\)/"\1" "\2" "\3" "\4"/' data
"WFI001" "Xxxxxx Control Work Item" "A" "Number of Records"
"WFI001" "Xxxxxx Control Work Item" "B" "Count of Distinct Hook Id"
"WFI001" "Xxxxxx Control Work Item" "C" "Sum of Workitem Num"
"WFI008" "Xxxxxx Control DocTABA" "A" "Number of Records"

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

To flat file, append null or space if its length is less than 10

Hi, We receive flat files with fixed width data Now our goal is append from right null or space to each record if the lenght of the record is less than for example 10. for example 123 45 6 0 123 45 123 45 6 123 and output should be 123 45 6 0 123 45**** 123 45 6**... (7 Replies)
Discussion started by: shharrath
7 Replies

2. Shell Programming and Scripting

Fixed length flat file extraction

Hii ,I am new to Unix ,i have a flat file which is (fixed length) sitting in unix,Which is holding the data for a table.I want to extract one column(length7-10) on the basis of another column(length13-15) and want only one single row Example: Below is the sample of flat file. 1111 AAAA 100 ... (4 Replies)
Discussion started by: laxmi1166
4 Replies

3. Shell Programming and Scripting

Make multiple files of equal length

I have 150 files with 4 columns each but variable row lengths that I need to combine by column. I do not have any common column. I want to use "paste " command in unix to do it but before that I have to get all my files to be of equal length. Is there a way using awk or sed to fill up n no. of... (7 Replies)
Discussion started by: manishabh
7 Replies

4. Shell Programming and Scripting

Make variable length record a fixed length

Very, very new to unix scripting and have a unique situation. I have a file of records that contain 3 records types: (H)eader Records (D)etail Records (T)railer Records The Detail records are 82 bytes in length which is perfect. The Header and Trailer records sometimes are 82 bytes in... (3 Replies)
Discussion started by: jclanc8
3 Replies

5. Shell Programming and Scripting

reading fixed length flat file and calling java code using shell scripting

I am new to shell scripting and I have to to the following I have a flat file with storename(lenth 20) , emailaddress(lenth 40), location(15). There is NO delimiters in that file. Like the following str00001.txt StoreName emailaddress location... (3 Replies)
Discussion started by: willywilly
3 Replies

6. Shell Programming and Scripting

how to read fixed length flat file....

Hi Gurus, Thanks in advance... I am new to writing shell scripting and help me out reading a flat file with fixed length. I have a fixed length flat file with storename(lenth 6) , emailaddress(lenth 15), location(10). There is NO delimiters in that file. Like the following str00001.txt... (2 Replies)
Discussion started by: willywilly
2 Replies

7. Shell Programming and Scripting

Exporting a flat fixed length file (Urgent)

Hi All, So far, I've been extracting data from db2 tables and exporting the file as a tab delimited file into a UNIX server using the following command: export to /.../.../.../.../.../SM_RAW_DATA.dat of del modified by coldel| nochardel select a.accno, a.CUR_BL_AM, ... (1 Reply)
Discussion started by: jj2485
1 Replies

8. UNIX for Dummies Questions & Answers

Conditional sorting on fixed length flat file

I have a fixed length file that need to be sorted according to the following rule IF B=1 ORDER by A,B Else ORDER by A,C Input file is ABC 131 112 122 231 212 222 Output needed ABC 112 131 122 212 231 222 (1 Reply)
Discussion started by: zsk_00
1 Replies

9. UNIX for Dummies Questions & Answers

Convert a tab delimited/variable length file to fixed length file

Hi, all. I need to convert a file tab delimited/variable length file in AIX to a fixed lenght file delimited by spaces. This is the input file: 10200002<tab>US$ COM<tab>16/12/2008<tab>2,3775<tab>2,3783 19300978<tab>EURO<tab>16/12/2008<tab>3,28523<tab>3,28657 And this is the expected... (2 Replies)
Discussion started by: Everton_Silveir
2 Replies

10. UNIX for Dummies Questions & Answers

What the command to find out the record length of a fixed length file?

I want to find out the record length of a fixed length file? I forgot the command. Any body know? (9 Replies)
Discussion started by: tranq01
9 Replies
Login or Register to Ask a Question