Login or Register to Ask a Question and Join Our Community


Linux

Add empty columns at the end of a CSV file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Operating Systems Linux Add empty columns at the end of a CSV file
# 1  
Old 10-09-2013
Add empty columns at the end of a CSV file
I have a .CSV file (lets say named as file.csv) with numeric and string values. The string might contain commas hence they are enclosed in double quotes as in the below format.

Code:
column1,column2,column3,column4,column5,column6,column7
12,455,"string, with, quotes, and with, commas, in between",4432,6787,890,88
4432,6787,"another, string, with, quotes, and, with, multiple, commaz, in between",890,88,12,455
11,22,"simple, string",77,777,333,22

When I am trying to add empty columns at the end of the file, using the below code

Code:
awk -F, '{NF=13}1' OFS="," file.csv > temp_file.csv

the output is not as per my requirement. The code is also counting the commas in the text qualifier fields too whihc are enclosed in double quotes. The output of the file
Code:
cat temp_file.csv

using the above command is as below:

Code:
column1,column2,column3,column4,column5,column6,column7,,,,,,
12,455,"string, with, quotes, and with, commas, in between",4432,6787,890,88,
4432,6787,"another, string, with, quotes, and, with, multiple, commaz, in between",890,88
11,22,"simple, string",77,777,333,22,,,,,

Where as I need the total number of fields in the file to be 13. Any input on this issue using either `awk` or `sed` is greatly appreciated.
# 2  
Old 10-09-2013
Something like this?
Code:
awk -F, '
        {
                for ( i = 1; i <= NF; i++ )
                {
                        if ( $i ~ /"/ && f == 1)
                                f = 0
                        else
                        {
                                if ( $i ~ /"/ && f == 0 )
                                        f = 1
                        }
                        if ( f == 0 )
                                ++c
                }
                printf "%s", $0
                if ( c != 13 )
                {
                        while ( ++j < (13-c) )
                                printf ","
                }
                c = f = j = 0
                printf RS
        }
' file.csv

This User Gave Thanks to Yoda For This Post:
RavinderSingh13
# 3  
Old 10-09-2013
Thanks Yoda, but variables are not defined.
Could you please explain this a bit please.


Thanks,
R. Singh
# 4  
Old 10-09-2013
Not as elegant as desired, but may be worth a shot:
Code:
 awk -F'"' '{gsub (/,/,"\001",$2)}1' OFS='"' file | awk -F, '{NF=13}1' OFS=","|awk '{gsub ("\001",",")}1'
column1,column2,column3,column4,column5,column6,column7,,,,,,
12,455,"string, with, quotes, and with, commas, in between",4432,6787,890,88,,,,,,
4432,6787,"another, string, with, quotes, and, with, multiple, commaz, in between",890,88,12,455,,,,,,
11,22,"simple, string",77,777,333,22,,,,,,

and, if there's more fields with double quotes, you need to run a loop on all even fields to gsub commas...
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

To add multiple columns in csv file

Hi Guys, Can you help to add static values at the end of the csv file with headers input_file id,name 1,neo 2,pull Expected id,name,status,entry,g_id 1,neo,done,2019-11-01T07:14:23,pass 2,pull,done,2019-11-01T07:14:23,pass My try but not able replacing properly and unable... (1 Reply)
Discussion started by: Master_Mind
1 Replies

2. Shell Programming and Scripting

Add 8 columns at the end of .csv file using awk

Hello all, I have a .csv file of 16 columns consists of bunch of numbers. 6.45E+01 1.17E+01 8.10E+04 8.21E+01 8.50E+00 1.20E+01 1.02E+01 1.88E+01 1.86E+04 3.53E+03 1.09E+07 3.82E+04 2.09E+03 3.57E+03 2.98E+03 3.93E+03 6.34E+01 3.23E+01 9.24E+04 ... (5 Replies)
Discussion started by: Zam_1234
5 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

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

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

6. Shell Programming and Scripting

AWK : Add columns in the end of csv file

Hi everybody, I need some help please I have a csv file named masterFile1.csv header1,header2,header3 value1,value2,value3 value4,value5,value6 I am trying to add new columns in the end of the csv to have a new csv file named masterFile2.csv like this :... (3 Replies)
Discussion started by: villebonnais
3 Replies

7. Shell Programming and Scripting

Add columns in csv file

Hi everybody, I am new here and I need a reel help please A have a csv file and I want to add new in the end of the file Devisce Model,VMGuest Name,Host OS Name, Memory Size Value1,Value2,Value3,Value4 Value5,Value6,Value7,Value8 Value9,Value10,Value11,Value12 And I want to add to new... (3 Replies)
Discussion started by: villebonnais
3 Replies

8. Shell Programming and Scripting

Add Empty columns at the end of csv file

Hi, Can you please tell me how to add empty columns at the end csv file? Currently there are 6 columns in the csv file. I want to add 35 empty columns at the end of this csv file. Thanks, Tushar (17 Replies)
Discussion started by: Tushar Bendale
17 Replies

9. UNIX for Dummies Questions & Answers

Merging Non-Empty Columns within a CSV

I am trying to place all my data in a single row (order doesn't matter). Note I am a Unix novice, so please go easy on me. Here is an example Raw data: row# (1) 45 64 23 (2) 32 1 6 56 (3) 32 45 Needs to be like this: row# (1) 45 (2) 32 (3) 32 ... (2 Replies)
Discussion started by: mmann1123
2 Replies

10. Filesystems, Disks and Memory

manipulate csv file to add columns

Hi, I have a csv file with a key composed by 3 columns and some other numeric fields and I need to obtain the partial amounts by some part of the key. This may be some difficult to understand, so better see an example, where my input file is: name,surname,department,y2004,y2005,y2006... (6 Replies)
Discussion started by: oscarmon
6 Replies
Login or Register to Ask a Question