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.
I am looking for something like this
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
Last edited by Scrutinizer; 05-17-2014 at 04:13 PM..
Reason: Additional CODE tags
If you are on a Debian-based Linux system, there is: csvtool (1) - tool for performing manipulations on CSV files available for install:
Best wishes ... cheers, drl
I think this will include the content of 7th and 8th postion to the new file. But I am looking to create a new csv file with all the data except 7th and 8th position.(rule definition and mismatched conditions).
---------- Post updated at 01:56 AM ---------- Previous update was at 01:52 AM ----------
Thanks drl...Will I be able to access the commands that are used by this tool ? or this is something that will process the original data and give the result back in a new file. If that is the case then it is not going to serve my purpose.
Location: Saint Paul, MN USA / BSD, CentOS, Debian, OS X, Solaris
Posts: 2,288
Thanks Given: 430
Thanked 480 Times in 395 Posts
Hi.
Here is an example with 5 columns (I usually say fields), and the commands to csvtool will copy 1-2 and 5, effectively deleting columns 3 and 4. Note that some columns contain embedded commas and spaces:
producing:
Quote:
Originally Posted by Litu19
Thanks drl...Will I be able to access the commands that are used by this tool ? or this is something that will process the original data and give the result back in a new file. If that is the case then it is not going to serve my purpose.
Sorry, I don't understand this statement / question.
Thanks for such a nice brief along with example. I understand that csvtool is an application which takes data as an input and based on the instruction it will process the input data and give the result in as an output.
Let me explain the scenario here what we are looking here.
From an automated application we are getting an input data file in csv format and we need to format that csv file in a specific format as I mentioned above. Here we maynt use any other application like csv tool to format the data (since the file may not be accessible to any other third party application). We are looking for some scripting command to format the input csv file and generate another csv file in side that automated tool which intially generated the input file.
Below is the part of the script which is generating the csv file and creating another csv file from the initial csv file.
Currently these 2 commands are used in a shell script which is executing in the automation tool I have explained above. We have observing some problem in second command used to remove the carriage return. Instead of using the second command can you help me to create a new csv file with some specific columns/fields.
I hope you understood my limitation of not using csvtool. We can only use some scriting command to create a new csv file.
---------- Post updated at 04:47 PM ---------- Previous update was at 02:32 PM ----------
In our case we have csv file and data in below format.
Note: Here we have space, carriage return and white spaces in permanent and current location.
I think we have issue that we have carriage return in place for 3rd and 4th field in csv file.
Location: Saint Paul, MN USA / BSD, CentOS, Debian, OS X, Solaris
Posts: 2,288
Thanks Given: 430
Thanked 480 Times in 395 Posts
Hi.
Thanks for your extended reply.
Let me review what I've understood so far.
Quote:
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 then supplied a demonstration script to illustrate one solution with a utility called csvtool.
Quote:
Here we have space, carriage return and white spaces in permanent and current location.
You have stated a fact. You have implied that you wish to handle these with the use of awk, cut, and/or sed. You seem to be saying that those commands are OK to use, but that csvtool is not acceptable. I don't understand your basis for that, and it seems to me that it is a distinction without a differenceDistinction without a difference - Wikipedia, the free encyclopedia
The csvtool command can, in fact, handle embedded spaces, commas, and newlines. Here's an example of that using exactly the same csvtool command, but with a data file that has an embedded newline in a column that will be kept, and in a column that will be effectively deleted:
The output is as I would expect: the column to be kept with an embedded newline is still there, and the column also with an embedded newline to be deleted, is gone.
Because we don't seem to be communicating, I will withdraw and wait for someone else to reply who understands your situation better than I do.
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)
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)
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)
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)
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)
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)
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)
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)
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)