|
|
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 OPENDARWIN
uniq
UNIQ(1) BSD General Commands Manual UNIQ(1) NAME
uniq -- report or filter out repeated lines in a file SYNOPSIS
uniq [-c | -d | -u] [-i] [-f num] [-s chars] [input_file [output_file]] DESCRIPTION
The uniq utility reads the specified input_file comparing adjacent lines, and writes a copy of each unique input line to the output_file. If input_file is a single dash ('-') or absent, the standard input is read. If output_file is absent, standard output is used for output. The second and succeeding copies of identical adjacent input lines are not written. Repeated lines in the input will not be detected if they are not adjacent, so it may be necessary to sort the files first. The following options are available: -c Precede each output line with the count of the number of times the line occurred in the input, followed by a single space. -d Only output lines that are repeated in the input. -f num Ignore the first num fields in each input line when doing comparisons. A field is a string of non-blank characters separated from adjacent fields by blanks. Field numbers are one based, i.e. the first field is field one. -s chars Ignore the first chars characters in each input line when doing comparisons. If specified in conjunction with the -f option, the first chars characters after the first num fields will be ignored. Character numbers are one based, i.e. the first character is character one. -u Only output lines that are not repeated in the input. -i Case insensitive comparison of lines. DIAGNOSTICS
The uniq utility exits 0 on success, and >0 if an error occurs. COMPATIBILITY
The historic +number and -number options have been deprecated but are still supported in this implementation. SEE ALSO
sort(1) STANDARDS
The uniq utility is expected to be IEEE Std 1003.2 (``POSIX.2'') compatible. HISTORY
A uniq command appeared in Version 3 AT&T UNIX. BSD
June 6, 1993 BSD