Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

CSV file data extraction

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting CSV file data extraction
# 1  
Old 09-02-2014
Code CSV file data extraction
Hi

I am writing a shell script to parse a CSV file , in which i am facing a problem to separate the columns . Could some one help me with it.

Code:
IN301330/00001 pvavan kumar limited xyz@ttccpp.com
IN302148/00002 PRECIOUS SECURITIES (P) LTD  viash@yahoo.co.in
IN300239/00000 CENTRE india compay for you pvt ltx ifo@vikas.com
IN301696/000006 yourcomany pvt ltd pavan@pavan.com
IN300095/0000009 mycomany Pvt Ltd vijy@indianov1.com

Output:

Code:
$id=IN301330/00001
$name=Pavan Kumar Limited
$email=xyz@ttccpp.com

Moderator's Comments:
Mod Comment Please use CODE tags of sample input, output, and code.
Last edited by Don Cragun; 09-02-2014 at 02:54 AM.. Reason: Add more CODE tags.
# 2  
Old 09-02-2014
Hello Nanduri,

Following may help you in same.

Code:
awk '{id=$1;email=$NF; {for(i=2;i<NF;i++){a=a!~/[LTD ltd] || [limited]/?$i:a OFS $i}{gsub(/^[[:space:]]/,X,a);print id OFS a OFS email;a=""}}}' filename

Output will be as follows.

Code:
IN301330/00001 pvavan kumar limited xyz@ttccpp.com
IN302148/00002 PRECIOUS SECURITIES (P) LTD viash@yahoo.co.in
IN300239/00000 CENTRE india compay for you pvt ltx ifo@vikas.com
IN301696/000006 yourcomany pvt ltd pavan@pavan.com
IN300095/0000009 mycomany Pvt Ltd vijy@indianov1.com


EDIT: I have given only variables values in previous soluiton, if you want to print the variable names with values then following may help.

Code:
awk '{id=$1;email=$NF; {for(i=2;i<NF;i++){a=a!~/[LTD ltd] || [limited]/?$i:a OFS $i}{gsub(/^[[:space:]]/,X,a);print "$id= "id ORS "$name= " a ORS "$email= " email;a=""}}}'  filename

Output will be as follows.

Code:
$id= IN301330/00001
$name= pvavan kumar limited
$email= xyz@ttccpp.com
$id= IN302148/00002
$name= PRECIOUS SECURITIES (P) LTD
$email= viash@yahoo.co.in
$id= IN300239/00000
$name= CENTRE india compay for you pvt ltx
$email= ifo@vikas.com
$id= IN301696/000006
$name= yourcomany pvt ltd
$email= pavan@pavan.com
$id= IN300095/0000009
$name= mycomany Pvt Ltd
$email= vijy@indianov1.com

Thanks,
R. Singh
Last edited by RavinderSingh13; 09-02-2014 at 03:01 AM.. Reason: Added new solution which gives variables names also in output
# 3  
Old 09-02-2014
Code:
awk '{id=$1; email=$NF; $1=$NF=""; $0 = $0; $1 = $1; name=$0; print "$id=" id; print "$name=" name; print "$email=" email}' file

This User Gave Thanks to SriniShoo For This Post:
nanduri
# 4  
Old 09-02-2014
Quote:
Originally Posted by nanduri
Hi

I am writing a shell script to parse a CSV file , in which i am facing a problem to separate the columns . Could some one help me with it.

Code:
IN301330/00001 pvavan kumar limited xyz@ttccpp.com
IN302148/00002 PRECIOUS SECURITIES (P) LTD  viash@yahoo.co.in
IN300239/00000 CENTRE india compay for you pvt ltx ifo@vikas.com
IN301696/000006 yourcomany pvt ltd pavan@pavan.com
IN300095/0000009 mycomany Pvt Ltd vijy@indianov1.com

Output:

Code:
$id=IN301330/00001
$name=Pavan Kumar Limited
$email=xyz@ttccpp.com

Moderator's Comments:
Mod Comment Please use CODE tags of sample input, output, and code.
What you are trying to do is not clear. From what you have shown us, we could guess that what you want to do is:
  1. Ignore everything except the 1st line in your input,
  2. print a line containing $id= followed by the first field in your input line,
  3. print a line containing $name= followed by the contents of the 2nd through the next to the last fields in your input line with the case shifted for the first character in each field and separate each of those fields by a single space character, and
  4. print a line containing $email= followed by the contents of the last field from your input line.
Is this what you want?

If not please provide a complete description of what you are trying to do. And, if more than one line of input is to be processed, show us the complete output that you want (using CODE tags for input and output) for the sample input provided.

Please also get into the habit of always telling us what OS and shell you're using. Different systems have different utilities and many common utilities have additional/different options depending on what OS is being used. And, various shells have some common features, and some very different extensions that aren't available in other shells. Help us help you.

What have code have you tried to solve your problem?
# 5  
Old 09-02-2014
Yes Don ,

I am looking for the same as you described , next time i will give more detailed description .

Writing a script which parse the csv file and sends the mail to users .
# 6  
Old 09-02-2014
Quote:
Originally Posted by SriniShoo
Code:
awk '{id=$1; email=$NF; $1=$NF=""; $0 = $0; $1 = $1; name=$0; print "$id=" id; print "$name=" name; print "$email=" email}' file

Hi,

I don't understand the purpose of,
Code:
$0 = $0

and
Code:
$1 = $1

, if i remove it, it works yet ?
# 7  
Old 09-02-2014
OK, I'm surprised that the guess I made about the processing you wanted was correct, but if that is what you want, this seems to work:
Code:
awk '
{	print "$id=" $1
	out = "$name"
	for(i = 2; i < NF; i++) {
		c = substr($i, 1, 1)
		out = out (i == 2 ? "=" : " ") \
			(c ~ /[[:upper:]]/ ? tolower(c) : \
			(c ~ /[[:lower:]]/ ? toupper(c) : c)) substr($i, 2)
	}
	print out
	print "$email=" $NF
	exit
}' file

which produces the output:
Code:
$id=IN301330/00001
$name=Pvavan Kumar Limited
$email=xyz@ttccpp.com

as you requested.
If you remove the exit at the end, it will process every line in the file (instead of ignoring every line in the file except the first one), producing the output:
Code:
$id=IN301330/00001
$name=Pvavan Kumar Limited
$email=xyz@ttccpp.com
$id=IN302148/00002
$name=pRECIOUS sECURITIES (P) lTD
$email=viash@yahoo.co.in
$id=IN300239/00000
$name=cENTRE India Compay For You Pvt Ltx
$email=ifo@vikas.com
$id=IN301696/000006
$name=Yourcomany Pvt Ltd
$email=pavan@pavan.com
$id=IN300095/0000009
$name=Mycomany pvt ltd
$email=vijy@indianov1.com

If the capitalization of the 1st letter in each word in the name is not what you said you wanted, I assume you can easily modify this code to perform capitalization the way you want it.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Data extraction and converting into .csv file.

Hi All, I have a data file and need to extract and convert it into csv format: 1) Read and extract the line containing string ending with "----" (file sample_linebyline.txt file) and to make a .csv file from this. 2) To read the flat file flatfile_sample.txt which consists of similar data (... (9 Replies)
Discussion started by: abhi_123
9 Replies

2. Shell Programming and Scripting

Data extraction from .xml file

Hello, I'm attempting to extract 13 digit numbers beginning with 978 from a data file with the following command: awk '{ for(i=1;i<=NF;i++) if($i ~ /^978/) print $i; }' datafile > outfile This typically works. However, the new data file is an .xml file, and this command is no longer working... (6 Replies)
Discussion started by: palex
6 Replies

3. Shell Programming and Scripting

Compare 2 files of csv file and match column data and create a new csv file of them

Hi, I am newbie in shell script. I need your help to solve my problem. Firstly, I have 2 files of csv and i want to compare of the contents then the output will be written in a new csv file. File1: SourceFile,DateTimeOriginal /home/intannf/foto/IMG_0713.JPG,2015:02:17 11:14:07... (8 Replies)
Discussion started by: refrain
8 Replies

4. Shell Programming and Scripting

Data Manipulation on a .csv file

Hallo Friends, I need you help. My file has 5000 or so lines and currently looks like below(sample). Service Type,Origin,Destination,Rate Per Minute,Minimum Charge,Time Based Rate,Time Based From Day,Time Based To Day,Time Based From Time,Time Based To Time,Destination Prefix List,, VoIS... (3 Replies)
Discussion started by: kekanap
3 Replies

5. Shell Programming and Scripting

Data extraction from .txt file

Hey all, i´ve got the following problem: i´m aquiring data with an instrument and i get data in a .txt file. This is how the txt file looks like: Report of AU program poptau F1P=-49.986ppm F2P=-110.014ppm Target directory for serfile: D:/data/Spect500/nmr/Thoma/882 Linear... (17 Replies)
Discussion started by: expikx
17 Replies

6. Shell Programming and Scripting

FILE_ID extraction from file name and save it in CSV file after looping through each folders

FILE_ID extraction from file name and save it in CSV file after looping through each folders My files are located in UNIX Server, i want to extract file_id and file_name from each file .and save it in a CSV file. How do I do that? I have folders in unix environment, directory structure is... (15 Replies)
Discussion started by: princetd001
15 Replies

7. Shell Programming and Scripting

data extraction from a file

Hi Freinds, I have a file1.txt in the following format File1.txt I want to get 2 files from the above file filextra.txt should have the lines which are ending with "<" and remaining lines in the filecompare.txt file. Please help. (3 Replies)
Discussion started by: i150371485
3 Replies

8. Shell Programming and Scripting

data extraction from xml file

I have an of xml file as shown below <?xml version='1.0' encoding='ASCII' standalone='yes' ?> <Station Index="10264" > <Number Value="237895890" /> <Position Lat="-29.5" Lon="3.5" /> <MaxDepth Value="-4939" /> <VeloLines Count="24"> <VeloLine Index="0" > <Depth... (3 Replies)
Discussion started by: shashi792
3 Replies

9. Shell Programming and Scripting

Data Extraction From a File

Hi All, I have a requirement where I have to search the file with some text say "Exception". This exception word can be repeated for more then 10 times. Suppose the "Exception" word is repeated at line numbers say x=10, 50, 60, 120. Now I want to extract all the lines starting from x-5 to... (3 Replies)
Discussion started by: rrangaraju
3 Replies

10. UNIX for Advanced & Expert Users

extraction of data from a text file which follows certain pattern

hi everybody, i have a file, in it I need to extract some data that follows a particular pattern.. For example: my file contains like now running Speak225 sep 22 mon 16:34:05 2008 -------------------------------- ... (4 Replies)
Discussion started by: mohkris
4 Replies
Login or Register to Ask a Question