Solved

CSV handling

Posted on 2004-08-03
16
461 Views
Last Modified: 2010-05-18
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
Comment
Question by:ehout
  • 8
  • 8
16 Comments
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 11704836
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
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 11704966
Ooops, the csv file posted has typos, but it doesn't matter -- the script works.
0
 
LVL 3

Author Comment

by:ehout
ID: 11705182
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
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 11705645
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
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 11705692

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
 
LVL 3

Author Comment

by:ehout
ID: 11709577
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
 
LVL 3

Author Comment

by:ehout
ID: 11712977
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
 
LVL 3

Author Comment

by:ehout
ID: 11713375
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 11714534
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
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 11714724
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
 
LVL 3

Author Comment

by:ehout
ID: 11715209
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
 
LVL 3

Author Comment

by:ehout
ID: 11715565
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
 
LVL 29

Accepted Solution

by:
MikeOM_DBA earned 500 total points
ID: 11715781
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
 
LVL 3

Author Comment

by:ehout
ID: 11715902
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
 
LVL 3

Author Comment

by:ehout
ID: 11715927
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
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 11716487
Thanks, glad to be of help.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Have you ever been frustrated by having to click seven times in order to retrieve a small bit of information from the web, always the same seven clicks, scrolling down and down until you reach your target? When you know the benefits of the command l…
The purpose of this article is to demonstrate how we can upgrade Python from version 2.7.6 to Python 2.7.10 on the Linux Mint operating system. I am using an Oracle Virtual Box where I have installed Linux Mint operating system version 17.2. Once yo…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now