Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Inserting blank columns in already present CSV file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Inserting blank columns in already present CSV file
# 1  
Old 05-30-2014
Inserting blank columns in already present CSV file
Hi,

i have a csv file which have headers and values of it like below :
PHP Code:
headers --> CI Ref        SerialNumber LastScanDate
values -->   VMware-42,VMware-42,Tue, 20 May 2014 11:03:44 +0000 
i want to have a above csv in below format :
PHP Code:
headers --> CI Name CI Description CI Ref SerialNumber LastScanDate abc
values -->   <blank>,<blank>,VMware-42,VMware-42,20 May 2014 11:03:44 +0000,<blank> 
basically to add columns with specific headers wnd blank or null value in between .

request you to provide some hint or help.
# 2  
Old 05-30-2014
Quote:
Originally Posted by omkar.jadhav
Hi,

i have a csv file which have headers and values of it like below :
PHP Code:
headers --> CI Ref        SerialNumber LastScanDate
values -->   VMware-42,VMware-42,Tue, 20 May 2014 11:03:44 +0000 
i want to have a above csv in below format :
PHP Code:
headers --> CI Name CI Description CI Ref SerialNumber LastScanDate abc
values -->   <blank>,<blank>,VMware-42,VMware-42,20 May 2014 11:03:44 +0000,<blank> 
basically to add columns with specific headers wnd blank or null value in between .

request you to provide some hint or help.
Your header is space+ / tab separated and values are comma separated, is it your real input ? is so try something like this

Code:
$ cat file
CI Ref        SerialNumber LastScanDate
VMware-42,VMware-42,Tue, 20 May 2014 11:03:44 +0000

Code:
$ awk '{ $0 = FNR > 1 ? "<blank>,<blank>," $0 ",<blank>"  : "CI Name CI Description " $0 " abc" }1' file

Code:
CI Name CI Description CI Ref        SerialNumber LastScanDate abc
<blank>,<blank>,VMware-42,VMware-42,Tue, 20 May 2014 11:03:44 +0000,<blank>

# 3  
Old 05-30-2014
thanks a lot..here is my actual output of csv file :
cat sample.csv :
Code:
CI Ref,SerialNumber,Last Report Time
VMware-42 2b cb d6 e7 36 ec 73-f4 77 bd 2b f2 6f 25 5d,VMware-42 2b cb d6 e7 36 ec 73-f4 77 bd 2b f2 6f 25 5d,"Fri, 30 May 2014 12:23:13 +0000"
VMware-42 2b 79 e0 da 18 2e 9e-17 b2 6e e1 67 df 3c 6b,VMware-42 2b 79 e0 da 18 2e 9e-17 b2 6e e1 67 df 3c 6b,"Fri, 30 May 2014 12:26:08 +0000"

both values and headers are coma separated ...could you please let me know the change in the awk script which i have to do...
thanks in advance Smilie

also i request you to advise me what i have to do if i want to insert multiple empty columns with headers in between CI Ref and SerialNumber
Last edited by omkar.jadhav; 05-30-2014 at 10:13 AM..
# 4  
Old 05-30-2014
Hope this might make your life easy, sorry for late reply

Code:
$ cat file
CI Ref,SerialNumber,Last Report Time
VMware-42 2b cb d6 e7 36 ec 73-f4 77 bd 2b f2 6f 25 5d,VMware-42 2b cb d6 e7 36 ec 73-f4 77 bd 2b f2 6f 25 5d,"Fri, 30 May 2014 12:23:13 +0000"
VMware-42 2b 79 e0 da 18 2e 9e-17 b2 6e e1 67 df 3c 6b,VMware-42 2b 79 e0 da 18 2e 9e-17 b2 6e e1 67 df 3c 6b,"Fri, 30 May 2014 12:26:08 +0000"

Code:
$ cat addheader
#!/bin/bash

cat >header<<EOF
before=1
CI Name=<blank>
CI Description=<blank>

after=3
abc=<blank>
EOF

awk '

BEGIN{
	file="header"; OFS=","

	while(getline line <file)
	{
		split(line,Arr,/=/)

		if(tolower(Arr[1])=="before")
		{
			key = "b"  
			n = Arr[2] 
			continue
		}
		else if(tolower(Arr[1])=="after")
		{
			key = "a" 
			n = Arr[2] 
			continue
		}	
		
		
		if(length(line))
		{
			H[n FS key FS 1] = ((n FS key FS 1) in H) ? H[n FS key FS 1] OFS Arr[1] : Arr[1]
			H[n FS key FS 0] = ((n FS key FS 0) in H) ? H[n FS key FS 0] OFS Arr[2] : Arr[2]
		}
	}
	close(file)


    }

    {
	c=0 ; str = ""
	$0 = $0","                                   
	while($0) {
  			match($0,/ *"[^"]*" *,|[^,]*,/) 
  			f=substr($0,RSTART,RLENGTH)             
  			gsub(/,$/,"",f)               
			c++  		
		
			if(length(f))
			{
			ind = FNR==1 ? FNR : 0			
			
			f = ((c FS "a" FS ind) in H) ? \
			    f OFS H[c FS "a" FS ind] : f

			f = ((c FS "b" FS ind) in H) ? \
			    H[c FS "b" FS ind] OFS f : f
			    
			str = str == "" ? f : str OFS f
			}
			
  			$0 = substr($0,RLENGTH+1)                
		  }

		if(length(str))print str
    }
   ' file

Resulting
Code:
$ bash addheader
CI Name,CI Description,CI Ref,SerialNumber,Last Report Time,abc
<blank>,<blank>,VMware-42 2b cb d6 e7 36 ec 73-f4 77 bd 2b f2 6f 25 5d,VMware-42 2b cb d6 e7 36 ec 73-f4 77 bd 2b f2 6f 25 5d,"Fri, 30 May 2014 12:23:13 +0000",<blank>
<blank>,<blank>,VMware-42 2b 79 e0 da 18 2e 9e-17 b2 6e e1 67 df 3c 6b,VMware-42 2b 79 e0 da 18 2e 9e-17 b2 6e e1 67 df 3c 6b,"Fri, 30 May 2014 12:26:08 +0000",<blank>

use nawk if you are using sunos/solaris

Header format

Code:
<before|after>=field number
header_name1 = value1
header_name2 = value2

if no headername and no value only equal = it just creates empty fields..

do experiments...
Last edited by Akshay Hegde; 05-30-2014 at 01:31 PM.. Reason: to add header format
# 5  
Old 05-30-2014
thanks a trillion Akshay Hegde.

I have a final request to let me kbnow the logic behind this awk script as i will be using the same login to make this happen for csv which have almost 200 fileds or the changes i have to make in the script if i have to make the column values as black mean to say NULL values. so result should be csv containing 200 fileds which will be combination of colums which have headers alongwith their values and columns which will have header but no values that is NULL.

thanks again for the help you provided.

---------- Post updated at 10:55 AM ---------- Previous update was at 10:42 AM ----------

also i have tried to run the script but got the below output on Rhel machine:

PHP Code:
cat csv
,abcame,CI Description,CI Ref,SerialNumber,Last Report Time
<blank>,<blank>,VMware-42 2b cb d6 e7 36 ec 73-f4 77 bd 2b f2 6f 25 5d,VMware-42 2b cb d6 e7 36 ec 73-f4 77 bd 2b f2 6f 25 5d,"Fri,<blank>, 30 May 2014 12:23:13 +0000"
<blank>,<blank>,VMware-42 2b 79 e0 da 18 2e 9e-17 b2 6e e1 67 df 3c 6b,VMware-42 2b 79 e0 da 18 2e 9e-17 b2 6e e1 67 df 3c 6b,"Fri,<blank>, 30 May 2014 12:26:08 +0000" 
# 6  
Old 05-30-2014
Just make header file like this

Code:
cat >header<<EOF
before=1
CI Name=
CI Description=

after=3
abc=<blank>
EOF

O/p would be

Code:
CI Name,CI Description,CI Ref,SerialNumber,Last Report Time,abc
,,VMware-42 2b cb d6 e7 36 ec 73-f4 77 bd 2b f2 6f 25 5d,VMware-42 2b cb d6 e7 36 ec 73-f4 77 bd 2b f2 6f 25 5d,"Fri, 30 May 2014 12:23:13 +0000",<blank>
,,VMware-42 2b 79 e0 da 18 2e 9e-17 b2 6e e1 67 df 3c 6b,VMware-42 2b 79 e0 da 18 2e 9e-17 b2 6e e1 67 df 3c 6b,"Fri, 30 May 2014 12:26:08 +0000",<blank>

with header
Code:
cat >header<<EOF
before=1
=
=

after=3
abc=<blank>
EOF


O/p would be
Code:
,,CI Ref,SerialNumber,Last Report Time,abc
,,VMware-42 2b cb d6 e7 36 ec 73-f4 77 bd 2b f2 6f 25 5d,VMware-42 2b cb d6 e7 36 ec 73-f4 77 bd 2b f2 6f 25 5d,"Fri, 30 May 2014 12:23:13 +0000",<blank>
,,VMware-42 2b 79 e0 da 18 2e 9e-17 b2 6e e1 67 df 3c 6b,VMware-42 2b 79 e0 da 18 2e 9e-17 b2 6e e1 67 df 3c 6b,"Fri, 30 May 2014 12:26:08 +0000",<blank>

Last edited by Akshay Hegde; 05-30-2014 at 01:03 PM..
# 7  
Old 06-02-2014
Hi Akshaay .. i have used the below script :
Code:
 
# cat csv.sh
#!/bin/bash
cat >header<<EOF
before=1
CI Name=
CI Description=
after=3
abc=<blank>
EOF
awk '
BEGIN{
        file="header"; OFS=","
        while(getline line <file)
        {
                split(line,Arr,/=/)
                if(tolower(Arr[1])=="before")
                {
                        key = "b"
                        n = Arr[2]
                        continue
                }
                else if(tolower(Arr[1])=="after")
                {
                        key = "a"
                        n = Arr[2]
                        continue
                }
 
                if(length(line))
                {
                        H[n FS key FS 1] = ((n FS key FS 1) in H) ? H[n FS key FS 1] OFS Arr[1] : Arr[1]
                        H[n FS key FS 0] = ((n FS key FS 0) in H) ? H[n FS key FS 0] OFS Arr[2] : Arr[2]
                }
        }
        close(file)
 
    }
    {
        c=0 ; str = ""
        $0 = $0","
        while($0) {
                        match($0,/ *"[^"]*" *,|[^,]*,/)
                        f=substr($0,RSTART,RLENGTH)
                        gsub(/,$/,"",f)
                        c++
                        ind = FNR==1 ? FNR : 0
                        f = ((c FS "a" FS ind) in H) ? \
                            f OFS H[c FS "a" FS ind] : \
                            ((c FS "b" FS ind) in H) ? H[c FS "b" FS ind] OFS f : f
                        str = str == "" ? f : str OFS f
                        $0 = substr($0,RLENGTH+1)
                  }
                print str
    }
   ' sample.csv

and its giving below output :
Code:
 
,abcame,CI Description,CI Ref,SerialNumber,Last Report Time
,,VMware-42 2b cb d6 e7 36 ec 73-f4 77 bd 2b f2 6f 25 5d,VMware-42 2b cb d6 e7 36 ec 73-f4 77 bd 2b f2 6f 25 5d,"Fri,<blank>, 30 May 2014 12:23:13 +0000"
,,VMware-42 2b 79 e0 da 18 2e 9e-17 b2 6e e1 67 df 3c 6b,VMware-42 2b 79 e0 da 18 2e 9e-17 b2 6e e1 67 df 3c 6b,"Fri,<blank>, 30 May 2014 12:26:08 +0000"
,,VMware-42 2b e7 9e 5e c6 48 b6-25 36 6e 1e da 6c 6d d2,VMware-42 2b e7 9e 5e c6 48 b6-25 36 6e 1e da 6c 6d d2,"Fri,<blank>, 30 May 2014 12:39:54 +0000"
,,VMware-42 2b b0 e5 dc 56 bd 89-f2 87 de 0b 17 11 43 6e,VMware-42 2b b0 e5 dc 56 bd 89-f2 87 de 0b 17 11 43 6e,"Fri,<blank>, 30 May 2014 12:39:58 +0000"
,,VMware-42 2b e6 f0 6a 81 a5 f8-58 f3 9b 07 71 31 10 f8,VMware-42 2b e6 f0 6a 81 a5 f8-58 f3 9b 07 71 31 10 f8,"Fri,<blank>, 30 May 2014 12:46:30 +0000"
,,VMWARE-42_2B_6A_35_14_1E_87_54-35_75_8E_2E_33_66_94_44,VMWARE-42_2B_6A_35_14_1E_87_54-35_75_8E_2E_33_66_94_44,"Fri,<blank>, 30 May 2014 12:49:21 +0000"
,,VMware-42 2b d9 2d de 16 64 96-43 c3 f8 4a c2 18 c7 8b,VMware-42 2b d9 2d de 16 64 96-43 c3 f8 4a c2 18 c7 8b,"Fri,<blank>, 30 May 2014 12:51:46 +0000"
,,VMware-42 2b dd 58 9f 55 f0 c5-04 06 3a a4 26 dd cc 3c,VMware-42 2b dd 58 9f 55 f0 c5-04 06 3a a4 26 dd cc 3c,"Fri,<blank>, 30 May 2014 12:52:45 +0000"
,,VMware-42 2b e5 49 0f ac 9c a7-23 bd aa 06 b2 3c df 41,VMware-42 2b e5 49 0f ac 9c a7-23 bd aa 06 b2 3c df 41,"Fri,<blank>, 30 May 2014 12:52:50 +0000"
,,


the file which i am suing is as below , also it will have n number of rows which depends upon the number of computers :

Code:
 
cat sample.csv
CI Ref,SerialNumber,Last Report Time
VMware-42 2b cb d6 e7 36 ec 73-f4 77 bd 2b f2 6f 25 5d,VMware-42 2b cb d6 e7 36 ec 73-f4 77 bd 2b f2 6f 25 5d,"Fri, 30 May 2014 12:23:13 +0000"
VMware-42 2b 79 e0 da 18 2e 9e-17 b2 6e e1 67 df 3c 6b,VMware-42 2b 79 e0 da 18 2e 9e-17 b2 6e e1 67 df 3c 6b,"Fri, 30 May 2014 12:26:08 +0000"
VMware-42 2b e7 9e 5e c6 48 b6-25 36 6e 1e da 6c 6d d2,VMware-42 2b e7 9e 5e c6 48 b6-25 36 6e 1e da 6c 6d d2,"Fri, 30 May 2014 12:39:54 +0000"
VMware-42 2b b0 e5 dc 56 bd 89-f2 87 de 0b 17 11 43 6e,VMware-42 2b b0 e5 dc 56 bd 89-f2 87 de 0b 17 11 43 6e,"Fri, 30 May 2014 12:39:58 +0000"
VMware-42 2b e6 f0 6a 81 a5 f8-58 f3 9b 07 71 31 10 f8,VMware-42 2b e6 f0 6a 81 a5 f8-58 f3 9b 07 71 31 10 f8,"Fri, 30 May 2014 12:46:30 +0000"
VMWARE-42_2B_6A_35_14_1E_87_54-35_75_8E_2E_33_66_94_44,VMWARE-42_2B_6A_35_14_1E_87_54-35_75_8E_2E_33_66_94_44,"Fri, 30 May 2014 12:49:21 +0000"
VMware-42 2b d9 2d de 16 64 96-43 c3 f8 4a c2 18 c7 8b,VMware-42 2b d9 2d de 16 64 96-43 c3 f8 4a c2 18 c7 8b,"Fri, 30 May 2014 12:51:46 +0000"
VMware-42 2b dd 58 9f 55 f0 c5-04 06 3a a4 26 dd cc 3c,VMware-42 2b dd 58 9f 55 f0 c5-04 06 3a a4 26 dd cc 3c,"Fri, 30 May 2014 12:52:45 +0000"
VMware-42 2b e5 49 0f ac 9c a7-23 bd aa 06 b2 3c df 41,VMware-42 2b e5 49 0f ac 9c a7-23 bd aa 06 b2 3c df 41,"Fri, 30 May 2014 12:52:50 +0000"

Could you please let me know where its going wrong...

thanks
Last edited by omkar.jadhav; 06-02-2014 at 05:06 AM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Inserting a blank line at the end of a .txt file?

Hi there, I am having this problem: a) I am uploading a txt file from windows (notepad) with some Gaussian 09 command lines; b) Gaussian needs in certain command files, that those files have a blank line at the end of the file! c) I open the command file with vi and no blank line at the of... (2 Replies)
Discussion started by: luismga
2 Replies

2. Shell Programming and Scripting

Detect blank spaces in a CSV file

I'm monitoring a WLAN network to keep track of new SSIDs popping up. The SSIDs are stored along with the AP MAC address and a few other parameters in a CSV file. A typical line could look like this: 18:70:9f:e3:80:aa 10:11:15 MyNetwork 2437 Now, the problem is that some networks use SSIDs... (4 Replies)
Discussion started by: Zooma
4 Replies

3. Shell Programming and Scripting

Match columns from two csv files and update field in one of the csv file

Hi, I have a file of csv data, which looks like this: file1: 1AA,LGV_PONCEY_LES_ATHEE,1,\N,1,00020460E1,0,\N,\N,\N,\N,2,00.22335321,0.00466628 2BB,LES_POUGES_ASF,\N,200,200,00006298G1,0,\N,\N,\N,\N,1,00.30887539,0.00050312... (10 Replies)
Discussion started by: djoseph
10 Replies

4. Shell Programming and Scripting

Randomly inserting extra columns into csv file

Hi Tech Guru, I have a test file as below , which needs some more fields to be populated randomly : dks3243;12;20130823;1420;25m;0;syt dks3243;rocy;10 dks3243;kiop;18 sde21p4;77;20151210;8479;7py;9;vfr sde21p4;temp;67 sfq6i01;12;20120123;3412;4rd;7;jui sfq6i01;uymk;90 sfq6i01;kiop;51 ... (8 Replies)
Discussion started by: Lokesha
8 Replies

5. Shell Programming and Scripting

Inserting new fields to a csv file

hi I have a csv file with few rows > cat job_stat 1,jobname1,somthing,somthing 2,jobname2,somthing,somthing 3,jobname3,somthing,somthing 4,jobname4,somthing,somthing I want to add few columns after the 2nd column and then append rest of the columns after the 3rd newly added... (3 Replies)
Discussion started by: midhun19
3 Replies

6. Shell Programming and Scripting

Deleting all the fields(columns) from a .csv file if all rows in that columns are blanks

Hi Friends, I have come across some files where some of the columns don not have data. Key, Data1,Data2,Data3,Data4,Data5 A,5,6,,10,, A,3,4,,3,, B,1,,4,5,, B,2,,3,4,, If we see the above data on Data5 column do not have any row got filled. So remove only that column(Here Data5) and... (4 Replies)
Discussion started by: ks_reddy
4 Replies

7. Shell Programming and Scripting

Inserting additional comma delimiters in a csv file, after and before certian fields.

Hello I have a csv file which I need to insert addtional commas into. The csv is of the format field1,field2,field3,field4,...etc...,field13,field14 I need to add extra commas in each record so that the final output looks like ... (1 Reply)
Discussion started by: kamal_p_99
1 Replies

8. UNIX for Advanced & Expert Users

Help in Deleting columns and Renaming Mutliple columns in a .Csv File

Hi All, i have a .Csv file in the below format startTime, endTime, delta, gName, rName, rNumber, m2239max, m2239min, m2239avg, m100016509avg, m100019240max, metric3min, m100019240avg, propValues 11-Mar-2012 00:00:00, 11-Mar-2012 00:05:00, 300.0, vma3550a, a-1_CPU Index<1>, 200237463, 0.0,... (9 Replies)
Discussion started by: mahi_mayu069
9 Replies

9. Shell Programming and Scripting

How to remove Blank rows in a csv file

Hi, I need help to remove blank rows at the end of file. Sample data: "Oslo, Symra kino",Oslo,130-7,Symra 1,130-7-91 "Tønsberg, Brygga Kino SF",Tønsberg,202-1,Tønsberg SF 4,202-1-4 ,,,, ,,,, ,,,, ,,,, Expected data: "Oslo, Symra kino",Oslo,130-7,Symra 1,130-7-91 "Tønsberg, Brygga... (6 Replies)
Discussion started by: cnraja
6 Replies

10. Shell Programming and Scripting

Inserting 2 columns from a file to another with nawk

Hello all, I have these 2 files File1 123 100 456 200 789 300 File2 |1|2|3||4|5||6| |1|2|3||4|5||6| |1|2|3||4|5||6| I need an output like : |1|2|3|123|4|5|100|6| |1|2|3|456|4|5|200|6| |1|2|3|789|4|5|300|6| (3 Replies)
Discussion started by: rany1
3 Replies
Login or Register to Ask a Question