Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

How to parse fixed-width columns which may include empty fields?

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How to parse fixed-width columns which may include empty fields?
# 1  
Old 08-02-2012
How to parse fixed-width columns which may include empty fields?
I am trying to selectively display several columns from a db2 query, which gives me a fixed-width output (partial output listed here):
Code:
--------- -------------------------- ------------ ------
000       0000000000198012           702          29
000       0000000000198013                        29
000                                  702          29
000       0000000000198015           702          03
055       0000000000000001           702


I am trying to accomplish two things:
1. parsing each field from each column, even the ones that are blank
2. put a delimiter of ";" between each field

So expected output would be:
Code:
000;0000000000198012;702;29
000;0000000000198013;;29
000;;702;29
000;0000000000198015;702;03
055;0000000000000001;702;


This is my draft code, which works:
myquery | cut -c 1-9,11-48,60-68,112-142,143-159 --output-delimiter=";"

However my target system does not support any of the GNU utilities, so I cannot use the "output-delimiter" option with cut, or the "FIELDWIDTHS" option with gawk.

So how can I get the desired output, when there are blank fields in some columns?

Thanks in advance for your help!
myquery.txt (324 Bytes) 
Last edited by ahsh79; 08-02-2012 at 03:32 PM.. Reason: formatting
# 2  
Old 08-02-2012
To keep your data's formatting, use code tags, like this:
Code:
--------- -------------------------- ------------ ------
000       0000000000198012           702          29
000       0000000000198013                        29
000                                  702          29
000       0000000000198015           702          03
055       0000000000000001           702

What should be desired output for that sample data (with code tags)?
This User Gave Thanks to bartus11 For This Post:
ahsh79
# 3  
Old 08-02-2012
Code:
sed "s/ \{1\,\}/;/g" <filename>

Last edited by Franklin52; 08-06-2012 at 03:28 AM.. Reason: Please use code tags for data and code samples
# 4  
Old 08-02-2012
I tried it in my code:

Code:
myquery | cut -c 1-9,11-48,60-68,112-142,143-159 | sed "s/ \{1\,\}/;/g"

and although it's putting in the delimiter between the fields correctly, I am still having a problem where when one field is blank, it is pulling the data from the next non-blank field.

Here is the actual output:
Code:
000;0000000000198012;702;DS;WNT000
000;0000000000198013;702;DS;WNT100
000;0000000000198014;702;DS;WNT100
000;0000000000198015;702;ABC123456;DS;WNT100
100;0000000000000001;702;ABC123;DS;WNT100

Here is the desired output (notice the empty fields in the 4th column);
Code:
000;0000000000198012;702; ;DS;WNT000
000;0000000000198013;702; ;DS;WNT100
000;0000000000198014;702; ;DS;WNT100
000;0000000000198015;702;ABC123456;DS;WNT100
100;0000000000000001;702;ABC123;DS;WNT100

# 5  
Old 08-02-2012
thought I'm not master to get the result in one stream, I'll use another sed command .
Code:
myquery | cut -c 1-9,11-48,60-68,112-142,143-159 | sed "s/ \{1\,\}/;/g" | sed "s/;;/;/g"

---------- Post updated at 03:28 PM ---------- Previous update was at 03:26 PM ----------

ignore my above post
Last edited by vgersh99; 08-02-2012 at 04:27 PM.. Reason: code tags, please!
# 6  
Old 08-02-2012
Code:
awk '
BEGIN {
  n = split("1-9,11-36,38-49,51-56", a, "[^0-9]") - 1
  for (i = 1; i <= n; i += 2)
    l[i] = a[i+1] - (p[i] = a[i]) + 1
}
{
  for (i = 1; i <= n; i += 2) {
    x = substr($0, p[i], l[i])
    sub(/ +$/, "", x)
    printf("%s%s", x, (i < n)? "," : "\n")
  }
}'

This User Gave Thanks to binlib For This Post:
ahsh79
# 7  
Old 08-02-2012
Quote:
Originally Posted by ahsh79
I tried it in my code:

Code:
myquery | cut -c 1-9,11-48,60-68,112-142,143-159 | sed "s/ \{1\,\}/;/g"

and although it's putting in the delimiter between the fields correctly, I am still having a problem where when one field is blank, it is pulling the data from the next non-blank field.

Here is the actual output:
Code:
000;0000000000198012;702;DS;WNT000
000;0000000000198013;702;DS;WNT100
000;0000000000198014;702;DS;WNT100
000;0000000000198015;702;ABC123456;DS;WNT100
100;0000000000000001;702;ABC123;DS;WNT100

Here is the desired output (notice the empty fields in the 4th column);
Code:
000;0000000000198012;702; ;DS;WNT000
000;0000000000198013;702; ;DS;WNT100
000;0000000000198014;702; ;DS;WNT100
000;0000000000198015;702;ABC123456;DS;WNT100
100;0000000000000001;702;ABC123;DS;WNT100

Do you want <space> representing an empty field? Or do you want:
Code:
000;0000000000198012;702;;DS;WNT000
000;0000000000198013;702;;DS;WNT100
000;0000000000198014;702;;DS;WNT100
000;0000000000198015;702;ABC123456;DS;WNT100
100;0000000000000001;702;ABC123;DS;WNT100
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Request: How to Parse dynamic SQL query to pad extra columns to match the fixed number of columns

Hello All, I have a requirement in which i will be given a sql query as input in a file with dynamic number of columns. For example some times i will get 5 columns, some times 8 columns etc up to 20 columns. So my requirement is to generate a output query which will have 20 columns all the... (7 Replies)
Discussion started by: vikas_trl
7 Replies

2. 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

3. Shell Programming and Scripting

Removing duplicates in fixed width file which has multiple key columns

Hi All , I have a requirement where I need to remove duplicates from a fixed width file which has multiple key columns .Also , need to capture the duplicate records into another file . File has 8 columns. Key columns are col1 and col2. Col1 has the length of 8 col 2 has the length of 3. ... (5 Replies)
Discussion started by: saj
5 Replies

4. UNIX for Dummies Questions & Answers

Filling the empty columns in a fixed column file

Hi, I have a file with fixed number of columns (total 58 columns) delimeted by pipe (|). Due to a bug in the application the export file does not come with fixed number of columns. The missing data columns are being replaced by blank in the output file. In one line I can have 25 columns (33... (1 Reply)
Discussion started by: yale_work
1 Replies

5. Shell Programming and Scripting

variable fixed-width fields

Hi there, CTL Port IO Rate(IOPS) Read Rate(IOPS) Write Rate(IOPS) Read Hit(%) Write Hit(%) Trans. Rate(MB/S) Read Trans. Rate(MB/S) Write Trans. Rate(MB/S) 09:36:48 0 A 136 0 135 97 100 ... (6 Replies)
Discussion started by: gray380
6 Replies

6. Shell Programming and Scripting

Printing Fixed Width Columns

Hi everyone, I have been working on a pretty laborious shellscript (with bash) the last couple weeks that parses my firewall policies (from a Juniper) for me and creates a nifty little columned output. It does so using awk on a line by line basis to pull out the appropriate pieces of each... (4 Replies)
Discussion started by: cixelsyd
4 Replies

7. Shell Programming and Scripting

Removing \n within a fixed width record

I am trying to remove a line feed (\n) within a fixed width record. I tried the tr -d ‘\n' command, but it also removes the record delimiter. Is there a way to remove the line feed without removing the record delimiter? (10 Replies)
Discussion started by: CKT_newbie88
10 Replies

8. 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

9. Shell Programming and Scripting

Combining Two fixed width columns to a variable length file

Hi, I have two files. File1: File1 contains two fixed width columns ID of 15 characters length and Name is of 100 characters length. ID Name 1-43<<11 spaces>>Swapna<<94 spaces>> 1-234<<10 spaces>>Mani<<96 spaces>> 1-3456<<9 spaces>>Kapil<<95 spaces>> File2: ... (4 Replies)
Discussion started by: manneni prakash
4 Replies

10. Shell Programming and Scripting

Extracting records with unique fields from a fixed width txt file

Greetings, I would like to extract records from a fixed width text file that have unique field elements. Data is structured like this: John A Smith NY Mary C Jones WA Adam J Clark PA Mary Jones WA Fieldname / start-end position Firstname 1-10... (8 Replies)
Discussion started by: sitney
8 Replies
Login or Register to Ask a Question