Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Need to Insert three extra columns in csv file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Need to Insert three extra columns in csv file
# 1  
Old 09-11-2015
Need to Insert three extra columns in csv file
Hello Experts,

I got a requirement i have a input file which am getting from different source,Now i want to add extra 3 columns to this file like BASE,ACTUAL and DATE.

Input File Looks like

Code:
QUAL	CHGE	 TYP	LAW COM1	COM2
A        1      X     SED   HO    ASE
B        3      Z     CDE   SE     TOE

Ouptut file i need like this
Code:
BASE ACTUAL    DATE           QUAL CHGE   TYP LAW  COM1 COM2
PD    DV     09/11/2015         A       1       X  SED   HO    ASE
PD    DV     09/11/2015         B       3       Z  CDE   SE     TOE

Please help me out here,Am new to shell scripting.

Regards,
Ahmed.
Last edited by Scrutinizer; 09-11-2015 at 09:21 PM.. Reason: code tags
# 2  
Old 09-11-2015
Hello ahmed.vaghar,

Welcome to forum, please use code tags in your inputs/codes/samples used in your posts as per forum rules, following may help you in same.
Input_file:
Code:
cat change_fields
QUAL    CHGE     TYP    LAW COM1        COM2
A        1      X     SED   HO    ASE
B        3      Z     CDE   SE     TOE

Then following code may help.
Code:
awk 'NR==1{$0="BASE ACTUAL    DATE" "\t\t" $0} NR>1{$0="PD    DV     09/11/2015" "\t\t" $0} 1' change_fields

Output will be as follows.
Code:
BASE ACTUAL    DATE             QUAL    CHGE     TYP    LAW COM1        COM2
PD    DV     09/11/2015         A        1      X     SED   HO    ASE
PD    DV     09/11/2015         B        3      Z     CDE   SE     TOE

Thanks,
R. Singh
# 3  
Old 09-12-2015
Thank you very much Ravinder for prompt response.

Yes it is working.But my requirement is to write the output in CSV file.I can able to write that in CSV file using this command ">" Is there a way i can write each field in each column of CSV file

Attached the output file which am getting now for your reference.

Thanks Once again.

Regards,
Ahmed.
Sample.xls (23.0 KB) 
# 4  
Old 09-12-2015
Your welcome Ahmad. You can use the following command to get output in a .csv file first.
Code:
 awk 'NR==1{$0="BASE ACTUAL    DATE" "\t\t" $0} NR>1{$0="PD    DV     09/11/2015" "\t\t" $0} 1' change_fields > sample.csv

Now open .csv file and perform following steps.
Code:
 Go to Menu's DATA option ---> Select TEXT to Columns option---> Select Delimited option--->Then select  TAB + space --->Give a Next in wizard--->Click DONE

Here I am considering that your fields doesn't have space in themselves
else it will segregate them too.

Thanks,
R. Singh
# 5  
Old 09-13-2015
Ravinder,

Thanks again.

Is there a way we can do that using script.
# 6  
Old 09-13-2015
I can't see three columns added to your sample file, I see three words separated by spaces added to your first column.

A .csv file is a text file with columns separated by commas, semicolons or similar, readable immediately by data bases or spread sheets. To achieve this, try
Code:
awk '{T=(NR==1)?"BASE;ACTUAL;DATE":"PD;DV;09/11/2015"; $1=$1; printf "%s;%s\n", T, $0} ' OFS=";" file
BASE;ACTUAL;DATE;QUAL;CHGE;TYP;LAW;COM1;COM2
PD;DV;09/11/2015;A;1;X;SED;HO;ASE
PD;DV;09/11/2015;B;3;Z;CDE;SE;TOE

and try to open it with e.g. EXCEL.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. 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

2. 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

3. 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

4. UNIX for Dummies Questions & Answers

To Add extra commas to a CSV file.

Hi All, I got this requirement to process a complex CSV file. Eg File. Line 1: Name:,XYz Line 2: Age:,15 Line 3: Grade:,7 Line 4: Line 5: English, Maths, Science,Spanish Line 6:10,11,13,14 As you can see the maximum column is 4 . The file i need to make is Line 1: Name:,XYz,,... (12 Replies)
Discussion started by: chillblue
12 Replies

5. UNIX for Dummies Questions & Answers

To Add extra commas to a CSV file using 2 files...

Hi , Based on my previous requirement the code works fine for comma as delimiter. Now my Req is widened up a bit .. There will be two set of files .. one with comma as delimiter and other with semi-colon ; as delimiter. Second Sample file. With Double Quotes (Semi-Colon... (1 Reply)
Discussion started by: chillblue
1 Replies

6. Shell Programming and Scripting

Adding Extra Commas to a CSV file

Trying in this forum. Not sure if it is permitted.... but in need of help. Please find the requirements in the below link. https://www.unix.com/unix-dummies-questions-answers/191503-add-extra-commas-csv-file-2.html#post302665179 Thanks in Advance. (1 Reply)
Discussion started by: chillblue
1 Replies

7. Shell Programming and Scripting

Ascii Mode appending extra records to csv file

I am relatively new to this forum and Unix scripting. ksh script: part 1 :will call a PL\SQL program will create 3 CSV file at the unix directory. part 2 : will sftp the files to the EFT server. Once the EFT server receives these file , it will transfer them to a shared windows folders. ... (3 Replies)
Discussion started by: developerpa
3 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

shell script to remove extra commas from CSV outp file

Name,,,,,,,,,,,,,,,,,,,,Domain,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,Contact,Phone,Email,,,,,,,,,,,,,,,,,,,,,,,,,,,,,Location -----------------------,------------------------------------------------,-------,-----,---------------------------------,------------------------------------ ----... (1 Reply)
Discussion started by: sreenath1037
1 Replies

10. Shell Programming and Scripting

how to insert a extra line in a text file

how to insert a extra line in a text file using a sh command iam trying to think of a way to add a extra line but without deleting the whole text do anyone have any ideas (2 Replies)
Discussion started by: bhaviknp
2 Replies
Login or Register to Ask a Question