• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 491
  • Last Modified:

CSV handling

Hi,

For a certain app I need te following functionality:
A csv with multiple lines is stored in a place. I need a script that takes this csv for every line looks to a certain filed. This field contains a word( 2nd field, actually a product or department name). Based on this word, a new csv file with only this line in it must be made and mailed to a mailaddress that belongs to the Word mentioned above.

I am looking for a script (either c, shell or perl) which can help me handling this csv data?
Does anyone know of such a thing?

If someone gives me a running script which actually (more or less) does the thing mentioned above I'll raise the points to 600. I someone wants to give it a try, tekstfields are surrounded by double quotes in this csv.

Kind regards
0
ehout
Asked:
ehout
  • 8
  • 8
1 Solution
 
MikeOM_DBACommented:
Try this:

1) Create a mail address file:

cat  - <<! >mailaddr.txt
#Dept e-Mail
d10 d10@foobar.com
d20 d20@foobar.com
d30 d30@foobar.com
d40 d40@foobar.com
!

2) Create a test csv file:

cat - <<! >file1.csv
aa,d10,new data for dept 10
aa,a1 data for dept 10
aa,a2 data for dept 10
aa,a3 data for dept 10
bb,d20,new data for dept 20
bb,b1 dbtb for dept 10
bb,b2 dbtb for dept 10
bb,b3 dbtb for dept 10
cc,d30,new dbtb for dept 30
cc,c1 dctc for dept 10
cc,c2 dctc for dept 10
cc,c3 dctc for dept 10
dd,d40,new data for dept 40
dd,d1 ddtd for dept 10
dd,d40,new data for dept 40
dd,d1 ddtd for dept 10
dd,d2 ddtd for dept 10
dd,d3 ddtd for dept 10
!

3) Execute the script:

#!/bin/ksh
cat mailaddr.txt|grep -v ^#|\
while read d0 e0
do
  awk -F, '{if ( $2 == dept ) print $0;}' dept=$d0 file1.csv|\
  echo "mailx -s 'New data arrived' $e0"
done

Results:
mailx -s 'New data arrived' d10@foobar.com
mailx -s 'New data arrived' d20@foobar.com
mailx -s 'New data arrived' d30@foobar.com
mailx -s 'New data arrived' d40@foobar.com
0
 
MikeOM_DBACommented:
Ooops, the csv file posted has typos, but it doesn't matter -- the script works.
0
 
ehoutAuthor Commented:
Hi,

Thanx for the reply,
I'm not quite familiar with shell programming, but it looks like it just looks if a certain value is present in at least 1 of the lines.

What I'm looking for is to cut, export or at least copy the line of data to a seperate file. Can I do this with help of that awk thing?

So if d40 is found twice in the file, two actions need to be taken.

Kind regards,

0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
MikeOM_DBACommented:
Did you execute the script?

The script does this:

#!/bin/ksh

#1) Assumes there is a file with two fields, the department number (search field) and the e-mail
#    address' where to send the info.
cat mailaddr.txt|grep -v ^#|\
#2) The where loop takes this mailaddr file and 'reads' the search string (d0) and the
#    e-mail address (e0)
while read d0 e0
do
  #3) The awk checks if the serach string (d0) matches field #2 of any line in the file1.csv file
  #    and if correct, prints the entire line
  awk -F, '{if ( $2 == dept ) print $0;}' dept=$d0 file1.csv|\
  #4) The output from awk is input to mailx and sent to e-mail address (e0)
  echo "mailx -s 'New data arrived' $e0"
done

0
 
MikeOM_DBACommented:

Now, if you just want to copy the line(s) to a separate file, then change the script to this which will generate a separate file for each search string in mailaddr.txt which is found in the .csv file:

#!/bin/ksh
cat mailaddr.txt|grep -v ^#|\
while read d0 e0
do
  awk -F, '{if ( $2 == dept ) print $0;}' dept=$d0 file1.csv >${d0}_file1.csv
done




0
 
ehoutAuthor Commented:
Hai,

Yes, I did execute it.

And, like in your first comment, I got
mailx -s 'New data arrived' d10@foobar.com
mailx -s 'New data arrived' d20@foobar.com
mailx -s 'New data arrived' d30@foobar.com
mailx -s 'New data arrived' d40@foobar.com

as output, while I expected
mailx -s 'New data arrived' d10@foobar.com
mailx -s 'New data arrived' d20@foobar.com
mailx -s 'New data arrived' d30@foobar.com
mailx -s 'New data arrived' d40@foobar.com
mailx -s 'New data arrived' d40@foobar.com

(last line double because d40 is on 2 lines i your script).

I'll try the cope thing tomorrow, I'm not at the office right now.

BTW, I don't have the korn shell, so I use /bin/sh on the first line

Kind regards
0
 
ehoutAuthor Commented:
Hi,

I ran the script,
It makes voor every possible word 1 csv file.
So in your script I get 4 files, d40 is twice in de last csv file. Can I get those 2 lines also in seperate files?

Thanx for your help so far already.
you're way on route for the 600.

Kind regards
0
 
ehoutAuthor Commented:
OK,


Tried some things, but still need help.

Consider the following data:
mailaddr.txt

#Dept e-Mail
Hypotheken 1webmaster@levob.nl
Schadeverzekeringen 2webmaster@levob.nl
Sparen 3webmaster@levob.nl
Beleggen 4webmaster@levob.nl
Lenen 5webmaster@levob.nl
Pensioenen 6webmaster@levob.nl
Levensverzekeringen 7webmaster@levob.nl
Anders 8webmaster@levob.nl


and csv66832.csv

2004-08-03,"Hypotheken","Nee","43534543","nvt","M","em","van den","hout","mesdaglaan","20","3931 tt","woudenberg","033-4344303","033-4790305","eric@houtjes.nl","Ik ben moe"
2004-08-03,"Schadeverzekeringen","Nee","43534543","nvt","M","em","van den","hout","mesdaglaan","20","3931 tt","woudenberg","033-4344303","033-4790305","eric@houtjes.nl","Ik ben moe"
2004-08-03,"Sparen","Nee","43534543","nvt","M","em","van den","hout","mesdaglaan","20","3931 tt","woudenberg","033-4344303","033-4790305","eric@houtjes.nl","Ik ben moe"
2004-08-03,"Beleggen","Nee","43534543","nvt","M","em","van den","hout","mesdaglaan","20","3931 tt","woudenberg","033-4344303","033-4790305","eric@houtjes.nl","Ik ben moe"
2004-08-03,"Lenen","Nee","43534543","nvt","M","em","van den","hout","mesdaglaan","20","3931 tt","woudenberg","033-4344303","033-4790305","eric@houtjes.nl","Ik ben moe"
2004-08-03,"Pensioenen","Nee","43534543","nvt","M","em","van den","hout","mesdaglaan","20","3931 tt","woudenberg","033-4344303","033-4790305","eric@houtjes.nl","Ik ben moe"
2004-08-03,"Levensverzekeringen","Nee","43534543","nvt","M","em","van den","hout","mesdaglaan","20","3931 tt","woudenberg","033-4344303","033-4790305","eric@houtjes.nl","Ik ben moe"
2004-08-03,"Anders","Nee","43534543","nvt","M","em","van den","hout","mesdaglaan","20","3931 tt","woudenberg","033-4344303","033-4790305","eric@houtjes.nl","Ik ben moe"
2004-08-03,"Hypotheken","Nee","43534543","nvt","M","em","van den","hout","mesdaglaan","20","3931 tt","woudenberg","033-4344303","033-4790305","eric@houtjes.nl","Ik ben moe"

And the script

#!/bin/sh

cat mailaddr.txt|grep -v ^#|\
while read d0 e0
do
  awk -F, '{if ( $2 == "\"dept\"" ) print $0;}' dept=$d0 csv66832.csv >output/${d0}_csv66832.csv
done

It is producing all empty files, any idea why?

Kind regards
0
 
MikeOM_DBACommented:
Because of the quotes ("), try this:

#!/bin/sh

cat mailaddr.txt|grep -v ^#|\
while read d0 e0
do
  awk -F, '{if ( $2 == """ dept """ ) print $0;}' dept=$d0 csv66832.csv >output/${d0}_csv66832.csv
done
0
 
MikeOM_DBACommented:
Ooops, corrected script is:

#!/bin/ksh
cat mailaddr.txt|grep -v ^#|\
while read d0 e0
do
  awk -F, '{if ( substr($2,2,length($2)-2) == dept ) print $0;}' dept=$d0 csv66832.csv \
       >output/${d0}_csv66832.csv
done
0
 
ehoutAuthor Commented:
Thanx, this helps.
Now the only thing left is this:

Can I get every line of the input file in a seperate file?
(in your first example, 2 lines were had department d40, I'ld like both lines in a seperate csv file.)

Kind regards
0
 
ehoutAuthor Commented:
Hi,

Could you say why this won't work? What am I doing wrong?

#!/bin/sh
MY_nr="0"
cat mailaddr.txt|grep -v ^#|\
while read d0 e0
do
 awk -F, '{if ( substr($2,2,length($2)-2) == dept ) print $0; $MY_nr=$NF}' dept=$d0 csv66832.csv >output/$MY_nr_${d0}_csv66832.csv
done

Kind regards
0
 
MikeOM_DBACommented:
Here:
                  $MY_nr=$NF

You cannot set an external variable inside awk script.

Your new requirement is that you want seperate files for each equal line?
Ok try this:

#!/bin/ksh
cat mailaddr.txt|grep -v ^#|\
while read d0 e0
do
  MY_nr=0
  awk -F, '{if(substr($2,2,length($2)-2) == dept)print $0;}'\
            dept=$d0 csv66832.csv |\
  while read lin
  do
   echo $lin >${MY_nr}_${d0}_csv66832.csv
   (( MY_nr += 1 ))
  done
done

0
 
ehoutAuthor Commented:
Well,
To be honest, it's not exactly a new requirement,
I've stated it more or less in the question and in several comments.
However, Thanx for your patience and helping me out.
I'll give you 100 points

...
...
...
;-)
Just kidding.

Kind regards
0
 
ehoutAuthor Commented:
I'm sorry, couldn't raise the points anymore, dunno why.
I'll post a seperate question for 100 points. Just put a comment in the and I'll accpet, OK?
0
 
MikeOM_DBACommented:
Thanks, glad to be of help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 8
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now