Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

shell script to remove extra commas from CSV outp file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting shell script to remove extra commas from CSV outp file
# 1  
Old 04-29-2011
shell script to remove extra commas from CSV outp file
Code:
Name,,,,,,,,,,,,,,,,,,,,Domain,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,Contact,Phone,Email,,,,,,,,,,,,,,,,,,,,,,,,,,,,,Location
-----------------------,------------------------------------------------,-------,-----,---------------------------------,------------------------------------
----
clients,,,,,,,,,,,,,,,,,/,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,5fdf97049abdd582976d954bba8ff256c4be
edd8
GEN-I,,,,,,,,,,,,,,,,,,,/,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,Service,Provider
MC_RETIRED,,,,,,,,,,,,,,/,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,acb53a6470ba5fdd6efc93a0d7228e9e014a
199e
REPLICATE,,,,,,,,,,,,,,,/,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,5cf5275db7ceb0a7c8e29c7d66fd548f61ce
d14d
CUSTOMER_01,,,,,,,,,,,,,/GEN-I,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,<<Cust01,PARTY,NUMBER>>
CUSTOMER_02,,,,,,,,,,,,,/GEN-I,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,<<Cust02,PARTY,NUMBER>>
RCB_INSTANCE_01,,,,,,,,,/GEN-I/CUSTOMER_01,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,"Product,Instance,01,Description",<<Cust01_Inst01,PRODUCT,ID>>
RCB_INSTANCE_02,,,,,,,,,/GEN-I/CUSTOMER_01,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,"Product,Instance,02,Description",<<Cust01_Inst02,PRODUCT,ID>>
RCB_INSTANCE_01,,,,,,,,,/GEN-I/CUSTOMER_02,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,"Product,Instance,01,Description",<<Cust02_Inst01,PRODUCT,ID>>
wn1naeavu101.sdp.net.nz,/REPLICATE,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,1eb4ad6f161e2be6644c13270adaa8dc7e7a
a6a8
clients,,,,,,,,,,,,,,,,,/REPLICATE/wn1naeavu101.sdp.net.nz,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,d93a694ed05147f8f9895ffa57fb91a0b15e
d25d
GEN-I,,,,,,,,,,,,,,,,,,,/REPLICATE/wn1naeavu101.sdp.net.nz,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,a4af9e4cc65ba65550b3365759280cf1197c
b7da
MC_RETIRED,,,,,,,,,,,,,,/REPLICATE/wn1naeavu101.sdp.net.nz,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,892e8133bb46f16caeb4667811457e5a9126
838c
REPLICATE,,,,,,,,,,,,,,,/REPLICATE/wn1naeavu101.sdp.net.nz,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,b983f7c9a69545b4f0a63a30f28447279a45
24c2
WLGtest,,,,,,,,,,,,,,,,,/REPLICATE/wn1naeavu101.sdp.net.nz/GEN-I,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,4d6c41eb132caae70e8a9f990201e5c83c11
04b5
RCB_INSTANCE_01,,,,,,,,,/REPLICATE/wn1naeavu101.sdp.net.nz/GEN-I/CUSTOMER_02,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,0aec9c024ae8c9b60a81a371980e58be0eb2
f27e
RCB_INSTANCE_02,,,,,,,,,/REPLICATE/wn1naeavu101.sdp.net.nz/GEN-I/CUSTOMER_03,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,95e059107f165bb603aabae42b5

Last edited by Yogesh Sawant; 05-01-2011 at 07:00 AM.. Reason: added code tags
# 2  
Old 04-29-2011
Code:
sed 's/,,*/,/g'

but the commas are column separators, even if the columns are mainly null, so it is better to select out the columns you want with:
Code:
cut -d, -f1,3,7-11

which works as long as there are no quoted commas in fields: "... , ...". BTW, in proper CSV, literal double quotes are doubled, e.g.,
Code:
,,,"He said, ""Drop the gun, stupid!"" in a shout.",,,,

Old Access did not honor double quotes, being "Johhny bought lately", but Excel was the mother of CSV. You can always select out the good columns using s CSV friendly JDBC/unixODBC driver and jisql(xigole)/isql in SQL.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Is there a way to handle commas inside the data when generating a csv file from shell script?

I am extracting data via sql query and some of the data has commas. Output File must be csv and I cannot update the data in the db (as it is used by other application). Example table FavoriteThings Person VARCHAR2(25), Favorite VARCHAR2(100) Sample Data Greta rain drop on... (12 Replies)
Discussion started by: patk625
12 Replies

2. Shell Programming and Scripting

How to remove unwanted commas from a .csv file?

how to remove unwanted commas from a .csv file Input file format "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"... (5 Replies)
Discussion started by: ranjancom2000
5 Replies

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

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

Remove line breaks in csv file using shell script

Hi All, I've a csv file in which the record is getting break into 1 line or more than one line. I want to combine those splits into one line and remove the unwanted character existing in the record i.e. double quote symbol ("). The line gets break only when the record contains double... (4 Replies)
Discussion started by: rajak.net
4 Replies

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

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

exporting number into .csv file in text form (no other extra charc) from shell script

I have written a k shell program which is executing a sql and exporting data in numeric form like 0412323444 into .csv file. the problem i am facing is that , the data is coming in excel formatted in scientific form like 4.1+E08,while my requirement is to store data as such 0412323444 in excel ( no... (5 Replies)
Discussion started by: Deepak_Rastogi
5 Replies
Login or Register to Ask a Question