|
|
Sponsored Content
Top Forums
Shell Programming and Scripting
Text file to CSV with field data separated by blank lines
Post 302609543 by ops_team on Tuesday 20th of March 2012 05:43:12 AM
03-20-2012
Need to remove cooma from description in Comma seperated csv file
Hi All,
I have csv file which is comma seperated having data like :
CSV FILE:
121,11852,2011 Q4,0201, ,TRADITIONAL,Internal Fraud,6/14/2010,"100,000,000","Unauthorized Trading (e.g. Violating guidelines, Hiding losses, Securities Valuation)",1,
121,11853,2011 Q4,0201, ,TRADITIONAL,Internal Fraud,6/14/2010,"100,000,000",Fraud and Collusion,2,
121,11854,2011 Q4,0201, ,TRADITIONAL,Internal Fraud,6/14/2010,"100,000,000",Embezzlement,3,
In this file for few columns actual data contains comma e.g. column no 9,10 in first row. ("100,000,000","Unauthorized Trading (e.g. Violating guidelines, Hiding losses, Securities Valuation)").
For columns where actual data contains comma those columns are prefixed and suffixed by double quote in csv (" ").
I want to replace commas which comes in between double quotes with semicolon. (In short where actual column data contains comma i want it to replace with semicolon) and also those double quotes should get deleted afterwards.
Output should be like this where comma should be only file delimiter:
121,11852,2011 Q4,0201, ,TRADITIONAL,Internal Fraud,6/14/2010,100;000;000,Unauthorized Trading (e.g. Violating guidelines; Hiding losses; Securities Valuation),1,
121,11853,2011 Q4,0201, ,TRADITIONAL,Internal Fraud,6/14/2010,100;000;000,Fraud and Collusion,2,
121,11854,2011 Q4,0201, ,TRADITIONAL,Internal Fraud,6/14/2010,100;000;000",Embezzlement,3,
Please reply
I have csv file which is comma seperated having data like :
CSV FILE:
121,11852,2011 Q4,0201, ,TRADITIONAL,Internal Fraud,6/14/2010,"100,000,000","Unauthorized Trading (e.g. Violating guidelines, Hiding losses, Securities Valuation)",1,
121,11853,2011 Q4,0201, ,TRADITIONAL,Internal Fraud,6/14/2010,"100,000,000",Fraud and Collusion,2,
121,11854,2011 Q4,0201, ,TRADITIONAL,Internal Fraud,6/14/2010,"100,000,000",Embezzlement,3,
In this file for few columns actual data contains comma e.g. column no 9,10 in first row. ("100,000,000","Unauthorized Trading (e.g. Violating guidelines, Hiding losses, Securities Valuation)").
For columns where actual data contains comma those columns are prefixed and suffixed by double quote in csv (" ").
I want to replace commas which comes in between double quotes with semicolon. (In short where actual column data contains comma i want it to replace with semicolon) and also those double quotes should get deleted afterwards.
Output should be like this where comma should be only file delimiter:
121,11852,2011 Q4,0201, ,TRADITIONAL,Internal Fraud,6/14/2010,100;000;000,Unauthorized Trading (e.g. Violating guidelines; Hiding losses; Securities Valuation),1,
121,11853,2011 Q4,0201, ,TRADITIONAL,Internal Fraud,6/14/2010,100;000;000,Fraud and Collusion,2,
121,11854,2011 Q4,0201, ,TRADITIONAL,Internal Fraud,6/14/2010,100;000;000",Embezzlement,3,
Please reply
|
|
10 More Discussions You Might Find Interesting
1. Shell Programming and Scripting
Could any one help me in basic shell script to export text file data to csv.
I need to export only particular data from text file to csv column.
I am a newbie to UNIX could anyone help me with sample script code (3 Replies)
Discussion started by: l_jayakumar
3 Replies
2. Shell Programming and Scripting
I have a csv file which has three columns
mem no. name surname
1234 John Smith
12345 John Doe
I want to change the mem no. to add TF to the mem no. field i.e.
mem no. name surname
1234TF John Smith
12345TF John Doe
How do you do this for all records in the file? (3 Replies)
Discussion started by: Pablo_beezo
3 Replies
3. Shell Programming and Scripting
Hi all,
Sorry for the title, I was unsure how to word my issue. I'll get right to the issue. In my text file, I need to find all lines with the same data in the first field. Then I need to create a file with the matching lines merged into one. So my original file will look something like... (4 Replies)
Discussion started by: rstev39147
4 Replies
4. UNIX for Dummies Questions & Answers
Hi,
I am using BASH. How can I remove any lines in a text file that are either blank or begin with a # (ie. comments)? Thanks in advance.
Mike (3 Replies)
Discussion started by: msb65
3 Replies
5. Shell Programming and Scripting
Hi.
I have a tab separated file that has a couple nearly identical lines. When doing:
sort file | uniq > file.new
It passes through the nearly identical lines because, well, they still are unique.
a)
I want to look only at field x for uniqueness and if the content in field x is the... (1 Reply)
Discussion started by: rocket_dog
1 Replies
6. Shell Programming and Scripting
I have a csv file that I need to extract some data from depending on another field after reading info from another text file.
The text file would say have 592560 in it.
The csv file may have some data like so
Field 1 Field2 Field3 Field4 Field5 Field6
20009756 1 ... (9 Replies)
Discussion started by: GroveTuckey
9 Replies
7. Shell Programming and Scripting
Hi.. I'm facing a trouble in replacing two blank lines in a file using shell script...
I used sed to search a line and insert two blank lines after the searchd line using the following sed command.
sed "/data/{G;G;}/" filename . In the file, after data tag, two lines got inserted blank lines..... (4 Replies)
Discussion started by: arjun_arippa
4 Replies
8. Shell Programming and Scripting
hello, here is an example:
9.07
9.05
0.00
2.28
0.00
0.08
1.93
3.62
10.97
12.03
12.03
0.00
2.73
0.00
0.07 (3 Replies)
Discussion started by: Baron1
3 Replies
9. Shell Programming and Scripting
Gents
Using the script attached (raw2csv). i use to create the file .csv.. The input file is called 201.raw.
Kindly can you check if there is easy way to do it. The script works fine but takes a lot time to process
Thanks for your help (8 Replies)
Discussion started by: jiam912
8 Replies
10. Shell Programming and Scripting
Hi,
I tried the following options but was unable to delete blank lines from file
Input file = temp.hash.txt
temp.hash.txt content
90
0
89.56
0
0
57575.4544
56.89 (9 Replies)
Discussion started by: uuuunnnn
9 Replies
LEARN ABOUT SUSE
join
JOIN(1) User Commands JOIN(1) NAME
join - join lines of two files on a common field SYNOPSIS
join [OPTION]... FILE1 FILE2 DESCRIPTION
For each pair of input lines with identical join fields, write a line to standard output. The default join field is the first, delimited by whitespace. When FILE1 or FILE2 (not both) is -, read standard input. -a FILENUM print unpairable lines coming from file FILENUM, where FILENUM is 1 or 2, corresponding to FILE1 or FILE2 -e EMPTY replace missing input fields with EMPTY -i, --ignore-case ignore differences in case when comparing fields -j FIELD equivalent to `-1 FIELD -2 FIELD' -o FORMAT obey FORMAT while constructing output line -t CHAR use CHAR as input and output field separator -v FILENUM like -a FILENUM, but suppress joined output lines -1 FIELD join on this FIELD of file 1 -2 FIELD join on this FIELD of file 2 --check-order check that the input is correctly sorted, even if all input lines are pairable --nocheck-order do not check that the input is correctly sorted --help display this help and exit --version output version information and exit Unless -t CHAR is given, leading blanks separate fields and are ignored, else fields are separated by CHAR. Any FIELD is a field number counted from 1. FORMAT is one or more comma or blank separated specifications, each being `FILENUM.FIELD' or `0'. Default FORMAT outputs the join field, the remaining fields from FILE1, the remaining fields from FILE2, all separated by CHAR. Important: FILE1 and FILE2 must be sorted on the join fields. E.g., use `sort -k 1b,1' if `join' has no options. If the input is not sorted and some lines cannot be joined, a warning message will be given. AUTHOR
Written by Mike Haertel. REPORTING BUGS
Report join bugs to bug-coreutils@gnu.org GNU coreutils home page: <http://www.gnu.org/software/coreutils/> General help using GNU software: <http://www.gnu.org/gethelp/> COPYRIGHT
Copyright (C) 2009 Free Software Foundation, Inc. License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>. This is free software: you are free to change and redistribute it. There is NO WARRANTY, to the extent permitted by law. SEE ALSO
The full documentation for join is maintained as a Texinfo manual. If the info and join programs are properly installed at your site, the command info coreutils 'join invocation' should give you access to the complete manual. GNU coreutils 7.1 July 2010 JOIN(1)