Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

CSv2dat file headers and columns order

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting CSv2dat file headers and columns order
# 1  
Old 04-22-2014
CSv2dat file headers and columns order
Dear all,

I have a csv file which is transformed to .dat. I have an awk file which is supposing to do the mapping of the dat file. the code from the awk file is the one below.

The content of the dat file is looking like this (tab separated):
Code:
ODT   AGE        CDT    CO    SEX    TIME    VALUE    COMMENT
P3      Y6-8         ACT    FG    F         2011       1297        
P4      Y3-4         EMP    FG    M        2011       6940           b
P1      Y7-9         GRT    FG    F          2011       0                 c

what I have to do is the following:
1. keep the headers together with the columns order - done
2. if the value for COMMENT is "c" then the column COMMENT must be changed to STRING_COM, if the value for COMMENT is "b" (or anything different than "c") the COMMENT column must be changed to STRING_STATUS
3. No matter what other columns will be received except the ones mentioned here they must be removed
4. VALUE column should be renamed in "NUMB"

Here is my code which fix only the 1st point
Code:
BEGIN { 
  FS = "," ;
  OFS = "    " ;  
}

{ 

if(NR == 1)
{
  split($0, tmp, ",");
  for(i = 1; i <= NF; i++) 
  fields[tmp[i]] = i
  print tmp[1], tmp[2], tmp[3], tmp[4], tmp[5], tmp[6], tmp[7], tmp[8]

else
{
  split($0, tmp, ",");
  for(i = 1; i <= NF; i++) 
  fields[tmp[i]] = i

  print tmp[1], tmp[2], tmp[3], tmp[4], tmp[5], tmp[6], tmp[7], tmp[8]
}
}

expected result in the dat file:
Code:
ODT   AGE        CDT    CO    SEX    TIME    NUMB STRING_COM STRING_STATUS
P3      Y6-8         ACT    FG    F         2011       1297        
P4      Y3-4         ERT    FG    M        2011       6940           b
P1      Y7-9         GRT    FG    F          2011       0                           c

Thank you in advance
Last edited by Franklin52; 04-23-2014 at 02:59 AM.. Reason: Please use code tags
# 2  
Old 04-22-2014
PLease use code tags as required by forum rule!

Try
Code:
awk     'NR==1          {$7="NUMB";$8="STRING_COM";$9="STRING_STATUS"; print $0; next}
         $8 != "c"      {$9=$8; $8=""}
         NF > 9         {for (i=10; i<=NF; i++) $i=""}              
         1
        ' OFS="\t" file
ODT    AGE    CDT    CO    SEX    TIME    NUMB    STRING_COM    STRING_STATUS
P3    Y6-8    ACT    FG    F      2011    1297        
P4    Y3-4    EMP    FG    M      2011    6940              b    
P1    Y7-9    GRT    FG    F      2011    0       c

# 3  
Old 04-22-2014
do you mean to change my code exactly like this:
Code:
BEGIN { 
FS = "," ;
OFS = " " ; 
}
NR==1          {$7="NUMB";$8="STRING_COM";$9="STRING_STATUS"; print $0; next}          $8 != "c"      {$9=$8; $8=""}          NF > 9         {for (i=10; i<=NF; i++) $i=""}

---------- Post updated at 10:57 AM ---------- Previous update was at 10:39 AM ----------

the result of the script you gave me is

Code:
ODT    AGE    CDT    CO    SEX    TIME    NUMB    STRING_COM    STRING_STATUS
P3    Y6-8    ACT    FG    F      2011    NUMB    STRING_COM    STRING_STATUS 
P4    Y3-4    EMP    FG    M      2011    NUMB    STRING_COM    STRING_STATUS  
P1    Y7-9    GRT    FG    F      2011    NUMB    STRING_COM    STRING_STATUS

please also be aware that the columns are not coming always in the same order.<br>
Last edited by Don Cragun; 04-24-2014 at 01:35 AM.. Reason: Add CODE tags.
# 4  
Old 04-22-2014
Code:
awk 'BEGIN{FS=OFS="\t"}
  NR == 1{print ("ODT" FS "AGE" FS "CDT" FS "CO" FS "SEX" FS "TIME" FS "NUMB" FS "STARING_COM" FS "STRING_STATUS");
    next}
  NR > 1 && $8 == "b" {$9 = $8; $8 = ""}1' file

# 5  
Old 04-22-2014
the result is :

Code:
ODT AGE CDT CO SEX TIME NUMB STRING_COM STRING_STATUS
ODT AGE CDT CO SEX TIME NUMB STRING_COM STRING_STATUS
ODT AGE CDT CO SEX TIME NUMB STRING_COM STRING_STATUS

Last edited by Don Cragun; 04-24-2014 at 01:37 AM.. Reason: Add CODE tags.
# 6  
Old 04-22-2014
Please use code tags as required by forum rule. What so difficult about this?

No, I didn't mean to change FS = "," ; OFS = " " ; in your script; I meant to use my proposal as is.
In your spec you said "... dat file ... (tab separated):" Why then do you set the field separator to ","? No surprise your result is different from mine.
Last edited by RudiC; 04-22-2014 at 02:08 PM..
# 7  
Old 04-22-2014
The code worked fine for me with the tab separated data you have provided and generated the expected output
Unless you make any changes to the provided code, it should work fine
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk to compare files and validate order of headers

The below awk verifies the count and order of each text file in the directory. The script does execute and produce output, however the order of the headers are not compared to key. The portion in bold is supposed to do that. If the order of the headers in each text file is the same as key, then... (0 Replies)
Discussion started by: cmccabe
0 Replies

2. UNIX for Advanced & Expert Users

Cannot find logical file format for BSD file headers.

Hi. Unix rookie here. Been looking for a few days for reference documents on how BSD UNIX lays the logical file format onto a disk. Goal is to view/edit with hex editor for data repair. Lots of docs are available for how to use Unix commands (like xxd), but I want to learn the map of how Unix... (4 Replies)
Discussion started by: Chris_top_he_r
4 Replies

3. Shell Programming and Scripting

Re-order columns

I am trying to reorder columns based on a value in a field in the attached file. Thank you :). If $11 is not equal to "unknown", then in a new text file $4, $11, $7, $13 For example, row 24 $11 is not unknown A_16_P39089234, chr10:76765556-76765615, KAT6B, 0.9337 (5 Replies)
Discussion started by: cmccabe
5 Replies

4. Shell Programming and Scripting

copying columns with headers' specific pattern

Hi friends, I have data in tab separated file with headers like this : *sml1 *sml3 *smln7 smfk9 smllf56... Which shell command I should use if i want to extract entire columns that have header names beginning with "*" ? i want to copy these columns into another file. Thanks, (14 Replies)
Discussion started by: jacks
14 Replies

5. Shell Programming and Scripting

Change many columns position/order

Hi everyone, Please some help over here. (I´m using cygwing) I have files with 40 columns and 2000 lines in average. I´m trying to change the order position as follow. Original columns position:... (3 Replies)
Discussion started by: cgkmal
3 Replies

6. Shell Programming and Scripting

Transpose multipe columns to rows and adding headers

Hi, I found the following awk script to transpose multiple (3) columns to multiple rows: #=== BEGIN {FS=","} { for (i=1;i<=NF;i++) { arr=$i; if(nf<= NF) nf=NF; } nr=NR } END { for(i=1;i<=nf;i++) { (8 Replies)
Discussion started by: Gery
8 Replies

7. Shell Programming and Scripting

Merging of files with different headers to make combined headers file

Hi , I have a typical situation. I have 4 files and with different headers (number of headers is varible ). I need to make such a merged file which will have headers combined from all files (comman coluns should appear once only). For example - File 1 H1|H2|H3|H4 11|12|13|14 21|22|23|23... (1 Reply)
Discussion started by: marut_ashu
1 Replies

8. Shell Programming and Scripting

Remove text between headers while leaving headers intact

Hi, I'm trying to strip all lines between two headers in a file: ### BEGIN ### Text to remove, contains all kinds of characters ... Antispyware-Downloadserver.com (Germany)=http://www.antispyware-downloadserver.c om/updates/ Antispyware-Downloadserver.com #2... (3 Replies)
Discussion started by: Trones
3 Replies

9. UNIX for Dummies Questions & Answers

Changing the order of columns in a script

I have the following script: (echo -n `curl http://www.example.com/scores.txt | grep mylocation`; date +%Y%m%d%H%M%S) >> myscores.txt This script works fine, except that it places the timestamp at the end of the file myscores.txt. How do add the timestamp as the first column and then a tab and... (4 Replies)
Discussion started by: figaro
4 Replies

10. Programming

Question on order of headers and WEXITSTATUS

In one of the Unix Programming FAQ's they have the following headers in the program to catch SIGCHLD #include <sys/types.h> /* include this before any other sys headers */ #include <sys/wait.h> /* header for waitpid() and various macros */ #include <signal.h> /* header for signal... (5 Replies)
Discussion started by: frequency8
5 Replies
Login or Register to Ask a Question