Shell Programming and Scripting

I have been trying to find a good solution for this seemingly simple task for 2 days, and I'm giving up and posting a thread. I hope someone can help me out!

I'm on HPUX, using sqlplus, mailx, awk, have some other tools available, but can't install stuff that isn't already in place (without a HUGE hassle anyway).

I have a file that is output from a sqlplus query, that is a comma seperated value file of the type:
First task is to create the messag ebody of the email, that I would like to be more readable like the following, with one 'paragraph' for each record or row. This way the email can be printed out and is easy enough to read if there are only a few rows.
If I could put the CSV into a 2 dimensional array I could do something like this in awk:

Code:

        for (row=2; row<=NR; row+=1)
                for (col=1; col<=NF; col+=1)
                        printf("%s: %s\n",csv[1][col], csv[row][col]);
                end
        end

But I'm not sure how to get this done properly and simply. I can use awk, sed, and this is all in ksh. There are other tools available too, so if you have an idea shout.

The second part of my problem is that I can't get this to email properly. I found a million examples, but none of them seem to work. We have very stringent rules here, (i work at a power utility) so I can't just install mutt or similar, but I have access to mail, mailx, and sendmail, but I think thats it for mailing purposes. Here's what I've got:

Code:

ux2dos list.prt > list.prt.dos
uuencode list.prt.dos list.csv > Attach
cat Body Attach > MessageAndy
mailx -m -s "subject" me@here.com < MessageAndy

Once I get problem 1 figured out, that will be the body of the message, while the attached file will be the original CSV file so if there are a ton of records it can be opened and sorted/manipulated in excel.

But the current code for mailing stuff gives me a test body of the message followed by a seemingly incorrectly processed uuencode file, something like this:
You get the idea. There was a point where i had it working properly, except that was before I realized I had to take the CSV and do the ux2dos on it, but once I threw in hte ux2dos bit it stopped working. Any ideas? I've tried just about every variation I could find in the forums, but there must be something I'm not understanding here.

I appreciate your help, and if you're in the kansas city area I'll meet you at a bar and buy you a beer sometime lol.

I think this should get you going on the awk statement

Code:

awk -F, '{gsub("\"","",$0)}; {if(NR==1) split($0,col,",")} NR==1 {next} {printf("%s: %s\n%s: %s\n%s: %s\n\n",col[1],$1,col[2],$2,col[3],$3)}' input.csv

I think that should work with just awk or nawk, but for sure works with gawk.

I really dont know how to format awk stuff or I would format that up for you.

As for the email problem Ive never had a problem with a unix formatted csv file opening in excel... Not sure why the un2dos command is goofing up your email formatting/encoding...

OK my biggest problem is that I want to be able to use this on a number of different queries, and most of my queries / CSV have 10-25 columns, and I'm not about to write out in scripts for each and every column depending on the particular query. I want to write this in a nice easy function.

you can loop the printf statement if needed.

something like:

Code:

for(i=1,i<=NF,i++)
  if(i == NF)
    printf("%s: $s\n\n",col[i],$i)
  else
    printf("%s: $s\n",col[i],$i)

Sorry, I dont have the time to test that out for you right now, but im sure someone else can whip it up or that may get you going.

Allright the CSV problem is solved. The first issue was the fact that the output of sql plus, while I used the colsep set to '","' worked properly, the beginning of the line and the end of the line were not quoted. So I thought I was working with:
But was actually working with:
The other problem was that for some reason the 'attachment' file began with a ^Z after encoding it. This means that the very first line of the attachment did not start with "begin 644 list.csv" like MIME header restrictions require. After removing that ^Z, the file attaches properly. If you are wondering if that is the case with your email, try running a cat on it but make sure you show all non-visible characters. I think the switch is -t but I'm not entirely sure. Anyway, here's how I run it now, and this works:

Code:

# Encapsulate each line with quotes in order to balance the quotes from the sql output
# Also deletes first line which is essentially blank
sed 's/^/"/;s/$/"/;1d;s/        //' list.prt > list.quoted

# Convert the file to dos format, but delete the strange control Z at the beg of the file
ux2dos list.quoted | sed '1s/^Z//' > list.prt.dos

# Encode the dos formatted file into CSV format as the attachment
uuencode list.prt.dos list.csv > Attachment

# Concat the attachment and the body together for sending
cat list.prt.dos Attachment > Message

# Send the message
mailx -m -s "subject" me@here.com < Message

So back to the first problem.
Here's what I've got so far:

Code:

function csv2mail {

awk  '{
        FS = '\",\"'
        if (NR==1){
                for (col=1; col<=NF; col+=1) {
                        head[col]=$col
                }
        } else {

        for (col=1; col<=NF; col+=1) {
                printf("%s: %s\n",head[col], $col)
        }
        }
}' $1
}

csv2mail list.prt > Body

Problem is that it isn't printing right. It seems to run the second for loop for each record including the first record, but each time the 'head' array is blank, so instead of getting:
All I get is:

awk -F, '{gsub("\"","",$0)} {if(NR==1) split($0,col,",")} NR==1 {next} {for(rd=1;rd<=NF;rd++) {if(NF==rd) {printf("%s: %s\n\n",col[rd],$rd)} else {printf("%s: %s\n",col[rd],$rd)}}}' input.csv

just a mix of your above one.. and the one I have already given you. I think your if statement is killing your your initial col variable.

edit: you can ditch the gsub line.. and change the field seperator to "," if you dont want to deal with changing the input.

Code:

my %hash;
while(<DATA>){
	chomp;
	if($. eq 1){
		my @tmp = $_=~/([^",]+)/g;
		%hash = map { $_=>$tmp[$_] } (0..$#tmp);
	}
	else{
		my @tmp = $_=~/([^",]+)/g;
		map { print $hash{$_},": ",$tmp[$_],"\n"} (0..$#tmp);
	}
}
__DATA__
"Name","Age","ID"
"name1","12","1"
"name2","13","2"
"name3","14","3"