Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

How to remove unwanted commas from a .csv file?

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How to remove unwanted commas from a .csv file?
# 1  
Old 10-05-2018
How to remove unwanted commas from a .csv file?
how to remove unwanted commas from a .csv file

Input file format
Code:
"Server1","server-PRI-Windows","PRI-VC01","Microsoft Windows Server 2012, (64-bit)","Powered On","1,696.12","server-GEN-SFCHT2-VMS-R013,server-GEN-SFCHT2-VMS-R031,server-GEN-SFCHT2-VMS-R023"
"Server2","server-PRI-Windows","PRI-VC01","Microsoft Windows Server  2012, (64-bit)","Powered  On","2,696.12","server-GEN-SFCHT2-VMS-R013,server-GEN-SFCHT2-VMS-R031,server-GEN-SFCHT2-VMS-R023"
"Server3","server-PRI-Windows","PRI-VC01","Microsoft Windows Server  2012, (64-bit)","Powered  On","3,696.12","server-GEN-SFCHT2-VMS-R013,server-GEN-SFCHT2-VMS-R031,server-GEN-SFCHT2-VMS-R023"

Output required

Code:
"Server1","server-PRI-Windows","PRI-VC01","Microsoft Windows Server  2012(64-bit)","Powered  On","1696.12","server-GEN-SFCHT2-VMS-R013,server-GEN-SFCHT2-VMS-R031,server-GEN-SFCHT2-VMS-R023"
"Server2","server-PRI-Windows","PRI-VC01","Microsoft Windows Server  2012(64-bit)","Powered  On","2696.12","server-GEN-SFCHT2-VMS-R013,server-GEN-SFCHT2-VMS-R031,server-GEN-SFCHT2-VMS-R023"
"Server3","server-PRI-Windows","PRI-VC01","Microsoft Windows Server  2012(64-bit)","Powered  On","3696.12","server-GEN-SFCHT2-VMS-R013,server-GEN-SFCHT2-VMS-R031,server-GEN-SFCHT2-VMS-R023"

I have removed the comma from column 6 (Size of the Capacity)
I have removed the comma from column 4 (Os field)
# 2  
Old 10-05-2018
how about:
Code:
awk -F'"' '{$12=substr($12,index($12,",")+1);sub(",","",$8)}1' OFS='"' myFile

# 3  
Old 10-05-2018
Whenever you start a thread in the Shell Programming and Scripting forum, please tell us what operating system and shell you're using!

And give us a clear explanation of what it is that you want to do. The fields in your CSV file seem to be separated by three character <double-quote><comma><double-quote> sequences.

You say you want to remove unwanted <comma>s; but you don't explain why the <comma>s in field six are unwanted, why both a <comma> and a <space> are unwanted in field four, and you don't explain why the <comma>s in other fields are not unwanted. You didn't say anything in your description about removing unwanted <space>s. (Note that the script vgersh99 suggested doesn't produce the output you say you want because it didn't remove the <space> after the <comma> you said you wanted removed in field four.)

With well over 100 posts in this forum, you should know by now that we expect you to show us what you have tried to solve this problem on your own. We want to help you learn how to do things like this on your own; not act as your unpaid programming staff.
This User Gave Thanks to Don Cragun For This Post:
vgersh99
# 4  
Old 10-05-2018
I agree with Don - the proposed solution is somewhat limited in scope as it only deals with the provided sample data.
I makes (or rather doesn't consider more generic cases, e.g. embedded double quote and multi-line csv fields).
Removing a sampled (but not stated explicitly) trailing blank(s) is is easy to fix and could be left as an exercise for the OP.

In order consider most (all?) cases of the csv file parcing, one would need a heavier "fork lift" for sure....
# 5  
Old 10-05-2018
Sorry i was using cygwin in windows. I was using this dont no this remove all comma from the csv file. But i cant awk but this resolve my issue

Code:
  sed '/[[:digit:]]/ {s/,//g}'

# 6  
Old 10-05-2018
Quote:
Originally Posted by ranjancom2000
Sorry i was using cygwin in windows. I was using this dont no this remove all comma from the csv file. But i cant awk but this resolve my issue

Code:
  sed '/[[:digit:]]/ {s/,//g}'

I don't quite understand the statement above....
Also, cygwin does have awk as part of the distribution....

You also haven't addressed any points that Don'd raised...
This User Gave Thanks to vgersh99 For This Post:
Don Cragun
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Removing commas from CSV file

Hi I'm creating a sh script to generate a csv file. The CSV contains the values from a sql table. The content looks this: a,b,c,c2,c3,,,,,,,,,,,d,e I have some code that can separate the fields using the comma as delimiter, but some values actually contain commas, such as... (2 Replies)
Discussion started by: preema
2 Replies

2. Shell Programming and Scripting

Shell script that should remove unnecessary commas between double quotes in CSV file

i have data as below 123,"paul phiri",paul@yahoo.com,"po.box 23, BT","Eco Bank,Blantyre,Malawi" i need an output to be 123,"paul phiri",paul@yahoo.com,"po.box 23 BT","Eco Bank Blantyre Malawi" (5 Replies)
Discussion started by: mathias23
5 Replies

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

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

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

6. Shell Programming and Scripting

Remove leading commas in the file

Hi , I have a file with below records 965382,10310858,1,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, 767010,10217614,3,10217616,10217622,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,... (4 Replies)
Discussion started by: vputtas@gmail.c
4 Replies

7. Shell Programming and Scripting

CSV with commas in field values, remove duplicates, cut columns

Hi Description of input file I have: ------------------------- 1) CSV with double quotes for string fields. 2) Some string fields have Comma as part of field value. 3) Have Duplicate lines 4) Have 200 columns/fields 5) File size is more than 10GB Description of output file I need:... (4 Replies)
Discussion started by: krishnix
4 Replies

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

9. Shell Programming and Scripting

Remove duplicate commas after exporting excel file to csv

Hello everyone I'm new here and this is my first post so first of all I want to say that this is a great forum and I have managed to found most of my answers in these forums : ) So with that I ask you my first question: I have an excel file which I saved as a csv. However the excel file... (3 Replies)
Discussion started by: Spunkerspawn
3 Replies

10. Shell Programming and Scripting

replacing commas with tilde in csv file.

hello all, i have a comma delimited file. i want to replace the commas in the file with the tilde symbol using sed. how can i do this? thanks. (4 Replies)
Discussion started by: femig
4 Replies
Login or Register to Ask a Question