Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Insert empty columns inside a pipe delimited file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Insert empty columns inside a pipe delimited file
# 8  
Old 12-10-2014
When given the input:
Code:
1,2,3,4,5,6,7,8,9,10,11,12

I would expect the output:
Code:
Align,3,10,9,7,6,4,2,5,11,10,11

from the awk script:
Code:
awk 'BEGIN {FS=OFS=","} {print "Align",$3,$10,$9,$7,$6,$4,$2,$5,$11,$10,$11}'

rather than:
Code:
ALIGN,3,10,9,7,6,4,2,5,11,10,11

but it is doing what I would expect.

What are you getting?

What did you expect to get?

What OS are you using?
This User Gave Thanks to Don Cragun For This Post:
Hypesslearner
# 9  
Old 12-10-2014
Thanks Singh for the quick reply , I am trying to extend the same code now as below

Code:
awk 'BEGIN {FS=OFS=","} 
{$1="Align"
$COMMA=" , , , , ,"
$SIT="SIT"; 
print $1,$3,$10,$9,$7,$6,$4,$2,$COMMA,$5,$HPR,$11,$12,$13,$14,$15,$16,$17}' EXPDATA.txt>temp.txt

I need to insert few more columns like SIT and commas also

But it is prinitng only SIT and empty columns in the output file

Please help

---------- Post updated at 02:59 AM ---------- Previous update was at 02:51 AM ----------

Hi Cragun,

Just to be detail , My requirement is as below

Code:
Input file fields  - 1,2,3,4,5,6,7,8,9,10,11,12
Ouput file fields should be - Align,10,9,7,6,4,2,,,,,5,SIT,11

I am trying to insert new fields and spaces basically .


Thanks
Moderator's Comments:
Mod Comment CODE tags needs to be in the form:
[CODE]code to be formatted[/CODE]
not:
[CODE][/CODE]code to be formatted[CODE][/CODE]
Last edited by Don Cragun; 12-10-2014 at 04:05 AM.. Reason: Fix CODE tags.
# 10  
Old 12-10-2014
In the code:
Code:
awk 'BEGIN {FS=OFS=","} 
{$1="Align"
$COMMA=" , , , , ,"
$SIT="SIT"; 
print $1,$3,$10,$9,$7,$6,$4,$2,$COMMA,$5,$HPR,$11,$12,$13,$14,$15,$16,$17}' EXPDATA.txt>temp.txt

You have $COMMA where you probably just want COMMA, $SIT where you probably just want SIT, and you're using $HPR even though HPR has never been defined. You also don't need to assign constant values to variables every time through the loop; just do it once in the BEGIN section of your script. Maybe you want something more like:
Code:
awk '
BEGIN { FS=OFS=","
        COMMA=" , , , , ,"
        SIT="SIT"} 
{       $1="Align"
        print $1,$3,$10,$9,$7,$6,$4,$2,COMMA,$5,SIT,$11,$12,$13,$14,$15,$16,$17
}' EXPDATA.txt>temp.txt

but if you don't show us any sample input or desired output, we're just guessing.
This User Gave Thanks to Don Cragun For This Post:
Hypesslearner
# 11  
Old 12-10-2014
Wow That worked

---------- Post updated at 10:31 PM ---------- Previous update was at 09:34 PM ----------

Hi all,

Here is my code

Code:
awk '
BEGIN { 
   FS=OFS="|"
   COMMA="|||||"
        HPR="HPR"
 
 } 
{ 
   if (NR==1) {next}
   $1="Align"
        print $1,$3,$10,$9,$7,$6,$4,$2,COMMA,$5,HPR,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22
}' $EXPDATAFILE> FORECAST_`echo $CURRFINYEAR`_$(echo `date +"%Y%d%m"`).txt

I need to do the same process for different files .

Like for One file i need to Print Align, another file it is realign

So i am trying to call this as a function in my code
Code:
 
File_Modify  ALIGN 
File_Modify REALIGN

But how to call these parameters inside thefunction

Code:
 
File_Modify {
awk '
BEGIN { 
   FS=OFS="|"
   COMMA="|||||"
        HPR="HPR"
 
 } 
{ 
   if (NR==1) {next}
   $1=?????
        print $1,$3,$10,$9,$7,$6,$4,$2,COMMA,$5,HPR,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22
}' $EXPDATAFILE> $1_`echo $CURRFINYEAR`_$(echo `date +"%Y%d%m"`).txt

Last edited by Don Cragun; 12-10-2014 at 11:59 PM.. Reason: Fix CODE tags.
# 12  
Old 12-10-2014
Hypesslearner,
I repeat:
Moderator's Comments:
Mod Comment CODE tags needs to be in the form:
[CODE]code to be formatted[/CODE]
not:
[CODE][/CODE]code to be formatted[CODE][/CODE]


Try something like:
Code:
File_Modify {
        awk -v aligntype="$1" '
        BEGIN { FS=OFS="|"
                COMMA="|||||"
                HPR="HPR"
        }
        NR==1 { next
        }
        {       print aligntype,$3,$10,$9,$7,$6,$4,$2,COMMA,$5,HPR,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22
        }' $EXPDATAFILE > "$1_${CURRFINYEAR}_$(date +%Y%d%m).txt"
}

Note that if you use date +%Y%m%d (instead of date + %Y%d%m), an alphabetical sort of your filenames (or the normal output of ls) will list your files for the same value of $CURFINYEAR in increasing date order. (If you later want to look at the last x days' files, this change might make your life easier.)
This User Gave Thanks to Don Cragun For This Post:
Hypesslearner
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

Need to convert a pipe delimited text file to tab delimited

Hi, I have a rquirement in unix as below . I have a text file with me seperated by | symbol and i need to generate a excel file through unix commands/script so that each value will go to each column. ex: Input Text file: 1|A|apple 2|B|bottle excel file to be generated as output as... (9 Replies)
Discussion started by: raja kakitapall
9 Replies

2. Shell Programming and Scripting

Shuffle Columns in Pipe delimited data

My sample data contains escape characters followed by delimiter. I'm stuck in writing awk comand to swap the columns. please help me out. Sample Data: 12345678|ABN\|XYZ MED CHEM PTY. LTD.|C||100.00|22|AB"C\|Corp|"XYZ|CDEF"| Expected Output Data: 12345678|C|ABN\|XYZ MED CHEM PTY.... (10 Replies)
Discussion started by: BrahmaNaiduA
10 Replies

3. Shell Programming and Scripting

Delete and insert columns in a tab delimited file

Hi all , I have a file having 12 columns tab delimited . I need to read this file and remove the column 3 and column 4 and insert a word in column 3 as "AVIALABLE " Is there a way to do this . I am trying like below Thanks DJ cat $FILENAME|awk -F"\t" '{ print $1 "\t... (3 Replies)
Discussion started by: Hypesslearner
3 Replies

4. Shell Programming and Scripting

Insert a value in a pipe delimited line (unsig sed,awk)

Hi, I want to insert a value (x) in the 3rd position of each line in a file like below a|b|c|d|1 a|b|c|d a|b|c|d|e|1 a|b|cso that output file looks like a|b|x|c|d|1 a|b|x|c|d a|b|x|c|d|e|1 a|b|x|cI can do that using perl as below #!/usr/bin/perl -w use strict; #inserting x at... (5 Replies)
Discussion started by: sam05121988
5 Replies

5. Shell Programming and Scripting

Oracle table extract: all columns are not converting into pipe delimited in flat file

Hi All, I am writing a shell script to extract oracle table into a pipe dilemited flat file. Below is my code and I have attached two files that I have abled to generate so far. 1. Table.txt ==> database extract file 2. flat.txt ==> pipe delimited after some manipulation of the original db... (5 Replies)
Discussion started by: express14
5 Replies

6. Shell Programming and Scripting

Remove few columns from pipe delimited file

I have file as below column1|column2|column3|column4|column5| fill1|fill2|fill3|fill4|fill5| abc1|abc2|abc3|abc4|abc5| . . . . i need to remove column2,3, from that file column1|column4|column5| fill1|fill4|fill5| abc1|abc4|abc5| . . . (3 Replies)
Discussion started by: greenworld123
3 Replies

7. Shell Programming and Scripting

Insert empty columns in a flat file

Hi, I have a tab delimited flat file, for example shown below Name Desg Loc a b c d e fI want to insert an empty column inbetween the column Desc and Loc, the result should be like shown below: Name LName Desg Loc a b c d e ... (6 Replies)
Discussion started by: sampoorna
6 Replies

8. Shell Programming and Scripting

how to Insert values in multiple lines(records) within a pipe delimited text file in specific cols

this is Korn shell unix. The scenario is I have a pipe delimited text file which needs to be customized. say for example,I have a pipe delimited text file with 15 columns(| delimited) and 200 rows. currently the 11th and 12th column has null values for all the records(there are other null columns... (4 Replies)
Discussion started by: vasan2815
4 Replies

9. Shell Programming and Scripting

How to insert a sequence number column inside a pipe delimited csv file using shell scripting?

Hi All, I need a shell script which could insert a sequence number column inside a dat file(pipe delimited). I have the dat file similar to the one as shown below.. |A|B|C||D|E |F|G|H||I|J |K|L|M||N|O |P|Q|R||S|T As shown above, the column 4 is currently blank and i need to insert sequence... (5 Replies)
Discussion started by: nithins007
5 Replies

10. UNIX for Dummies Questions & Answers

Adding EMPTY columns to Tab-delimited txt file

Hi I have a txt file with 4 columns where I need to add 4 empty columns in the middle meaning that I need what is currently column 4 to be column 8 in a new file. The idea is that I have to use the file as input in a program that reads the data in column 1 and 8, so the content of the other... (8 Replies)
Discussion started by: Banni
8 Replies
Login or Register to Ask a Question