Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Process text file to create CSV

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Process text file to create CSV
# 1  
Old 10-27-2015
Hammer & Screwdriver Process text file to create CSV
I am working on a text file where I have to get data from a text file and convert it into either CSV format or Column format as shown below.

OUTPUT Expected

Code:
GRP Name Pair  Size    DName       DNumber   PName     PNumber  
adm_grp  Pair1 150.00KG Pair_0ABC_1  0396   Pair_0267_s  1292

File contains inventory data as follows in INPUT Example. This data below is for one group (adm_grp) only. I have about 50 such groups. I have to grab only only few fields information and display it in following format. These items have pairs they have same pair name but different Name ( PName and Dname) and number ( PNumber and DNumber). Group Name always precedes line "Copy:"


PAIR Example I have copied them next to each other for better understanding:
Code:
adm_grp: <--- GRP Name
    Copy:

Copy Pair: Pair1                                                                Copy Pair: Pair1 <--- Pair
Size: 150.00KG                                                                  Size: 150.00KG <--- Size
H Group Name: adm_h_grp                    
                                           
  Details:                                                                                           Details: 
        Cat: Inv                                                                                Cat: Inv 
        DID: 5b-16                                                                      PID: 5a-7f 
        Mfg: KLM                                                                  Mfg: KLM 
        DSize: 150.00KG                                               Type: chm 
        DName: Pair_0ABC_1                  
        DNumber: 0396                                               PSize: 150.00KG 
        Streams: None                                                    PName: Pair_0267_s  <--- Pname
                                                                                                         PNumber:1292 <--- PNumber
                                                        Streams: None

INPUT Example -See File attached

OUTPUT Expected
Code:
GRP Name Pair  Size    DName       DNumber   PName     PNumber  
adm_grp  Pair1 150.00KG Pair_0ABC_1  0396   Pair_0267_s  1292

I have a script below but it gives me output for each processed line I want output for each pair not each Line.


Code:
#!/usr/bin/ksh
 
 gname=""
 pcname=""
 pair=""
 size=""
 dname=""
 pname=""
 
 while read line
 do
 prt1=$(echo $line |awk -F: '{ print $1}')]
 
         if [ "$prt1" == "Copy Pair" ]
         then
         pair=$(echo $line |awk -F: '{ print $2}')
         elif [ "$prt1" == "Size" ]
         then
         size=$(echo $line |awk -F: '{ print $2}')
         elif [ "$prt1" == "DName" ]
         then
         dname=$(echo $line |awk -F: '{ print $2}')
         elif [ "$prt1" == "PName" ]
         then
         pname=$(echo $line |awk -F: '{ print $2}')
         fi
 echo $pair, $size, $dname, $pname
 
 done < dummy-src.txt

Moderator's Comments:
Mod Comment Please use CODE tags when displaying all sample input, sample output, and code segments.
Do not add FONT and COLOR tags to every line of code you post. CODE tags automatically use a clear fixed-width font appropriate for this forum.
test_inv.txt (2.2 KB) 
Last edited by Don Cragun; 10-27-2015 at 02:17 PM.. Reason: Add CODE tags, remove COLOR and FONT tags.
# 2  
Old 10-27-2015
Here is an awk program based on some assumptions:-
Code:
awk -F'[: ]' '
        BEGIN {
                printf "%-18s%-18s%-18s%-18s%-18s%-18s%-18s\n", "GRP name", "Pair", "Size", "DName", "DNumber", "PName", "PNumber"
                n = split("Copy Pair:,Size:,DName:,DNumber:,PName:,PNumber:", A_s_strings, "," )
        }
        /Copy:/ {
                grp_name = p
                next
        }
        {
                for ( i = 1; i <= n; i++ )
                {
                        if ( $0 ~ A_s_strings[i] )
                                A_v[A_s_strings[i]] = $NF
                }
                if ( $0 ~ A_s_strings[1] && A_v[A_s_strings[1]] )
                {
                        printf "%-18s", grp_name
                        for ( i = 1; i <= n; i++ )
                                printf "%-18s", A_v[A_s_strings[i]]
                        printf "\n"
                }
        }
        {
                p = $1
        }
' filename

# 3  
Old 10-27-2015
How about
Code:
awk '
BEGIN           {print "GRP Name\tPair\tSize\tDName\tDNumber\tPName\tPNumber"
                }
/Copy:/         {GRPNAM=LAST
                 for (s in SIZE) print GRPNAM, s, SIZE[s], NAME[s,"D"], NMBR[s,"D"], NAME[s,"P"], NMBR[s,"P"]
                 delete NAME
                 delete NMBR
                 delete SIZE
                }
                {sub (/:/," ")
                 LAST=$0
                }

/Copy Pair/     {CP=$3
                }
/[DP]Name/      {NAME[CP,substr($1,1,1)] = $2
                }
/[DP]Number/    {NMBR[CP,substr($1,1,1)] = $2
                }
/Size/          {SIZE[CP] = $2
                }
END             {for (s in SIZE) print GRPNAM, s, SIZE[s], NAME[s,"D"], NMBR[s,"D"], NAME[s,"P"], NMBR[s,"P"]
                }
' OFS="\t" file
GRP Name    Pair     Size        DName          DNumber PName          PNumber
adm_grp     Pair1    150.00KG    Pair_0ABC_1    0396    Pair_0267_s    1292
adm_grp     Pair9    100.00KG    Pair_1678_s    0396        
adm_grp     Pair10    20.00KG    Pair_1679_s    0396    Pair_0274_s    1292

# 4  
Old 10-27-2015
Thank RudiC and Yoda. I truly appreciate help. For me RudiC's script works better as Yoda's script creates multiple lines for each pair and doesn't match the Name and Number of Pairs.

I have one followup question. I just realized that not all groups have DName, DNumber and PName and PNumber have "D" and "P" prefixes. Some groups just say Name and Number no Prefix to separate D Pair with P Pair.

File without prefixes for Number and Name
Code:
adm_grp:
    Copy:
      name_pc:
       Repli Ploy:
          Copy Pair: Pair1
          Size: 150.00KG
          H Group Name: adm_h_grp
          
      Details:
            Cat: Inv
            DID: 5b-16
            Mfg: KLM
            Size: 150.00KG
            Name: Pair_0ABC_1
            Number: 0396
            Streams: None

          Copy Pair: Pair10
          Size: 150.00KG
          H Group Name: adm_g_grp
          
          Details:
            Cat: Inv
            DID: 12-34
            Mfg: KLM
            Type: chm
            Size: 150.00KG
            Name: Pair_1679_s
            Number: 0396
            Streams: None
         
     Copy Pair: Pair9
          Size: 150.00KG
          H Group Name: adm_g_grp
          
          Details:
            Cat: Inv
            DID: 5b-3e
            Mfg: KLM
            Type: chm
            
            Size: 150.00KG
            Name: Pair_1678_s
            Number: 0396
            Streams: None
        Lib Members:
          Details:
            Kind: lop
            JID: 35-32
            Mfg: KLM
            Kind: mtx
            
            JSize: 100.00KG
            JName: 0A52
            JSerial: 661
            Streams: None

      name_cp:
        CP List:
          Copy Pair: Pair1
          Size: 150.00KG
          Details:
            Cat: Inv
            PID: 5a-7f
            Mfg: KLM
            Type: chm
            
            Size: 150.00KG
            Name: Pair_0267_s
            Number:1292
            Streams: None

          Copy Pair: Pair10
          Size: 150.00KG
          Details:
            Cat: Inv
            PID: 5a-bb
            Mfg: KLM
            Type: chm
            
            Size: 150.00KG
            Name: Pair_0274_s
            Number:1292
            Streams: None

        Lib Members:
          Details:
            Kind: lop
            JID: 39-33
            Mfg: KLM
            Kind: mtx
            
            JSize: 20.00KG
            JName: 1A93
            JNumber:2433
            Streams: None

test_inv.txt (2.1 KB) 
# 5  
Old 10-28-2015
So - which rule/condition do you want to apply to assign Name and Number to D... or P...?
# 6  
Old 10-28-2015
Thanks for your response RudiC. About condition. Lets say once I hit Pair1 the first time in file then Name will be D-Name and Number will be D-Number if I hit Pair1 second time it will be P-Name and P-Number.
# 7  
Old 10-28-2015
That's becoming a bit diffuse as there are several records that have e.g. "Name" in them. So you may have false triggers. Try
Code:
awk '
BEGIN           {print "GRP Name\tPair\tSize\tDName\tDNumber\tPName\tPNumber"
                }
/Copy:/         {GRPNAM=LAST
                 for (s in SIZE) print GRPNAM, s, SIZE[s], NAME[s,1], NMBR[s,1], NAME[s,2], NMBR[s,2]
                 delete NAME
                 delete NMBR
                 delete CNTA
                 delete CNTB
                }
                {sub (/:/," ")
                 LAST=$0
                }

/Copy Pair/     {CP=$3
                }
/^ *[DP]*Name/  {NAME[CP,++CNTA[CP]] = $2
                }
/^ *[DP]*Numbe/ {NMBR[CP,++CNTB[CP]] = $2
                }
/Size/          {SIZE[CP] = $2
                }
END             {for (s in SIZE) print GRPNAM, s, SIZE[s], NAME[s,1], NMBR[s,1], NAME[s,2], NMBR[s,2]
                }
' OFS="\t" file

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Create csv from text file

Gents, I am trying to create a csv file using the file attached. I have a problem to get all information required because the rows are not continues. Here is my code till now. awk ' /"ffid"/{if(s){print s;s=$NF}else{s=$NF}} /"LineNumber"/{s=s $NF} /"PointNumber"/{s=s $NF}... (4 Replies)
Discussion started by: jiam912
4 Replies

2. Shell Programming and Scripting

Compare 2 files of csv file and match column data and create a new csv file of them

Hi, I am newbie in shell script. I need your help to solve my problem. Firstly, I have 2 files of csv and i want to compare of the contents then the output will be written in a new csv file. File1: SourceFile,DateTimeOriginal /home/intannf/foto/IMG_0713.JPG,2015:02:17 11:14:07... (8 Replies)
Discussion started by: refrain
8 Replies

3. Shell Programming and Scripting

Create a pivot table from CSV file

Gents, Can you please help me to create a pivot table from a csv file. ( I have zip the csv file) Using the file attached, columns 1,28 and 21 i would like to get something like this output JD Val 1 2 3 4 5 6 7 8 9 10 11 12 Total... (4 Replies)
Discussion started by: jiam912
4 Replies

4. Shell Programming and Scripting

Need help. How to create csv file?

Hi I'm a beginner and I have some problem. I have multiple files in the same directory which has one column but rows following the format. File: directory/Disk.txt Content: a b c d e File: directory/Memory.txt a b c d e File: directory/CPU.txt (3 Replies)
Discussion started by: thenuie
3 Replies

5. Shell Programming and Scripting

Script to create a CSV file

I created a script that will go out and so a "/sbin/chkconfig --list | egrep XXX" against a server list that would create an output file like the following example: ---------------------------------------------------------------------------------- SERVER1 RC_Script_1 0:off 1:off 2:off... (4 Replies)
Discussion started by: asnatlas
4 Replies

6. UNIX for Dummies Questions & Answers

How to create a .csv file from 2 different .txt files?

Hi, I need to create a .csv file from information that i have in two different tab delimited .txt file. I just want to select some of the columns of each .txt file and paste them into a .cvs file. My files look like: File 1 transcript_id Seq. Description Seq. Length ... (2 Replies)
Discussion started by: alisrpp
2 Replies

7. Shell Programming and Scripting

Create an .csv/excel file using shellscript

In my file, i have 4 Product names(For ex:Microsoft excel, Oracle,.Net,IBM websphere,..etc.,) I want this 4 Products in 4 individual .csv/excel file after running the script with those products related information. (12 Replies)
Discussion started by: Navya
12 Replies

8. Shell Programming and Scripting

How can I create a CSV file from PL/Sql in UNIX?

Can someone help me on creating a script that will manage/create a csv file from Pl/Sql using UNIX?Any advice is welcome,thank you so much,:) (2 Replies)
Discussion started by: Atrap
2 Replies

9. Shell Programming and Scripting

how to create csv file using shell script

I have a file in multiple directory which has some records in the following format File: a/latest.txt , b/latest.txt, c/latest.txt -> Name=Jhon Age=27 Gender=M Street=LA Road Occupation=Service I want to generate a csv file from the above file as follows File: output.csv -> ... (9 Replies)
Discussion started by: rjk2504
9 Replies

10. Shell Programming and Scripting

Merging files to create CSV file

Hi, I have different files of the same type, as: Time: 100 snr: 88 perf: 10 other: 222 Each of these files are created periodically. What I need to do is to merge all of them into one but having the following form: (2 Replies)
Discussion started by: Ravendark
2 Replies
Login or Register to Ask a Question