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
Assuming the columns are comma separated:
awk -F, '{NF=41}1' OFS="," file > newfile
something like this:
awk -F"," 'BEGIN{a="";for(i=1;i<=35;i++){a=" ,"a}} { print $0","a}' input_file
Thanks for your reply. The command is adding empty columns at the end, after adding a New lline character. I want this in single row itself.
Could you please help me in this?
I don't see any problem with the command, this is what I get:
$ cat file
1,2,3,4,5,6
1,2,3,4,5,6
1,2,3,4,5,6
1,2,3,4,5,6
$ awk -F, '{NF=41}1' OFS="," file
1,2,3,4,5,6,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
1,2,3,4,5,6,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
1,2,3,4,5,6,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
1,2,3,4,5,6,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
Franklin,
Strange but i did not get the expected output with the command you suggested:
TES>cat rem
1,2,3,4,5,6
1,2,3,4,5,6
1,2,3,4,5,6
1,2,3,4,5,6
TEST>awk -F, '{NF=41}1' OFS="," rem
1,2,3,4,5,6
1,2,3,4,5,6
1,2,3,4,5,6
1,2,3,4,5,6
My OS is
HP-UX avalon B.11.11 U 9000/800 3547052374 unlimited-user license
The standard does not require recomputation of $0 when NF is assigned. However, it does if you assign to a field beyond NF (which also recomputes NF). Instead, try:
awk -F, '{$41=""}1' OFS="," file
I believe that should work with posix-compliant awks.
Regards,
Alister
proofed from within Solaris 10:
-> awk -F, '{$41=""}1' OFS="," file.csv
+ awk -F, {$41=""}1 OFS=, file.csv
awk: syntax error near line 1
awk: bailing out near line 1
-> nawk -F, '{$41=""}1' OFS="," file.csv
+ nawk -F, {$41=""}1 OFS=, file.csv
1,2,3,4,5,6,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
1,2,3,4,5,6,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
1,2,3,4,5,6,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
1,2,3,4,5,6,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
Hi Alister,
you pointed right. How ever i did not get ,what do you mean by
"The standard does not require recomputation of $0 when NF is assigned. However, it does if you assign to a field beyond NF (which also recomputes NF)."
It means that if you assign to a field beyond $NF, the necessary fields will be created, NF will be recomputed, and $0 will be rebuilt.
For example (with FS==OFS==","):
$0 == "a,b,c,d,e,f"
$NF == "f"
NF == 6
$10="g"
NF == 10 # NF has been recomputed
$NF == "g"
$0 == "a,b,c,d,e,f,,,,g" # $0 has been recomputed to include null fields between the old value of $NF and the new.
Note, that when $0 is rebuilt, the fields are separated by the value of the output field separator, OFS, so the new $0 may not be the same as the original $0 with the new fields appended. For example, if the default setting of FS is used, consecutive instances of whitespace delimit a field, but the OFS is a single space. If there are many spaces between two fields, when $0 is recomputed, there will be only one.
With default values of FS and OFS:
$0 == "a b c d e f"
$NF == "f"
NF == 6
$10="g"
NF == 10 # NF has been recomputed
$NF == "g"
$0 == "a b c d e f g" # $0 has been recomputed to include null fields between the old value of $NF and the new.
# Note that the spaces between fields have been compressed to a single space.
# Also, this is not reversible; once done, if you did not save the original value of $0,
# it cannot be restored.
$0 is similary recomputed when you assign a value to a field, even if it already exists. If NF=6, $1=$1 will trigger this recomputation.
$0 == "a b c d e f"
$1=$1
$0 == "a b c d e f" # $0 has been recomputed, the spaces between fields have been replaced by the value of OFS,
# but NF remains unchanged as we did not extend the number of fields in any way.
However, simply assinging a value to NF (instead of $NF) does not necessarily trigger the creation of fields and recomputation of $0.
Hope that helps,
Alister
1 Like
Thanks for your valuable inputs. I guess the csv file where I want to add empty columns at the end, contails a new line character at the end.
See below the input file format:
-> more test.csv
Object Type,Object Subtype,Object Name,CEMLI Type,Owner,Location,Created By, Creation Date, Updated By, Update Date, LastRecorded Usage
DB_OBJECT,ALERT,@GEPSAlphaPOSOEAInotification,CONFIGURATION,FND,ALR_ALERTS,CONCURRENT_MGR,10-Feb-10,CONCURRENT_MGR,test,
-> awk -F, '{NF=41}1' OFS="," test.csv > result.csv
-> more result.csv
Object Type,Object Subtype,Object Name,CEMLI Type,Owner,Location,Created By, Creation Date, Updated By, Update Date, LastRecorded Usage
, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,
DB_OBJECT,ALERT,@GEPSAlphaPOSOEAInotification,CONFIGURATION,FND,ALR_ALERTS,CONCURRENT_MGR,10-Feb-10,CONCURRENT_MGR,test,
, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,
Is it possible to add those empty columns in between the last field seprator and new line characher?
Huh? Do you want to insert the empty fields into the line, and not at the end of it? Or are you dealing with multiline records? I'm not certain what you mean. Why don' t you post a sample record from your csv file, as well as how that record should appear after the desired transformation? That would be most helpful.
If that awk snippet isn't giving you the desired result, perhaps the following will:
awk -F, '{$41=""}1' OFS="," file
Regards,
Alister
Can you post the output of:
head -3 test.csv | od -An -t x1
Please use code tags.
The result of command
-> head -3 test.csv | od -An -t x1
4f 62 6a 65 63 74 20 54 79 70 65 2c 4f 62 6a 65
63 74 20 53 75 62 74 79 70 65 2c 4f 62 6a 65 63
74 20 4e 61 6d 65 2c 43 45 4d 4c 49 20 54 79 70
65 2c 4f 77 6e 65 72 2c 4c 6f 63 61 74 69 6f 6e
2c 43 72 65 61 74 65 64 20 42 79 2c 20 43 72 65
61 74 69 6f 6e 20 44 61 74 65 2c 20 55 70 64 61
74 65 64 20 42 79 2c 20 55 70 64 61 74 65 20 44
61 74 65 2c 20 4c 61 73 74 52 65 63 6f 72 64 65
64 20 55 73 61 67 65 0d 0a 44 42 5f 4f 42 4a 45
43 54 2c 41 4c 45 52 54 2c 40 47 45 50 53 20 41
6c 70 68 61 50 4f 53 4f 20 45 41 49 20 6e 6f 74
69 66 69 63 61 74 69 6f 6e 2c 43 4f 4e 46 49 47
55 52 41 54 49 4f 4e 2c 46 4e 44 2c 41 4c 52 5f
41 4c 45 52 54 53 2c 43 4f 4e 43 55 52 52 45 4e
54 5f 4d 47 52 2c 31 30 2d 46 65 62 2d 31 30 2c
43 4f 4e 43 55 52 52 45 4e 54 5f 4d 47 52 2c 74
65 73 74 2c 0d 0a 44 42 5f 4f 42 4a 45 43 54 2c
41 4c 45 52 54 2c 40 47 45 50 53 20 41 6c 70 68
61 50 4f 53 4f 20 53 75 70 70 6c 20 53 69 74 65
20 73 65 74 75 70 20 61 6c 65 72 74 20 6e 6f 74
69 66 79 2c 43 4f 4e 46 49 47 55 52 41 54 49 4f
4e 2c 46 4e 44 2c 41 4c 52 5f 41 4c 45 52 54 53
2c 43 4f 4e 43 55 52 52 45 4e 54 5f 4d 47 52 2c
32 39 2d 4a 61 6e 2d 31 30 2c 43 4f 4e 43 55 52
52 45 4e 54 5f 4d 47 52 2c 20 2c 0d 0a
The file is in dos/win format, reformat the file first with:
tr -d '\r' < dosfile > unixfile
1 Like
Hi Alister,
I tried to use the command given by you. But it is still not giving me the required output.
the output I got is :
Object Type,Object Subtype,Object Name,CEMLI Type,Owner,Location,Created By, Creation Date, Updated By, Update Date, LastRecorded Usage
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
DB_OBJECT,ALERT,@GEPSAlphaPOSOEAInotification,CONFIGURATION,FND,ALR_ALERTS,CONCURRENT_MGR,10-Feb-10,CONCURRENT_MGR,test,
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
My requirement is to add 30 empty columns at the end of my csv file. The sample record of my original csv file is:
Object Type,Object Subtype,Object Name,CEMLI Type,Owner,Location,Created By, Creation Date, Updated By, Update Date, LastRecorded Usage
DB_OBJECT,ALERT,@GEPSAlphaPOSOEAInotification,CONFIGURATION,FND,ALR_ALERTS,CONCURRENT_MGR,10-Feb-10,CONCURRENT_MGR,test,
And i want it as:
Object Type,Object Subtype,Object Name,CEMLI Type,Owner,Location,Created By, Creation Date, Updated By, Update Date, LastRecorded Usage, , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,
DB_OBJECT,ALERT,@GEPSAlphaPOSOEAInotification,CONFIGURATION,FND,ALR_ALERTS,CONCURRENT_MGR,10-Feb-10,CONCURRENT_MGR,test, , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,
The awk commands you've been using seem to be working correctly, but the results "look" wrong to you because of the way the carriage return before the list of null fields (the string of commas) is being rendered.
If you want to get rid of it, filter the file with tr as franklin52 suggested.
1 Like
It worked after filtering the file with tr command and then using awk command. Thank you all for all your help.