Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: How to delete a columns of a CSV file which has cell values with a string enclosed in " , "?
Top Forums Shell Programming and Scripting How to delete a columns of a CSV file which has cell values with a string enclosed in " , "? Post 302902004 by Litu19 on Saturday 17th of May 2014 07:57:26 AM
Old 05-17-2014
How to delete a columns of a CSV file which has cell values with a string enclosed in " , "?
Hi

How can I delete a columns from a CSV file which has comma separated value with a string enclosed in double quotes or square bracket and a comma in between?

I have a csv file with below format.

Code:
 
Template,Target Server,Target Component,Rule Group,Rule,Rule Reference Number,Rule Definition,Mismatched Conditions,Result,Documented Exception,Compliant,Exception Name,Exception Reference,Expiration Date
 
 
RHEL_Pwd,server1,RHEL_Pwd (server1),/User w3server,Password expires,,"""Entry:RHEL_Pwd//w3server-Password expires"".""value (All OS)"" equals ""never""",,Pass,,Y,,,
RHEL_Pwd,server1,RHEL_Pwd (server1),/User w3server,Password inactive,,"""Entry:RHEL_Pwd//w3server-Password inactive"".""value (All OS)"" equals ""never""",,Pass,,Y,,,
RHEL_Pwd,server1,RHEL_Pwd (server1),/User w3server,Max Days Between Changes,,"""Entry:RHEL_Pwd//w3serverMax pwd days"".""Value1 (All OS)"" equals 999989 OR
""Entry:RHEL_Pwd//w3server-Max pwd days"".""value (All OS)"" = ""999989""","""Entry:RHEL_Pwd//w3serverMax pwd days"".""Value1 (All OS)"" = 999,989 [[ ""Entry:RHEL_Pwd//w3serverMax pwd days"".""Value1 (All OS)"" [99,998] = 999,989 ]] OR
""Entry:RHEL_Pwd//w3serverMax pwd days"".""value (All OS)"" = ""999989"" [[ ""Entry:RHEL_Pwd//w3serverMax pwd days"".""value (All OS)"" [""99998""] = ""999989"" ]]
",Fail,,N,,,

I am looking for something like this

Code:
 
Template,Target Server,Target Component,Rule Group,Rule,Rule Reference Number,Result,Documented Exception,Compliant,Exception Name,Exception Reference,Expiration Date
 
RHEL_Pwd,server1,RHEL_Pwd (server1),/User w3server,Password expires,,Pass,,Y,,,
RHEL_Pwd,server1,RHEL_Pwd (server1),/User w3server,Password inactive,,Pass,,Y,,,
RHEL_Pwd,server1,RHEL_Pwd (server1),/User w3server,Max Days Between Changes,,Fail,,N,,,

I have tried to remove unwanted data from columns position (7,8) but no luck. How i would remove column (rule definition and mismatched condition) from the new csv file ?
Any suggestion would be greatly appreciated.

---------- Post updated at 05:27 PM ---------- Previous update was at 04:52 PM ----------

Just to add a point here... I think there is some carriage return or blank or white space present in "rule definition and mismatched conditions".

I am very new to this scripting and not have enough knowledge how to handle carriage return or white or blank spaces.

few things I tried as of now

Code:
cut -d"," -f1,2,3,4,5,6,9,10,11,12,13 file.csv > file1.csv

Code:
awk -F, 'NR==1{NF-=3}NF>1{sub(/",.*/,"\"")}1' OFS=, file.csv >file.csv

Last edited by Scrutinizer; 05-17-2014 at 04:13 PM.. Reason: Additional CODE tags
 

9 More Discussions You Might Find Interesting

1. UNIX for Advanced & Expert Users

How to add two values in the same cell of CSV file

I need help to create a csv file with Unix command. In csv file, i need to put two values in the same cell. Rite now, whts happening is, if i put 2 values in the same cell, its comming as " asd, zxc" but i want it in different line but in same cell. asd zxc Please reply me ASAP. (1 Reply)
Discussion started by: Prashant Jain
1 Replies

2. Shell Programming and Scripting

awk command to replace ";" with "|" and ""|" at diferent places in line of file

Hi, I have line in input file as below: 3G_CENTRAL;INDONESIA_(M)_TELKOMSEL;SPECIAL_WORLD_GRP_7_FA_2_TELKOMSEL My expected output for line in the file must be : "1-Radon1-cMOC_deg"|"LDIndex"|"3G_CENTRAL|INDONESIA_(M)_TELKOMSEL"|LAST|"SPECIAL_WORLD_GRP_7_FA_2_TELKOMSEL" Can someone... (7 Replies)
Discussion started by: shis100
7 Replies

3. UNIX for Dummies Questions & Answers

replace "," with "." only in specific columns of a file?

Hi all, I have this text file containing 9 columns separated by space. The 8th columns contains the numbers. C1 C2 C3 C4 C5 C6 C7 C8 C9 er rt yt gh iu nk il 0.07 xs yt lr ty bg iu zk nh 0,0005 lt ...etc. I want to replace the comma with full stop only in 8th coloumn. the output... (8 Replies)
Discussion started by: Unilearn
8 Replies

4. Shell Programming and Scripting

how to use "cut" or "awk" or "sed" to remove a string

logs: "/home/abc/public_html/index.php" "/home/abc/public_html/index.php" "/home/xyz/public_html/index.php" "/home/xyz/public_html/index.php" "/home/xyz/public_html/index.php" how to use "cut" or "awk" or "sed" to get the following result: abc abc xyz xyz xyz (8 Replies)
Discussion started by: timmywong
8 Replies

5. Shell Programming and Scripting

How to delete a column/columns of a CSV file which has cell values with a string enclosed in " , "?

How can I delete a column from a CSV file which has comma separated value with a string enclosed in double quotes and a comma in between? I have a file 44.csv with 4 lines including the header like the below format: column1, column2, column3, column 4, column5, column6 12,455,"string with... (6 Replies)
Discussion started by: dhruuv369
6 Replies

6. Shell Programming and Scripting

Working with CSV files values enclosed with ""

I have a CSV file as shown below "1","SANTHA","KUMAR","SAM,MILLER","DEVELOPER","81,INDIA" "2","KAPIL","DHAMI","ECO SPORT","DEVELOPER","82,INDIA" File is comma delimited.All the field values are enclosed by double quotes. But while using awk or cut, it interprets the comma which is present in... (6 Replies)
Discussion started by: santhansk
6 Replies

7. Shell Programming and Scripting

How to delete the commas in a .CSV file that are enclosed in a string with double quotes?

Okay, I would like to delete all the commas in a .CSV file (TEST.CSV) or at least substitute them with empty space, that are enclosed in double quote. Please see the sample file as below: column 1,column 2,column 3,column 4,column 5,column 6,column 7,column 8,column 9,column 10... (8 Replies)
Discussion started by: dhruuv369
8 Replies

8. Shell Programming and Scripting

Delete all log files older than 10 day and whose first string of the first line is "MSH" or "<?xml"

Dear Ladies & Gents, I have a requirement to delete all the log files in /var/log/test directory that are older than 10 days and their first line begin with "MSH" or "<?xml" or "FHS". I've put together the following BASH script, but it's erroring out: for filename in $(find /var/log/test... (2 Replies)
Discussion started by: Hiroshi
2 Replies

9. Shell Programming and Scripting

Bash script - Print an ascii file using specific font "Latin Modern Mono 12" "regular" "9"

Hello. System : opensuse leap 42.3 I have a bash script that build a text file. I would like the last command doing : print_cmd -o page-left=43 -o page-right=22 -o page-top=28 -o page-bottom=43 -o font=LatinModernMono12:regular:9 some_file.txt where : print_cmd ::= some printing... (1 Reply)
Discussion started by: jcdole
1 Replies
All times are GMT -4. The time now is 09:06 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy