Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Formatting data to put it in the excel file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Formatting data to put it in the excel file
# 8  
Old 09-22-2015
Code:
$1 == HDArr[1]          {if (PR) PRT()
                         PR=1
                        }

This assumes that a record begins with the first defined field and prints (using function PRT) the old record, unless that was the first record (for which PR is undefined and thus FALSE)
Code:
HD ~ SEP $1 SEP         {T=$0                          
                         sub ($1 "[^:]*:[\t ]*", "", T) 
                         sub (/[\t ]*$/, "", T)        
                         RES[$1]=T                     
                        }

HD holds the fields' names, separated and enclosed by SEP. If it matches field 1 enclosed by SEPs, the output is prepared by
a) removing field 1 (= field name) plus white space plus colon plus further white space from line (=$0)
b) removing trailing white space from $0
c) saving the trimmed line into RES[field name] for printing

Printing is done in PRT by printing all RES elements in the order given in the HD defined in the NR==1 action.
This User Gave Thanks to RudiC For This Post:
rahul2662
# 9  
Old 09-23-2015
I have got the following file after running my script.

Code:
,Policy Name:       ALP-Backup,  Policy Type:         NBU-Catalog,  Active:              yes,  Effective date:      08/19/2013 05:07:12,  Mult. Data Streams:  no,  Client Encrypt:      no,  Checkpoint:          no,  Policy Priority:     0,  Max Jobs/Policy:     1,  Disaster Recovery:   0,  Collect BMR info:    no,  Residence:           TY_UL_PA,  Volume Pool:         NetBackup,  Server Group:        *ANY*,  Keyword:             (none specified),  Data Classification:       -,  Residence is Storage Lifecycle Policy:    no,  Application Discovery:      no,  Discovery Lifetime:      0 seconds,ASC Application and attributes: (none defined),  Granular Restore Info:  no,  Ignore Client Direct:  no,Enable Metadata Indexing:  no,Index server name:  NULL,  Use Accelerator:  no,  HW/OS/Client:  Linux         Linux         p1nur45th,  Include:  CATALOG_DRIVEN_BACKUP,  Schedule:              Full,    Type:                Full Backup,    Frequency:           every 7 days,    Maximum MPX:         1,    Synthetic:           0,    Checksum Change Detection: 0,    PFI Recovery:        0,    Retention Level:     1 (2 weeks),    Number Copies:       1,    Fail on Error:       0,    Residence:           (specific storage unit not required),    Volume Pool:         (same as policy volume pool),    Server Group:        (same as specified for policy),    Residence is Storage Lifecycle Policy:         0,    Schedule indexing:     0,    Daily Windows:,          Sunday     18:00:00  -->  Monday     05:00:00,  Schedule:              Incremental,    Type:                Differential Incremental Backup,    Frequency:           every 1 day,    Maximum MPX:         1,    Synthetic:           0,    Checksum Change Detection: 0,    PFI Recovery:        0,    Retention Level:     1 (2 weeks),    Number Copies:       1,    Fail on Error:       0,    Residence:           (specific storage unit not required),    Volume Pool:         (same as policy volume pool),    Server Group:        (same as specified for policy),    Residence is Storage Lifecycle Policy:         0,    Schedule indexing:     0,    Daily Windows:,          Monday     18:00:00  -->  Tuesday    05:00:00,          Tuesday    18:00:00  -->  Wednesday  05:00:00,          Wednesday  18:00:00  -->  Thursday   05:00:00,          Thursday   18:00:00  -->  Friday     05:00:00,          Friday     18:00:00  -->  Saturday   05:00:00,          Saturday   18:00:00  -->  Sunday     05:00:00,,

,Policy Name:       clmjk-001,  Policy Type:         MS-Windows,  Active:              yes,  Effective date:      07/15/2013 21:26:40,  Mult. Data Streams:  no,  Client Encrypt:      no,  Checkpoint:          yes,       Interval:       15,  Policy Priority:     0,  Max Jobs/Policy:     Unlimited,  Disaster Recovery:   0,  Collect BMR info:    no,  Residence:           PCDF_EN,  Volume Pool:         NetBackup,  Server Group:        *ANY*,  Keyword:             (none specified),  Data Classification:       -,  Residence is Storage Lifecycle Policy:    no,  Application Discovery:      no,  Discovery Lifetime:      0 seconds,ASC Application and attributes: (none defined),  Granular Restore Info:  no,  Ignore Client Direct:  no,Enable Metadata Indexing:  no,Index server name:  NULL,  Use Accelerator:  no,  HW/OS/Client:  Windows-x86   Windows2003   clmjk-001,  Include:  ALL_LOCAL_DRIVES,  Schedule:              Full,    Type:                Full Backup,    Frequency:           every 7 days,    Maximum MPX:         4,    Synthetic:           0,    Checksum Change Detection: 0,    PFI Recovery:        0,    Retention Level:     24 (45 days),    Number Copies:       1,    Fail on Error:       0,    Residence:           (specific storage unit not required),    Volume Pool:         (same as policy volume pool),    Server Group:        (same as specified for policy),    Residence is Storage Lifecycle Policy:         0,    Schedule indexing:     0,    Daily Windows:,          Saturday   12:00:00  -->  Saturday   22:00:00,  Schedule:              Incremental,    Type:                Differential Incremental Backup,    Frequency:           every 1 day,    Maximum MPX:         4,    Synthetic:           0,    Checksum Change Detection: 0,    PFI Recovery:        0,    Retention Level:     1 (2 weeks),    Number Copies:       1,    Fail on Error:       0,    Residence:           (specific storage unit not required),    Volume Pool:         (same as policy volume pool),    Server Group:        (same as specified for policy),    Residence is Storage Lifecycle Policy:         0,    Schedule indexing:     0,    Daily Windows:,          Sunday     19:00:00  -->  Monday     03:00:00,          Monday     19:00:00  -->  Tuesday    03:00:00,          Tuesday    19:00:00  -->  Wednesday  03:00:00,          Wednesday  19:00:00  -->  Thursday   03:00:00,          Thursday   19:00:00  -->  Friday     03:00:00,          Friday     19:00:00  -->  Saturday   03:00:00,

,Policy Name:       Test_Automation,  Policy Type:         Standard,  Active:              no,  Effective date:      07/24/2013 21:26:40,  Mult. Data Streams:  yes,  Client Encrypt:      no,  Checkpoint:          yes,       Interval:       15,  Policy Priority:     0,  Max Jobs/Policy:     Unlimited,  Disaster Recovery:   0,  Collect BMR info:    no,  Residence:           PCDF_EN,  Volume Pool:         NetBackup,  Server Group:        *ANY*,  Keyword:             (none specified),  Data Classification:       -,  Residence is Storage Lifecycle Policy:    no,  Application Discovery:      no,  Discovery Lifetime:      0 seconds,ASC Application and attributes: (none defined),  Granular Restore Info:  no,  Ignore Client Direct:  no,Enable Metadata Indexing:  no,Index server name:  NULL,  Use Accelerator:  no,  HW/OS/Client:  Linux         IBMpSeriesRed p1nur45th,  Include:  ALL_LOCAL_DRIVES,  Schedule:              Full,    Type:                Full Backup,    Frequency:           every 7 days,    Maximum MPX:         4,    Synthetic:           0,    Checksum Change Detection: 0,    PFI Recovery:        0,    Retention Level:     24 (45 days),    Number Copies:       1,    Fail on Error:       0,    Residence:           (specific storage unit not required),    Volume Pool:         (same as policy volume pool),    Server Group:        (same as specified for policy),    Residence is Storage Lifecycle Policy:         0,    Schedule indexing:     0,    Daily Windows:,          Sunday     00:00:00  -->  Sunday     08:00:00,  Schedule:              Incremental,    Type:                Differential Incremental Backup,    Frequency:           every 1 day,    Maximum MPX:         1,    Synthetic:           0,    Checksum Change Detection: 0,    PFI Recovery:        0,    Retention Level:     1 (2 weeks),    Number Copies:       1,    Fail on Error:       0,    Residence:           (specific storage unit not required),    Volume Pool:         (same as policy volume pool),    Server Group:        (same as specified for policy),    Residence is Storage Lifecycle Policy:         0,    Schedule indexing:     0,    Daily Windows:,          Monday     00:00:00  -->  Monday     10:00:00,          Tuesday    00:00:00  -->  Tuesday    10:00:00,          Wednesday  00:00:00  -->  Wednesday  10:00:00,          Thursday   00:00:00  -->  Thursday   10:00:00,          Friday     00:00:00  -->  Friday     10:00:00,          Saturday   00:00:00  -->  Saturday   10:00:00,

,Policy Name:       nbuumst05_OS,  Policy Type:         Standard,  Active:              yes,  Effective date:      07/14/2013 21:26:40,  Mult. Data Streams:  no,  Client Encrypt:      no,  Checkpoint:          yes,       Interval:       15,  Policy Priority:     0,  Max Jobs/Policy:     Unlimited,  Disaster Recovery:   0,  Collect BMR info:    no,  Residence:           PCDF_EN,  Volume Pool:         NetBackup,  Server Group:        *ANY*,  Keyword:             (none specified),  Data Classification:       -,  Residence is Storage Lifecycle Policy:    no,  Application Discovery:      no,  Discovery Lifetime:      0 seconds,ASC Application and attributes: (none defined),  Granular Restore Info:  no,  Ignore Client Direct:  no,Enable Metadata Indexing:  no,Index server name:  NULL,  Use Accelerator:  no,  HW/OS/Client:  Linux         IBMpSeriesRed p1nur45th,  Include:  ALL_LOCAL_DRIVES,  Schedule:              Full,    Type:                Full Backup,    Frequency:           every 7 days,    Maximum MPX:         4,    Synthetic:           0,    Checksum Change Detection: 0,    PFI Recovery:        0,    Retention Level:     24 (45 days),    Number Copies:       1,    Fail on Error:       0,    Residence:           (specific storage unit not required),    Volume Pool:         (same as policy volume pool),    Server Group:        (same as specified for policy),    Residence is Storage Lifecycle Policy:         0,    Schedule indexing:     0,    Daily Windows:,          Saturday   12:00:00  -->  Saturday   22:00:00,  Schedule:              Incremental,    Type:                Differential Incremental Backup,    Frequency:           every 1 day,    Maximum MPX:         4,    Synthetic:           0,    Checksum Change Detection: 0,    PFI Recovery:        0,    Retention Level:     1 (2 weeks),    Number Copies:       1,    Fail on Error:       0,    Residence:           (specific storage unit not required),    Volume Pool:         (same as policy volume pool),    Server Group:        (same as specified for policy),    Residence is Storage Lifecycle Policy:         0,    Schedule indexing:     0,    Daily Windows:,          Sunday     19:00:00  -->  Monday     03:00:00,          Monday     19:00:00  -->  Tuesday    03:00:00,          Tuesday    19:00:00  -->  Wednesday  03:00:00,          Wednesday  19:00:00  -->  Thursday   03:00:00,          Thursday   19:00:00  -->  Friday     03:00:00,          Friday     19:00:00  -->  Saturday   03:00:00,

In the above file I want to replace every occurence of "," upto first occurence of ":" with ","
For example ",Policy Name:" should be replaced with "," and so on in every line.

Can you please help.
# 10  
Old 09-23-2015
Not without knowing your script.
And, that's exactly what sub ($1 "[^:]*:[\t ]*", "", T)is for.
# 11  
Old 09-23-2015
Hello RudiC ,

I tried the below code as mentioned :

Code:
awk '{sub ($1 "[^:]*:[\t ]*", ""); print }' tmp1.txt

The above command formatted only first field , however I want all the fields in all the rows to be formatted :

for example Input File is as follows ( please note there are 69 fields in the file in each row however to make things simple I have taken only 4 fields) :

Code:
,Policy Name:       Catalog-Backup,  Policy Type:         NBU-Catalog,  Active:              yes,  Effective date:      08/07/2013 05:07:12
,Policy Name:       NI1FS-001_OS,  Policy Type:         MS-Windows,  Active:              yes,  Effective date:      07/15/2013 21:26:40
,Policy Name:       Test_Automation,  Policy Type:         Standard,  Active:              no,  Effective date:      07/15/2013 21:26:40

The output file should be as follows :

Code:
,Catalog-Backup,           NBU-Catalog,         yes,        08/07/2013 05:07:12
,NI1FS-001_OS,        MS-Windows,              yes,        07/15/2013 21:26:40
,Test_Automation,      Standard,             no,       07/15/2013 21:26:40

So basically in the output file every occurence of "," and upto the next occurence of ":" should be replaced with ","
for example :
",XYZABC:" should be replaced with ","

I tried following but it didnt worked :

Code:
awk '{ for (i = 1; i <= NF; i++) {sub ($i "[^:]*:[\t ]*", ""); print }}' tmp1.txt



Could you please help. Thanks a lot for your kind help.

I made a small mistake while writing codes and corrected the same above. Sorry for the same.

Thanks
Rahul
Last edited by rahul2662; 09-23-2015 at 10:54 AM..
# 12  
Old 09-23-2015
Unless either data file or script have been modified, none of those exercises should be necessary. The script as posted working on the data as posted does yield a clean .csv file.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Visit site get data and put in file for use.

I need to go to "vpnbook"vpnbook.com on the web (can't put in name yet)and open "Openvpn" tab On that page I need to get the username:vpnbook and the next line password:???????? I need to put those two in line one and two of a file "pwfile" When I have those I need to open openVPN with the... (1 Reply)
Discussion started by: tytower
1 Replies

2. Shell Programming and Scripting

Script to generate Excel file or to SQL output data to Excel format/tabular format

Hi , i am generating some data by firing sql query with connecting to the database by my solaris box. The below one should be the header line of my excel ,here its coming in separate row. TO_CHAR(C. CURR_EMP_NO ---------- --------------- LST_NM... (6 Replies)
Discussion started by: dani1234
6 Replies

3. Shell Programming and Scripting

Data formatting in CSV file to EXCEL

Hello friends I want to convert an csv file on unix (which is generated by a ETL application) to a formatted excel sheet like .I have roughly like 28 columns 1)All numbers need to be stored as numbers with leading zeros-like format as text for this column to preserve leading zeroes e.g... (6 Replies)
Discussion started by: etldev
6 Replies

4. Shell Programming and Scripting

Writing excel file using perl : Excel file formatting changed

I am trying to create a program where user can input data in certain excel cells using user interface on internet....the programming is on perl and server is unix But when i parse data into excel the formatting of sheets is turned to default and all macro coding removed. What to do...Please... (7 Replies)
Discussion started by: mud_born
7 Replies

5. UNIX for Advanced & Expert Users

put data in excel file using shell script

Hi. I wish to add data in a specific excel file on daily basis.However the currect dat's data should always come on top i.e for example should always occupy cell A7,B7,C7 .. and the data of day before which was earlier on 7th row of each coloumn should move to 8th row..data on 8th row should... (1 Reply)
Discussion started by: kanus
1 Replies

6. Shell Programming and Scripting

how to put data using shell script to a excel file

Hi, Can any one tell me how to put data using shell script to a excel file from text file to other columns of excel file,leaving first column unaffected i.e it should not overwrite data in first column. Say my text file data is: 15-dec-2008 15-dec-2009 16-dec-2008 16-dec-2009 say my first... (1 Reply)
Discussion started by: siri_886
1 Replies

7. UNIX for Advanced & Expert Users

filter last 24 hour data and put in new file

i have file server 1 (filesvr01acess.log) and disc server 1 (discsvr01acess.log) in unix box(say ip adress of the box 10.39.66.81) Similiarly i have file server 2 (filesvr01acess.log) and disc server 2(discsvr01acess.log) in another unix box(say ip adress of the box 10.39.66.82). Now my... (1 Reply)
Discussion started by: nripa1
1 Replies

8. Shell Programming and Scripting

Copying data from excel file

Hii friends, I am a newbie to unix/shell scripting and got stuck in implementing a functionality.Dear experts,kindly spare some time to bring me out of dark pit :confused:.. My requirement is somewhat wierd,let me explain what i have and what i need to do... 1) there are several excel... (1 Reply)
Discussion started by: 5ahen
1 Replies

9. Shell Programming and Scripting

how to copy data to to excel file

Hi, Can any one tell me how to copy data using shell script to a excel file from text file to other columns of excel file,leaving first column unaffected i.e it should not overwrite data in first column. Say my text file data is: 15-dec-2008 15-dec-2009 16-dec-2008 16-dec-2009 ... (7 Replies)
Discussion started by: tucs_123
7 Replies

10. Filesystems, Disks and Memory

Formatting excel file

Hi, I have a problem in formatting the excel file from my script. Assume that there is a text file generated using a 'C shell Unix script' with a format similar to the one seen below. Each column has a fixed length and every row is going to have a value against a column depending on the... (1 Reply)
Discussion started by: subra
1 Replies
Login or Register to Ask a Question