2 problems: Mailing CSV file / parsing CSV for display
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.
Quote:
Column Title: Column Value
Column Title: Column Value
Column Title: Column Value
Column Title: Column Value
Column Title: Column Value
Column Title: Column Value
If I could put the CSV into a 2 dimensional array I could do something like this in awk:
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:
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:
Quote:
test Body Body body Body
begin 644 list.csv
@JF#OIJASEN#@OIRHJ#@%(*)#@(N)F#@
#$(UTRJG VES(ENRJ (GN%T IM)IFGES)IRG
end
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
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.
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:
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:
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: So back to the first problem.
Here's what I've got so far:
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:
Quote:
Column Title: Column Value
Column Title: Column Value
Column Title: Column Value
Hello All,
I have an input CSV file like below, where first row data can be in different position after every run of the tool, i.e. pzTest in below example is in column 1, but it can be also in 3 column and same for all the headers in the first row.
pzTest, pzExtract, pxUpdate, pzInfo... (1 Reply)
Hi,
I have basic knowledge on unix shell scripting(not an expert).
My requirement is reading the csv file using the schema defined in the configuration file and if the condition is not mached then move the unmatched record to a error file and matched good records into other file.
In brief: ... (43 Replies)
Hi,
Newbie here and I need some help to parse a csv file that contains fields separated by ",". What I need to achieve here is, read the 1 line file and extract 240 fields and pass to a variable and then read the next 240 fields and pass to a variable, over and over. If anyone can assist that... (4 Replies)
Hi Members, I am stuck with the following problem. Request your kind help
I have an csv file which contains, 1 header record, data records and 1 footer record. Sample is as below
Contents of cm_update_file_101010.csv
--------------------------------------------------
... (6 Replies)
Hello list,
I am working on a csv file which contains two fields per record which contain IP addresses. What I am trying to do is find records which have identical fields(IP addresses) which occur 4(four) times, and if they do, delete all records with that specific identical field(ip address).
... (4 Replies)
Yes, there is a great doc out there that discusses parsing csv files with sed, and this topic has been covered before but not enough to answer my question (unix.com forums).
I'm trying to parse a CSV file that has optional quotes like the following:
"Apple","Apples, are fun",3.60,4.4,"I... (3 Replies)
Hey guys,
I'm in the process of learning PHP and BASH scripting. I'm getting there, slowly ;)
I would like some help with parsing a CSV file. This file contains a list of hostnames, dates, and either Valid, Expired, or Expired Soon in the last column.
Basically, I want to parse the file,... (12 Replies)
I have a CSV file that needs to through two seperate processes (in the end there will be 2 files (Dload.unl and Tload.unl and we'll say the input file name is mass.csv). I have a processfile() function that will call the process Dload funtion. In Dload I want to read mass.csv into Dload and then... (1 Reply)
I am trying to parse a csv file in the below 'name-value pair' format and then use the values corresponding to the name.
Type:G,Instance:instance1,FunctionalID:funcid,Env:dev,AppName:appname... (6 Replies)
I have a CSV file which contains number series as one of the fields. Some of the records of the field look like :
079661/3
I have to convert the above series as
079661
079662
079663
and store it as 3 different records.
Looking for help on how to achieve this. Am a newbie at Shell... (10 Replies)