This seems like it should be easy to do, but I'm not having much luck with it. I basically want to read a CSV and loop through each line so I can do some processing with the data from that line.

Any examles, or directions to documentation that dicuss looping would be appreciated. Thanks!

This message has been edited. Last edited by: Tim Bayer,

You can upload the CSV to WebFOCUS and the upload process will create a master so that you can then write a report against it. Is that what you are looking for or do you want to use Dialogue Manager? If the latter, use -READ inside a -REPEAT loop.

And if you have some non-Dialogue Manager WebFOCUS code between each -READ, you will need the NOCLOSE clause in the -READ statement.

What i do is write a master file, dynamically if need be, to assign a fixed length to each line, and a single field of format /A100, say.
then write a DEFINE that POSITs thru that /A100 record, looks for a comma, takes the substring of the line from that comma to the end, assigns that substring to a new field
then POSIT thru that new field.
i could loop till POSIT returned a 0.
here's some real code

DEFINE FILE HBMS3
CLIST/A150=COLORSET ;
-*IF STYLE IS 'ED59V506' OR 'ED59V516' OR 'ED59V526' THEN COLORSET || '18' ELSE COLORSET ;

p1/I3=POSIT(CLIST,150,',',1,'I2');
a1/I3=IF p1 EQ 0 THEN ARGLEN(150,CLIST,'I3')+1 ELSE p1;
COLOR1/A150= SUBSTR(150,CLIST,1, a1-1 , a1-1, COLOR1)  ;
REST1/A150=SUBSTR(150,CLIST,a1+1, 150, 150-a1, REST1);

p2/I3=IF p1 EQ 0 THEN 0 ELSE POSIT(REST1,150 ,',',1 ,'I2');
a2/I3=IF p2 EQ 0 THEN ARGLEN(150,REST1,'I3')+1 ELSE p2;
COLOR2/A150=SUBSTR(150,REST1,1,a2-1, a2-1,COLOR2);
REST2/A150 =SUBSTR(150,REST1,a2+1,150,150-a2, REST2);

p3/I3=IF p2 EQ 0 THEN 0 ELSE  POSIT(REST2,150 ,',',1 ,'I2');
a3/I3=IF p3 EQ 0 THEN ARGLEN(150,REST2,'I3')+1 ELSE p3;
COLOR3/A150=SUBSTR(150,REST2,1   ,a3-1,  a3-1, COLOR3);
REST3/A150 =SUBSTR(150,REST2,a3+1,150 ,150-a3, REST3);

p4/I3=IF p3 EQ 0 THEN 0 ELSE  POSIT(REST3,150 ,',',1 ,'I2');
a4/I3=IF p4 EQ 0 THEN ARGLEN(150,REST3,'I3')+1 ELSE p4;
COLOR4/A150=SUBSTR(150,REST3,1   ,a4-1,  a4-1, COLOR3);
REST4/A150 =SUBSTR(150,REST3,a4+1,150 ,150-a4, REST3); 
-* test for color=white and make sure it has 100 after it.
p5/I3=IF p4 EQ 0 THEN 0 ELSE  POSIT(REST4,150 ,',',1 ,'I2');
a5/I3=IF p5 EQ 0 THEN ARGLEN(150,REST4,'I3')+1 ELSE p5;
COLOR5/A150=SUBSTR(150,REST4,1   ,a5-1,  a5-1, COLOR5);
REST5/A150 =SUBSTR(150,REST4,a5+1,150 ,150-a5, REST5); 

p6/I3=IF p5 EQ 0 THEN 0 ELSE  POSIT(REST5,150 ,',',1 ,'I2');
a6/I3=IF p6 EQ 0 THEN ARGLEN(150,REST5,'I3')+1 ELSE p6;
COLOR6/A150=SUBSTR(150,REST5,1   ,a6-1,  a6-1, COLOR6);
REST6/A150 =SUBSTR(150,REST5,a6+1,150 ,150-a6, REST6); 


p7/I3=IF p6 EQ 0 THEN 0 ELSE  POSIT(REST6,150 ,',',1 ,'I2');
a7/I3=IF p7 EQ 0 THEN ARGLEN(150,REST6,'I3')+1 ELSE p7;
COLOR7/A150=SUBSTR(150,REST6,1   ,a7-1,  a7-1, COLOR7);
REST7/A150 =SUBSTR(150,REST6,a7+1,150 ,150-a7, REST7); 

p8/I3=IF p7 EQ 0 THEN 0 ELSE  POSIT(REST7,150 ,',',1 ,'I2');
a8/I3=IF p8 EQ 0 THEN ARGLEN(150,REST7,'I3')+1 ELSE p8;
COLOR8/A150=SUBSTR(150,REST7,1   ,a8-1,  a8-1, COLOR8);
REST8/A150 =SUBSTR(150,REST7,a8+1,150 ,150-a8, REST8); 

p9/I3=IF p8 EQ 0 THEN 0 ELSE  POSIT(REST8,150 ,',',1 ,'I2');
a9/I3=IF p9 EQ 0 THEN ARGLEN(150,REST8,'I3')+1 ELSE p9;
COLOR9/A150=SUBSTR(150,REST8,1   ,a9-1,  a9-1, COLOR9);
REST9/A150 =SUBSTR(150,REST8,a9+1,150 ,150-a9, REST9); 


p10/I3      = IF p8 EQ 0 THEN 0 ELSE  POSIT(REST9,150 ,',',1 ,'I2');
a10/I3      = IF p10 EQ 0 THEN ARGLEN(150,REST9,'I3')+1 ELSE p10;
COLOR10/A150= SUBSTR(150,REST9,1    ,a10-1,  a10-1, COLOR10);
REST10/A150 = SUBSTR(150,REST9,a10+1,150  ,150-a10, REST10 ); 
END
-RUN 
TABLE FILE HBMS3
 
 PRINT     COLOR1 COLOR2  COLOR3 COLOR4 COLOR5 COLOR6 COLOR7 COLOR8  COLOR9 COLOR10
 STDES   STDS2 DIV MONTH   FABCLASS STSSC SIZESCALE 
 IMSRPEURO   ISTPR3 ISTPR1
BY STYLE BY SEAS_CODE ON TABLE HOLD AS HBMS4
END 

Get the idea?

Susannah, the import feature is much easier though your method will always work. Have you tried it?

you mean IMPORT into excel or some such?
sure, but my end product needed to be a focus database, and its a batch job.
or do you mean something else?

my method allows you to look for all sorts of junk in the line. eg: we had a whole block of columns that were entered with Tabs and LineFeeds...that block had to be dumped into its own text file, and read with a whole new master inorder to xlate that sucker.
So the pedantic way works all manner of insane wonders. ha...just try to do this in Cog*** !

Ginny means the Dev Studio "Upload Data File" tool. Personally, I find it quite limiting. It converts the file from whatever format to text, and it's quite fussy about the content - you have to sure certain characters don't exist in the data. Three-quarter baked, in my opinion.

oh i see. well that's nice if its one single delimiter you're looking for.
if its not, if your file is , eg, the typical wild stuff sent from companies who collect data from your cash registers and then 'package' it and send you the mess.

Yes, but for a newbie who doesn't have those restrictions or problems, it is an easy solution. If it works great; if it doesn't, then you haven't wasted that much time.

Ginny,

Unfortunately, a newbie would not be aware they have to be careful that certain special characters should not be in the data - they would run the Upload, it would complete and then much later they may find out that some of the data was transformed or missing...

Sorry for the late reply, I was gone for a little while.

Thank you for all the feedback. I now have a master file that defines my csv (at least I think I do), so I should be able to get to all the data.

susannah, I guess to answer your question, No, I don't get the idea. I am trying to go through your example and I can't believe it's all that for a simple loop... Where does the looping actually occur in your example? Is there any way you can put a few comments in your code explaining what you’re doing?
What is the dialogue manger that was mentioned? Is that a gui way of doing loops? I will have to do some more searching through help for that.

Pardon my ignorance on all this, I come from a C# world where I just create objects for everything, for some reason webfocus has been very difficult for me to grasp. We were given training, but the trainer used the gui tools the entire time and didn't really touch the actual code.

Thanks again for all the help.

Tim,

Perhaps telling us what kind of processing you'd like to do will help get a better answer.

Cheers,

I basically want to loop through each row in the csv, which contains employee, employee start date, manager, manager email, and some other fields.

For each of those rows, I want to create a PDF pulling in certain information about the employee, creating a partially filled out employee review form. Then I want to attach that PDF to an email and send it to the manager.

Being a C# person, I created an executable that takes as the arguments employee, manager, manager email, and attachment location. Then it creates the email with the attachment an icalendar appointment and sends it. So for each row in the CSV I need to create the PDF and execute my exe with the parameters for that employee.

I still need to generate the PDF, but the execution of my exe from webfocus and sending the email actually works pretty slick.

I tend to over complicate things, so let me know if you can see a better way for this.

Thanks!

Is there only one line in the csv per employee or multiple? WebFOCUS is row and column oriented. Using a TABLE command, if you are using a PRINT verb, each row in the input file generates one row in the output file barring any records not passing the selection criteria. WebFOCUS takes care of reading all the rows in turn from the input file. You just have to tell it what you want to do.

So if you are screening by employee id and the other stuff, you would have something like:

TABLE FILE EMPCSV
PRINT FLD1 FLD2 ...
WHERE EMPID EQ '&EMPID'
WHERE MGR EQ &MGR
...
ON TABLE PCHOLD FORMAT PDF
END

Is this making any sense to you?

You don't have ReportCaster, do you? If so, this would be a VERY simple process.

Ginny has explained the basics. Your CSV files is just a data source - more or less the same as a DBMS table. You would just create one page per record, where the page contains the necessary fields either in tabular or free-form format, and ON EMPID PAGE-BREAK. That creates EVERYTHING you need.

If you have ReportCaster, it can autmoatically burst that doc and distribute via email to the managers. If not, that would be a manual process.

I think I do what you are asking all the time, however I bring it back as XML instead of csv. You could change the code to bring it back in csv and just split on the commas and '\n' (new lines).

using System.Net;
using System.IO;
using System.Data;
using System.Collections.Generic;
using System.Web;
using System.Text.RegularExpressions;

namespace focus
{
    public class focusTable : System.Web.UI.Page
    {
       
        public DataTable fetchDataTable(string query)
        {
            string sql = query;
            sql += "TABLE FILE FOCUSOUT\n";
            sql += "PRINT *\n";
            sql += "ON TABLE PCHOLD FORMAT XML\n";
            sql += "END";

            System.Text.UTF8Encoding encoding = new System.Text.UTF8Encoding();
            HttpWebRequest stuff = (HttpWebRequest)HttpWebRequest.Create("http://corpappd06/ibi_apps/WFServlet");
            stuff.Credentials = System.Net.CredentialCache.DefaultCredentials;
            stuff.KeepAlive = true;
            stuff.Method = "POST";
            stuff.ContentType = "application/x-www-form-urlencoded; charset=UTF-8";
            sql = "IBIF_adhocfex=" + Server.UrlEncode(sql);
            byte[] data = encoding.GetBytes(sql);
            Stream dataStream = stuff.GetRequestStream();
            dataStream.Write(data, 0, data.Length);
            dataStream.Close();
            stuff.Timeout = 660000;
            System.Net.WebResponse resp = stuff.GetResponse();
            System.IO.StreamReader sr = new System.IO.StreamReader(resp.GetResponseStream());
            string returnedText = sr.ReadToEnd().Trim();
            //HttpContext.Current.Response.Write(returnedText);
            string[] returnedStuff = returnedText.Split('\n');
            DataTable table = new DataTable();
            int cols = 0;
            for (int i = 0; i < returnedStuff.Length; i++)
            {
                if (returnedStuff[i].IndexOf("<report") >= 0)
                {
                    Regex x = new Regex("columns=\"");
                    cols = System.Int32.Parse(x.Split(returnedStuff[i].ToString())[1].ToString().Split('"')[0].ToString());
                }
                if (returnedStuff[i].IndexOf("<col  colnum=") >= 0)
                {
                    Regex x = new Regex("fieldname=\"");
                    string[] text = x.Split(returnedStuff[i].ToString());
                    Regex y = new Regex("\" alias=\"");
                    string[] fini = y.Split(text[1].ToString());
                    //HttpContext.Current.Response.Write(fini[0].ToString() + "<br>");
                    try
                    {
                        DataColumn colString = new DataColumn(fini[0].ToString());
                        table.Columns.Add(colString);
                    }
                    catch(System.Exception e)
                    {
                        HttpContext.Current.Response.Write(i);
                    }               
                }
                if (returnedStuff[i].IndexOf("<tr  linetype=") >= 0)
                {
                    i++;
                    string[] value = new string[cols];
                    for (int j = 0; j < cols; j++) value[j] = returnedStuff[i + j].Split('>')[1].Split('<')[0];
                    table.Rows.Add(value);
                    i = i + cols;
                }
            }
            return table;

        }


        public Dictionary<string, string[]> fetchDictionary(string query)
        {
            string sql = query;
            sql += "TABLE FILE FOCUSOUT\n";
            sql += "PRINT *\n";
            sql += "ON TABLE PCHOLD FORMAT XML\n";
            sql += "END";

            System.Text.UTF8Encoding encoding = new System.Text.UTF8Encoding();
            HttpWebRequest stuff = (HttpWebRequest)HttpWebRequest.Create("http://corpappd06/ibi_apps/WFServlet");
            stuff.Credentials = System.Net.CredentialCache.DefaultCredentials;
            stuff.KeepAlive = true;
            stuff.Method = "POST";
            stuff.ContentType = "application/x-www-form-urlencoded; charset=UTF-8";
            sql = "IBIF_adhocfex=" + Server.UrlEncode(sql);
            byte[] data = encoding.GetBytes(sql);
            Stream dataStream = stuff.GetRequestStream();
            dataStream.Write(data, 0, data.Length);
            dataStream.Close();
            stuff.Timeout = 660000;
            System.Net.WebResponse resp = stuff.GetResponse();
            System.IO.StreamReader sr = new System.IO.StreamReader(resp.GetResponseStream());
            string returnedText = sr.ReadToEnd().Trim();
            //HttpContext.Current.Response.Write(returnedText);
            string[] returnedStuff = returnedText.Split('\n');
            Dictionary<string, string[]> dict = new Dictionary<string, string[]>();
            int cols = 0;
            for (int i = 0; i < returnedStuff.Length; i++)
            {
                if (returnedStuff[i].IndexOf("<report") >= 0)
                {
                    Regex x = new Regex("columns=\"");
                    cols = System.Int32.Parse(x.Split(returnedStuff[i].ToString())[1].ToString().Split('"')[0].ToString());
                }

                if (returnedStuff[i].IndexOf("<tr  linetype=") >= 0)
                {
                    i++;
                    string[] value = new string[cols];
                    for (int j = 0; j < cols; j++) value[j] = returnedStuff[i + j].Split('>')[1].Split('<')[0];
                    if (!dict.ContainsKey(value[0].ToString())) dict.Add(value[0].ToString(), value);
                    i = i + cols;
                }
            }
            return dict;

        }
       

    }


}

here is a csv example.

void aggregateTrailer()
{
    
    
string focus = "DYNAM ALLOC FILE MSTRSCHD DA JCUSRNO.MSTRSCHD SHR REUSE\n"+
                "DEFINE FILE MSTRSCHD\n"+
                "BUILD/MDY= IF LINESTRT EQ 0 THEN 0 ELSE (LINESTRT - 694326);\n"+
                "END\n"+
                "TABLE FILE MSTRSCHD\n"+
                "ON TABLE PCHOLD FORMAT COMMA\nEND";
	
 UTF8Encoding encoding = new UTF8Encoding();

    HttpWebRequest stuff = (HttpWebRequest)HttpWebRequest.Create("http://corpappp18/ibi_apps/WFServlet");
    stuff.Credentials = System.Net.CredentialCache.DefaultCredentials;
    stuff.KeepAlive = true;
    stuff.Method = "POST";
    stuff.ContentType = "application/x-www-form-urlencoded; charset=UTF-8";
    sql = "IBIF_adhocfex=" + Server.UrlEncode(sql);
    byte[] data = encoding.GetBytes(sql);
    Stream dataStream = stuff.GetRequestStream();
    dataStream.Write(data, 0, data.Length);
    dataStream.Close();
    stuff.Timeout = 60000;
    System.Net.WebResponse resp = stuff.GetResponse();
    System.IO.StreamReader sr = new System.IO.StreamReader(resp.GetResponseStream());
    string returnedText = sr.ReadToEnd().Trim();
    string[] returnedStuff = returnedText.Split('\n');

     	string text = "<table class=\"tbl\"><tr><th class=\"tbl\">Number Of Trailers</th>"+
 	"<th class=\"tbl\">Total<br>Days<br>Retained</th>"+
 	"<th class=\"tbl\">Totals Cost</th></tr>";
 	
      for (int i = 0; i < returnedStuff.Length; i++)
            {
             string[] moStuff = returnedStuff[i].Split(',');
             text += "<tr><td class=\"tbl\">"+moStuff[0].ToString().Replace("\"", "") +"</td>"+
             "<td class=\"tbl\">"+moStuff[1].ToString()+"</td>"+
             "<td class=\"tbl\">$"+moStuff[2].ToString().Replace("\"", "") +".00</td><tr>";
             }
             text += "</table><p>";
    Response.Write(text);

    }

Thanks for all the responses and examples, this will give me a lot to play around with.

Lucas, I’m extremely interested in your example. I have the HttpWebRequest Working but I’m getting returned the Webfocus log in screen. How are you passing your credentials? I see your passing CredentialCache.DefaultCredentials. I don’t understand how that’s working to authenticate you?

The credentials I am sending are not for WF authentication. I am only telling the server (the one in which the C# code rides on) which account to use when making the request (for the proxy) to WF. I am sending my stuff from a trusted server and do not need to pass credintials, I just ask for data, receive and parse. I am sure the credentials you need can be passed in the request via &varibles but I just don't know which ones you need to send. Perhaps someone will chime in and help us out with that piece and I will ask my WF admin why I don't have to pass any login credintials and perhaps I can get you that answer. The example I have given is just a simple example of how you can mashup C# and WebFOCUS acuired data to run .NET apps there by allowing .NET guys who don't know a thing about Focus to send in SQL and get back data for use in anything they please. (ie. PDF's, graph's, web app's, windows forms and the list goes on .......)I'll post back when I get my answer from my WF admin guy.

I am passing the user name and password with &varibles, and now it is authenticating and dumping my XML to the screen. Mine is a little different from yours as I'm just building an exe I can kick off. Here is the query I'm passing in:

webFocusQuery = "IBIC_user=USERNAME&IBIC_pass=PASSWORD&IBIF_adhocfex=" + HttpUtility.UrlEncode(webFocusQuery);

I am still interested in what your wf admin says. I am not too comfortable passing the username and password as &varibles even though it's just internal.

I definately did not see the post going this direction when I first post it, but this is very cool being able to query with webfocus and then build C# objects off that data.

OK long story short on our credintials.....Access to the server is limited to two user's (administrator and myself) and therefore the server in on 'trust' with WF. There is more to it than that, but that is the abbreviated version.I am very glad we could find a solution. Let's mark this thread solved!!!!

Thanks for looking in to that. I am surprised Information Builders does not have more information on integrating webfocus and .net, seems like a really powerful combination.

Thanks again everyone!