Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Script using awk to replace space by comma

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Script using awk to replace space by comma
# 1  
Old 12-14-2017
Script using awk to replace space by comma
I have the content of this file that i would like to replace the space by comma.
The file content values in this format

Code:
FName LName     Date & time
------------------------------------
Gilles    John    14/12/17  12:30:45

I want this format

Code:
Fname,LName,Date&time
-----------------------------
Gilles,John,14/12/17 12:30:45

I used the below, but i'm getting the comma between date and time

Code:
awk '{$1=$1;print}'  SIM_Sale.csv | sed 's! |!|!g' | sed 's!| !|!g' |tr ' ' ','

Moderator's Comments:
Mod Comment Please use CODE tags not HTML tags when displaying code segments and also use CODE tags when displaying sample input and output.
Last edited by Don Cragun; 12-14-2017 at 08:48 PM.. Reason: Add missing CODE tags; change HTML tags to COE tags.
# 2  
Old 12-14-2017
That is an extremely complicated pipeline that doesn't seem to be accomplishing a lot of what you want to do. The output you say you want also removes some spaces and hyphens that don't fit with your stated desire to change spaces to commas when used as field separators between the first three fields.

The following trims the 2nd line of the input to the length of the modified 1st line. (Assuming that there will typically be more than three lines of input in the files you process, you can't adjust the 2nd line of the file to match the longest output line that will be produced unless you store the entire output in memory before printing it, or reading the input file twice.)
Code:
awk '
NR == 2 {
	printf("%*.*s\n", w - 1, w - 1, $0)
	next
}
{	for(i = 1; i <= NF; i++) {
		printf("%s%s", $i, (i < 3) ? "," : (i == NF) ? ORS : OFS)
		w += length($i) + 1
	}
}' SIM_Sale.csv

With your sample input, this produces the output:
Code:
FName,LName,Date & time
-----------------------
Gilles,John,14/12/17 12:30:45

If you want to try this on a Solaris/SunOS system, change awk to /usr/xpg4/bin/awk or nawk.

P.S. I had a cut-and-paste error omitting the 1st line of the awk script (as noted by Scrutinizer in post #3). That missing line has now been added above.
Last edited by Don Cragun; 12-15-2017 at 12:50 PM.. Reason: Add missing 1st line of awk script.
# 3  
Old 12-15-2017
How about:
Code:
sed 's/  */,/; s/  */,/' file


or a different approach:
Code:
awk '!/---/{$3=$3 FS $4 FS $5; $4=$5=x; sub(/,+$/,x)}1' OFS=, file


---
I think Don Cragun left out the following:
Quote:
Originally Posted by Don Cragun
[..]
Code:
awk '
NR == 2 {
	printf("%*.*s\n", w - 1, w - 1, $0)
	next
}
{	for(i = 1; i <= NF; i++) {
		printf("%s%s", $i, (i < 3) ? "," : (i == NF) ? ORS : OFS)
		w += length($i) + 1
	}
}' SIM_Sale.csv

[..]
Last edited by Scrutinizer; 12-15-2017 at 04:54 AM..
This User Gave Thanks to Scrutinizer For This Post:
Don Cragun
# 4  
Old 12-15-2017
Code:
tr -s ' ' ','

should do the trick, surely.
# 5  
Old 12-15-2017
Thanks it has helped a lot with 3 fields. But if i have more fields like below

Code:
agent_msisdn    agent_username  customer_msisdn        datetime            customer_firstname      customer_othernames     customer_surname     customer_gender customer_address_line1  customer_address_line2  customer_address_line3  customer_address_city
233246250391          NULL         0246250391       2017-12-12 11:19:16     Anicet Williams                0                   OWUSU                  1                 0                     0                     Centrale HO              2

which parameter to change?

---------- Post updated at 08:31 AM ---------- Previous update was at 08:30 AM ----------

Thanks it has helped a lot with 3 fields. But if i have more fields like below

Code:
agent_msisdn    agent_username  customer_msisdn        datetime            customer_firstname      customer_othernames     customer_surname     customer_gender customer_address_line1  customer_address_line2  customer_address_line3  customer_address_city
233246250391          NULL         0246250391       2017-12-12 11:19:16     Anicet Williams                0                   OWUSU                  1                 0                     0                     Centrale HO              2

which parameter to change?
Moderator's Comments:
Mod Comment Please use CODE tags (not ICODE tags) when displaying full-line and multi-line sample input, output, and code segments.
Last edited by Don Cragun; 12-15-2017 at 12:52 PM.. Reason: Change ICODE tags to CODE tags.
# 6  
Old 12-15-2017
Code:
sed ' s/ *& /\&/g; s/\([0-9]\)  *\([0-9][0-9]\):/\1:!:\2:/g; s/[ \t][ \t]*/,/g; s/:!:/ /g; ' infile

# 7  
Old 12-15-2017
@rdrtx1 it's exactly what i needed. Thank you very much.
Code:
agent_msisdn,agent_username,customer_msisdn,datetime,customer_firstname,customer_othernames,customer_surname,customer_gender,customer_address_line1,customer_address_line2,customer_address_line3,customer_address_city
233246250391,NULL,0246250391,2017-12-12 11:19:16,Anicet,Williams,0,OWUSU,1,0,0,Centrale,Okoyo

Moderator's Comments:
Mod Comment Please use CODE tags (not ICODE tags) when displaying full-line and multi-line sample input, output, and code segments.
Last edited by Don Cragun; 12-15-2017 at 12:54 PM.. Reason: Change ICODE tags to CODE tags.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Replace spaces with underscores up to first comma but not after the comma

I have a comma delimited file of major codes and descriptions. I want to replace all occurrences of spaces with underscores up to the first comma (only in the first field), but not replace spaces following the comma. For instance I have the following snippet of the file: EK ED,Elementary and... (7 Replies)
Discussion started by: tdouty
7 Replies

2. Shell Programming and Scripting

awk script to replace nth character with comma

I have a requirement as below. In one of my column, I have data which may or may not be separted with coma always. Now I need to validate the length of these text within the coma (if available) and if the length is more than 30 characters, I need to insert a coma either at 30 characters if its... (3 Replies)
Discussion started by: aramacha
3 Replies

3. Shell Programming and Scripting

Replace comma and blank with comma and number

I, I have a file and i need to replace comma and blank space with comma and 0. cat file.txt a,5 b,1 c, d, e,4 I need the output as cat file.txt a,5 b,1 c,0 d,0 (4 Replies)
Discussion started by: jaituteja
4 Replies

4. Shell Programming and Scripting

Using of gsub function in AWK to replace space by underscore

I must design a UNIX script to monitor files whose size is over a threshold of 5 MB in a specific UNIX directory I meet a problem during the for loop in my script. Some file names contain spaces. ls -lrt | awk '$5>=5000000 && length($8)==5 {gsub(/ /,"_",$9); print};' -rw-r--r-- 1 was61 ... (2 Replies)
Discussion started by: Scofield38
2 Replies

5. Shell Programming and Scripting

Replace comma with a blank space using SED

Hello everyone, I want to replace all "," (commas) with a blank space My command thus far is: cat test.text | sed -e s/\`//g | awk '{print$1" "$2" "$3}' I'm sure you guys know this, but the SED command that I am using is to get rid of the "`" (tics). which gives me: name ... (5 Replies)
Discussion started by: jayT
5 Replies

6. Shell Programming and Scripting

Find and replace a column that has '' to NULL in a comma delimited using awk or sed

Hi this is my first time posting ever. I'm relatively new in using AWK/SED, I've been trying many a solution. I'm trying to replace the 59th column in a file where if I encounter '' then I would like to replace it with the word NULL. example 0 , '' , '' , 0 , 195.538462 change it to 0... (5 Replies)
Discussion started by: gumal901
5 Replies

7. Shell Programming and Scripting

Replace comma by space for specified field in record

Hi, i want to replace comma by space for specified field in record, i mean i want to replace the commas in the 4th field by space. and rest all is same throught the record. the record is 16458,99,001,"RIMOUSKI, QC",418,"N",7,EST,EDT,902 16458,99,002,"CHANDLER,... (5 Replies)
Discussion started by: raghavendra.cse
5 Replies

8. Shell Programming and Scripting

replace space with comma in perl

arr_Ent_NameId variable holds 'Prakash pawar' 'sag' '23' '50000' this value 'Prakash pawar' 'sag' '23' '50000' I want to replace space( ) with comma (,) There are 4 fields here. I don't want to replace first field with comma. output should be: 'Prakash,pawar','sag','23','50000' ... (2 Replies)
Discussion started by: pritish.sas
2 Replies

9. Shell Programming and Scripting

How to replace all entries of comma in text file by space or other character

Hi , How to replace all entries of comma in text file by space or other character. cat temp.txt A,B,C,D I want this file to be like A B C D Please help!!! (4 Replies)
Discussion started by: prashant43
4 Replies

10. Shell Programming and Scripting

Replace , (comma) with space

Hi, what is the better way to replace the , (comma) with a space char? Example:STRING=dir1,dir2,dir3 toSTRING=dir1 dir2 dir3 And.. how to find if in the string there is a comma? Thanks :) (6 Replies)
Discussion started by: mbarberis
6 Replies
Login or Register to Ask a Question