Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Need help in converting the file format

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Need help in converting the file format
# 1  
Old 02-06-2014
Need help in converting the file format
Hi All,

I need help in converting the mentioned file format into desired output format using awk. Could anyone help me in this?
Below is the input..
Code:
                 Date   Account    Campaign    AdGroup    Keyword   Conversion   Revenue   Var1   Var2   Var3   Var4   Var5       10   20   30   40   50   60   70   80   90   100   110   120       110   120   130   140   150   160   170   180   190   200   210   220       210   220   230   240   250   260   270   280   290   300   310   320       310   320   330   340   350   360   370   380   390   400   410   420

Desired output is

Code:
                     Date   Account    Campaign    AdGroup    Keyword   Conversion   Revenue   Event Type   Event Value       10   20   30   40   50   60   70   -1   0       110   120   130   140   150   160   170   -1   0       210   220   230   240   250   260   270   -1   0       310   320   330   340   350   360   370   -1   0       
  
  
  
  
  
  
  
  
      10   20   30   40   50   0   0              Var1   80         10   20   30   40   50   0   0              Var3   100       10   20   30   40   50   0   0              Var4   110       10   20   30   40   50   0   0              Var5   120       
  
  
  
  
  
  
  
  
      110   120   130   140   150   0   0              Var1   180       110   120   130   140   150   0   0              Var2   190       110   120   130   140   150   0   0 
             Var3   200       110   120   130   140   150   0   0              Var4   210       110   120   130   140   150   0   0              Var5   220       
  
  
  
  
  
  
  
  
      210   220   230   240   250   0   0              Var1   280       210   220   230   240   250   0   0              Var2   290       210   220   230   240   250   0   0              Var3   300       210   220   230   240   250   0   0              Var4   310       210   220   230   240   250   0   0              Var5   320       
  
  
  
  
  
  
  
  
      310   320   330   340   350   0   0              Var1   380       310   320   330   340   350   0   0              Var2   390       310   320   330   340   350   0   0              Var3   400       310   320   330   340   350   0   0              Var4   410       310   320   330   340   350   0   0              Var5   420

---------- Post updated at 12:53 AM ---------- Previous update was at 12:20 AM ----------

Please ignore the above details

Actual input is,
Code:
Date,Account,Campaign,AdGroup,Keyword,Conversion,Revenue,Var1,Var2,Var3,Var4,Var5
10,20,30,40,50,60,70,80,90,100,110,120
110,120,130,140,150,160,170,180,190,200,210,220
210,220,230,240,250,260,270,280,290,300,310,320
310,320,330,340,350,360,370,380,390,400,410,420


Desired output is
Code:
Date,Account, Campaign, AdGroup, Keyword,Conversion,Revenue,Event Type,Event Value
10,20,30,40,50,60,70,-1,0
110,120,130,140,150,160,170,-1,0
210,220,230,240,250,260,270,-1,0
310,320,330,340,350,360,370,-1,0
10,20,30,40,50,0,0,Var1,80
10,20,30,40,50,0,0,Var2,90
10,20,30,40,50,0,0,Var3,100
10,20,30,40,50,0,0,Var4,110
10,20,30,40,50,0,0,Var5,120
110,120,130,140,150,0,0,Var1,180
110,120,130,140,150,0,0,Var2,190
110,120,130,140,150,0,0,Var3,200
110,120,130,140,150,0,0,Var4,210
110,120,130,140,150,0,0,Var5,220
210,220,230,240,250,0,0,Var1,280
210,220,230,240,250,0,0,Var2,290
210,220,230,240,250,0,0,Var3,300
210,220,230,240,250,0,0,Var4,310
210,220,230,240,250,0,0,Var5,320
310,320,330,340,350,0,0,Var1,380
310,320,330,340,350,0,0,Var2,390
310,320,330,340,350,0,0,Var3,400
310,320,330,340,350,0,0,Var4,410
310,320,330,340,350,0,0,Var5,420

Last edited by Don Cragun; 02-06-2014 at 02:38 AM.. Reason: Add CODE tags.
# 2  
Old 02-06-2014
I don't understand anything on the desired output! Smilie
# 3  
Old 02-06-2014
first bunch of four line here is input as it is till 7th column and for 8th and 9th column we append -1 and 0 ..Which means when you read the source file first we will have to print columns till fileds with var* starts and append two extra columns after which we will have to transpose the columns naming var with header as it is shown above .. For eg if there 5 columns naming var and and 5 rows for the columns upto var then there has to be 25 records in the output plus the above 5 records.
# 4  
Old 02-06-2014
Try this awk solution:

Code:
awk -F, 'NR==1{
   OFS=","
   for(i=8;i<=NF;i++) {
      H[i]=$i
      $i=""
   }
   $8="Event Type"
   $9="Event Value"
   NF=9
   print
   next
}
{L[NR]=$0}
END{
  for(l=2;l<=NR;l++) {
       split(L[l],V,",")
       print V[1],V[2],V[3],V[4],V[5],V[6],V[7],-1,0
  }
  for(l=2;l<=NR;l++) {
     for(m=8;m<i;m++) {
       split(L[l],V,",")
       print V[1],V[2],V[3],V[4],V[5],0,0,H[m],V[m]
     }
  }
}' infile

# 5  
Old 02-07-2014
Thanks a ton Chubler_XL Smilie SmilieSmilieSmilie You made my day!!

---------- Post updated 02-07-14 at 12:43 AM ---------- Previous update was 02-06-14 at 01:42 AM ----------

Hi Chubler,

Below is the mofdified code of yours for my requirement. I am facing a small issue in here,

Code:
awk -F, 'NR==1{
   OFS=","
   for(i=32;i<=NF;i++) {
      H[i]=$i
      $i=""
   }
   $32="Event Type"
   $33="Event Value"
   NF=33
   print
   next
}
{L[NR]=$0}
END{
  for(l=2;l<=NR;l++) {
       split(L[l],V,",")
print V[1],V[2],V[3],V[4],V[5],V[6],V[7],V[8],V[9],V[10],V[11],V[12],V[13],V[14],V[15],V[16],V[17],V[18],V[19],V[20],V[21],V[22],V[23],V[24],V[25],V[26],V[27],V[28],V[29],V[30],V[31],-1,0
  }
  for(l=2;l<=NR;l++) {
     for(m=32;m<i;m++) {
       split(L[l],V,",")
     print V[1],V[2],V[3],V[4],V[5],V[6],V[7],V[8],V[9],V[10],V[11],V[12],V[13],V[14],V[15],V[16],V[17],V[18],V[19],V[20],V[21],V[22],V[23],0,0,0,0,0,0,0,0,H[m],V[m]     }
  }
}' temp.csv

I am getting almost closed to desired output but last value is getting shifted to next line

as shown below sample output.

Code:
USD66Monster US Seeker SEMLocation CitiesActiveGoogleWest Covina - ExactActivewest covina careerExactActive59:06.03.02http://jobs.monster.com/l-west-covina%%###%%-ca.aspx?WT.srch=1&WT.mc_n=olm_sk_srch_ggl_loc&k_clickid=_kenshoo_clickid_&k_trackingid=66x101097189454929098647Kenshoo.API.Core@gmail.com1921.86E+09101091.95E+1000000000resume_finished0USD66Monster US Seeker SEMLocation CitiesActiveGoogleWest Covina - ExactActivewest covina careerExactActive59:06.03.02http://jobs.monster.com/l-west-covina%%###%%-ca.aspx?WT.srch=1&WT.mc_n=olm_sk_srch_ggl_loc&k_clickid=_kenshoo_clickid_&k_trackingid=66x101097189454929098647Kenshoo.API.Core@gmail.com1921.86E+09101091.95E+1000000000school_connect_w_fb0USD66Monster US Seeker SEMLocation CitiesActiveGoogleWest Covina - ExactActivewest covina careerExactActive59:06.03.02http://jobs.monster.com/l-west-covina%%###%%-ca.aspx?WT.srch=1&WT.mc_n=olm_sk_srch_ggl_loc&k_clickid=_kenshoo_clickid_&k_trackingid=66x101097189454929098647Kenshoo.API.Core@gmail.com1921.86E+09101091.95E+1000000000school_follow0USD66Monster US Seeker SEMLocation CitiesActiveGoogleWest Covina - ExactActivewest covina careerExactActive59:06.03.02http://jobs.monster.com/l-west-covina%%###%%-ca.aspx?WT.srch=1&WT.mc_n=olm_sk_srch_ggl_loc&k_clickid=_kenshoo_clickid_&k_trackingid=66x101097189454929098647Kenshoo.API.Core@gmail.com1921.86E+09101091.95E+1000000000search_clicks0USD66Monster US Seeker SEMLocation CitiesActiveGoogleWest Covina - ExactActivewest covina careerExactActive59:06.03.02http://jobs.monster.com/l-west-covina%%###%%-ca.aspx?WT.srch=1&WT.mc_n=olm_sk_srch_ggl_loc&k_clickid=_kenshoo_clickid_&k_trackingid=66x101097189454929098647Kenshoo.API.Core@gmail.com1921.86E+09101091.95E+1000000000social_clicks0USD66Monster US Seeker SEMLocation CitiesActiveGoogleWest Covina - ExactActivewest covina careerExactActive59:06.03.02http://jobs.monster.com/l-west-covina%%###%%-ca.aspx?WT.srch=1&WT.mc_n=olm_sk_srch_ggl_loc&k_clickid=_kenshoo_clickid_&k_trackingid=66x101097189454929098647Kenshoo.API.Core@gmail.com1921.86E+09101091.95E+1000000000social_impressions0USD66Monster US Seeker SEMLocation CitiesActiveGoogleWest Covina - ExactActivewest covina careerExactActive59:06.03.02http://jobs.monster.com/l-west-covina%%###%%-ca.aspx?WT.srch=1&WT.mc_n=olm_sk_srch_ggl_loc&k_clickid=_kenshoo_clickid_&k_trackingid=66x101097189454929098647Kenshoo.API.Core@gmail.com1921.86E+09101091.95E+1000000000social_spent0USD66Monster US Seeker SEMLocation CitiesActiveGoogleWest Covina - ExactActivewest covina careerExactActive59:06.03.02http://jobs.monster.com/l-west-covina%%###%%-ca.aspx?WT.srch=1&WT.mc_n=olm_sk_srch_ggl_loc&k_clickid=_kenshoo_clickid_&k_trackingid=66x101097189454929098647Kenshoo.API.Core@gmail.com1921.86E+09101091.95E+1000000000social_unique_clicks0USD66Monster US Seeker SEMLocation CitiesActiveGoogleWest Covina - ExactActivewest covina careerExactActive59:06.03.02http://jobs.monster.com/l-west-covina%%###%%-ca.aspx?WT.srch=1&WT.mc_n=olm_sk_srch_ggl_loc&k_clickid=_kenshoo_clickid_&k_trackingid=66x101097189454929098647Kenshoo.API.Core@gmail.com1921.86E+09101091.95E+1000000000social_unique_impressions0USD66Monster US Seeker SEMLocation CitiesActiveGoogleWest Covina - ExactActivewest covina careerExactActive59:06.03.02http://jobs.monster.com/l-west-covina%%###%%-ca.aspx?WT.srch=1&WT.mc_n=olm_sk_srch_ggl_loc&k_clickid=_kenshoo_clickid_&k_trackingid=66x101097189454929098647Kenshoo.API.Core@gmail.com1921.86E+09101091.95E+1000000000unique_clicks0USD66Monster US Seeker SEMLocation CitiesActiveGoogleWest Covina - ExactActivewest covina careerExactActive59:06.03.02http://jobs.monster.com/l-west-covina%%###%%-ca.aspx?WT.srch=1&WT.mc_n=olm_sk_srch_ggl_loc&k_clickid=_kenshoo_clickid_&k_trackingid=66x101097189454929098647Kenshoo.API.Core@gmail.com1921.86E+09101091.95E+1000000000unique_impressions0USD66Monster US Seeker SEMLocation CitiesActiveGoogleWest Covina - ExactActivewest covina careerExactActive59:06.03.02http://jobs.monster.com/l-west-covina%%###%%-ca.aspx?WT.srch=1&WT.mc_n=olm_sk_srch_ggl_loc&k_clickid=_kenshoo_clickid_&k_trackingid=66x101097189454929098647Kenshoo.API.Core@gmail.com1921.86E+09101091.95E+1000000000user_add_connection0USD66Monster US Seeker SEMLocation CitiesActiveGoogleWest Covina - ExactActivewest covina careerExactActive59:06.03.02http://jobs.monster.com/l-west-covina%%###%%-ca.aspx?WT.srch=1&WT.mc_n=olm_sk_srch_ggl_loc&k_clickid=_kenshoo_clickid_&k_trackingid=66x101097189454929098647Kenshoo.API.Core@gmail.com1921.86E+09101091.95E+1000000000user_connect_with_fb0USD66Monster US Seeker SEMLocation CitiesActiveGoogleWest Covina - ExactActivewest covina careerExactActive59:06.03.02http://jobs.monster.com/l-west-covina%%###%%-ca.aspx?WT.srch=1&WT.mc_n=olm_sk_srch_ggl_loc&k_clickid=_kenshoo_clickid_&k_trackingid=66x101097189454929098647Kenshoo.API.Core@gmail.com1921.86E+09101091.95E+1000000000view_all_employees0

Please help Smilie



Below are the sample input records,

Code:
CurrencyProfile IDProfile NameCampaign NameCampaign StatusChannelAdGroupAdGroup StatusKeywordMatch TypeStatusKeyword Creation DateBidLand URLQuality ScoreCampaign Id in KenshooCampaign ID in ChannelChannel IDAccount LoginAdGroup ID in KenshooAdGroup ID in ChannelKeyword Id in KenshooKeyword ID in ChannelImpressionsClicksConversionsCostCost/Conv.Avg Pos.Rev.Profitaj_tab_rss_xmlall_jobs_viewsapp installedapp%20installedapp_installedappdownloadedapplandingpageapply_completeapply_startbk_fb_install_buttoncompany_conn_viewscompany_connect_w_fbcompany_followconnectioninviteconvemployer_conversionendorsementgivenfeatured_jobs_viewsfj_tab_bk_clicksfj_tab_monster_clickfollow_us_clicksimportmonsterjob_search_conversionjob_view_conversionsjobpostedjobs_connect_w_fbjobs_interestedjobs_tab_viewsjobscom_searchjobsearchedmobileconnectioninvitemobilejobsearchmobilesendmessagenew_account_finishedresume_finishedschool_connect_w_fbschool_followsearch_clickssocial_clickssocial_impressionssocial_spentsocial_unique_clickssocial_unique_impressionsunique_clicksunique_impressionsuser_add_connectionuser_connect_with_fbview_all_employeesUSD66Monster US Seeker SEMLocation CitiesActiveGoogleJefferson County - BroadActivecareer jefferson countyBroadActive59:01.01.02http://jobs.monster.com/l-alabama.aspx?WT.srch=1&WT.mc_n=olm_sk_srch_ggl_loc&k_clickid=_kenshoo_clickid_&k_trackingid=66x100006.2857189454929098647Kenshoo.API.Core@gmail.com1861.86E+09100001.95E+101319903.901.1554970-3.9000000000000000000000000000000000000000000000000USD66Monster US Seeker SEMLocation CitiesActiveGoogleNassau - BroadActivenassau job searchBroadActive59:01.00.5http://jobs.monster.com/l-nassau,-ny.aspx?WT.srch=1&WT.mc_n=olm_sk_srch_ggl_loc&k_clickid=_kenshoo_clickid_&k_trackingid=66x100019189454929098647Kenshoo.API.Core@gmail.com1871.86E+09100011.95E+10456482230.8115.4052.2671120-30.81000000005000000000000000000000000000000000000000USD66Monster US Seeker SEMCompetitors - LocationsActiveGoogleCompetitors - Tampa - BroadActiveindeed job tampaBroadActive34:21.01.17http://jobs.monster.com/l-tampa,-fl.aspx?WT.srch=1&WT.mc_n=olm_sk_srch_ggl_cmpt_loc&k_clickid=_kenshoo_clickid_&k_trackingid=66x1000125193666795418647Kenshoo.API.Core@gmail.com21902.55E+091000123.01E+1036800004.33233700000000000000000000000000000000000000000000000000

---------- Post updated at 01:26 AM ---------- Previous update was at 12:43 AM ----------

Can anyone help please?
# 6  
Old 02-07-2014
So your input lines have " " (space) as the separator not "," (comma), right?

If so try removing -F parameter from the arguments like this:

Code:
 awk  'NR==1{
...

Do you still have a header line with the column titles?
# 7  
Old 02-07-2014
No Chubler, It is a comma separated file and delimiter is comma but only and only last value is getting shifted.. Please find the attachments.
sample_input.xls (28.0 KB) 
current_output.xls (87.5 KB) 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

help required in converting a file format

My file format: -------------------------------------------------- Complete Consistency Check Valid Area : VALID:VALID Started by : esanwad Started at : Thu Dec 11 16:04:46 2014 CNA version : R21H04_EC08 Check range : AREA VALID/VALID ... (4 Replies)
Discussion started by: Gautam Banerjee
4 Replies

2. Shell Programming and Scripting

Format problem while converting text file to csv

Hi , I need a help in following scenario.I tried searching in google but couldn't able to find the exact answer. Sorry if i am re-posting already answered query. While i am trying to convert into log file into csv i couldn't able to get the format which i am looking for. I converted file... (4 Replies)
Discussion started by: varmas424
4 Replies

3. UNIX for Advanced & Expert Users

Converting the date format in a flat file

Hi All, I am new to this forum, could any one help me out in resolving the below issue. Input of the flat file contains several lines of text for example find below: 5022090,2,4,7154,88,,,,,4/1/2011 0:00,Z,L,2 5022090,3,1,6648,88,,,,,4/1/2011 0:00,Z,,1... (0 Replies)
Discussion started by: av_sagar
0 Replies

4. Shell Programming and Scripting

Converting windows format file to unix format using script

Hi, I am having couple of files which i used to copy from windows to Linux, so now in case of text files (CTRL^M) appears at end of line. I know i can convert this windows format file to unix format file by running dos2unix. My requirement here is that i want to do it automatically using a... (5 Replies)
Discussion started by: sarbjit
5 Replies

5. UNIX for Dummies Questions & Answers

Converting binary file to readable format in Ksh

In Unix/Ksh, when I try to look inside a file it says that the file may be a binary file and if I want to see it anyway. When i say 'yes', it shows me the content filled with unreadable symbols (looks like binary). Is there a command that I can run from the Unix prompt to convert/translate that... (3 Replies)
Discussion started by: arthurs
3 Replies

6. Shell Programming and Scripting

Converting Unicode file to UTF8 format

Hi, I have a file in my desktop which is a unicode format. After this file is transferred to Unix using FTP, we are seeing some special character (like rectangle box type) at the first line. The same file is saved as UTF8 (using textpad tool, selecting encode to UTF-8 option) on my desktopand... (7 Replies)
Discussion started by: vfrg
7 Replies

7. Shell Programming and Scripting

Converting file format

My input file is Pipe delimited with 10 fields, I am trying to create a tab delimited output file with 6 fields from the provided input file. Below is sample data Input file abc||2|PIN|num||||www.123.com|abc@123.com| bcd||2|PIN|num|||||abc@123.com|... (3 Replies)
Discussion started by: pasupuleti81
3 Replies

8. Shell Programming and Scripting

Sybase Interface file and converting in text format.

Does anyone knows how to decode the address in interface file using shell , i have done it using perl but can it be done in shell. master tli tcp /dev/tcp \x00021004ac1414230000000000000000 query tli tcp /dev/tcp \x00021004ac1414230000000000000000 (0 Replies)
Discussion started by: dinjo_jo
0 Replies

9. Shell Programming and Scripting

converting config file to csv format

Hello, For 2 days now i've been searching for a solution to this. I am now beginning to doubt this is even possible. It's even harder when you don't know how to search for it. (which keywords generate enough relevancy etc..) I need to parse a config file to generate a CSV file in return. It... (7 Replies)
Discussion started by: zer0dvide
7 Replies

10. UNIX for Dummies Questions & Answers

Converting the File Creation Date to a new format

I need to capture a file's creation/modification date and time and convert this to a different format, whilst I can easily get the existing format from a ls -l | awk ' { print $......}' or a cut command I do not know how to convert it to a desired format? I should add that at present the ls -l... (1 Reply)
Discussion started by: barney_clough
1 Replies
Login or Register to Ask a Question