PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 02/13/2018 5:37 PM by  Orlando Gray
Sending Results of Query to a csv file
 20 Replies
Sort:
You are not authorized to post a reply.
Author Messages
Brad Schauer
Private
Private
Veteran Member
(222 points)
Veteran Member
Posts:76


Send Message:

--
07/20/2010 5:09 PM
    New to creating ProcessFlows. I created a flow that has three queries, the result of which I can use MsgBuilder and an Email node to send the results via e-mail.

    I am trying to figure out how to send the results to a csv file. I have tried using the file access node "write to file" but my results come back on one line.

    For example if I am returning ID and Name into a txt file, I want to see:

    ID,Name
    1,Tom
    2,Mary

    What is being written to the file is:
    1,Tom,2,Mary

    Can anyone point me in the right direction?

    Any help is appreciated.

    Brad
    John Henley
    Private
    Private
    Senior Member
    (9641 points)
    Senior Member
    Posts:3231


    Send Message:

    --
    07/20/2010 6:17 PM
    Hi Brad,
    I use the "append to file" in a loop. You will need to include a CR/LF in each record. So, create a variable in the start node, call it CRLF, and use javascript to assign carriage return+line feed (off top of my head it's something like chr(13)+chr(10), but look it up to make sure. Then in your File Access node add <!CRLF> to the input string. =
    Thanks for using the LawsonGuru.com forums!
    John
    Erikw
    Private
    Private
    Basic Member
    (10 points)
    Basic Member
    Posts:4


    Send Message:

    --
    07/21/2010 3:16 AM
    Hi Brad,
    To add a carriage return+ line feed to the end of every record you can create a variable with "\r\n".
    But just pushing the enter button at the end of the record also seems to work.

    Brad Schauer
    Private
    Private
    Veteran Member
    (222 points)
    Veteran Member
    Posts:76


    Send Message:

    --
    07/21/2010 10:29 AM
    John and Erik,

    Thanks for the assitance. I ended up creating a variable in the start node.

    String vCRLF="\n"

    And then I used the variable in my FileAccess node.

    Seems to be working.

    Thanks!
    Brad
    Ward Mitchell
    Veteran Member
    (205 points)
    Veteran Member
    Posts:81


    Send Message:

    --
    08/19/2010 10:04 AM
    Brad and friends,

    Hey I am doing the same thing I have the data being written out to a csv file. But now I am trying to attach the csv file I just created in the flow and then send. Do you have any ideas on how to attach? I attached the _returnMessage of my FileAccess node that I created, but all I get is the following message in my email:

    These attachments would be sent in run mode:
    <!CreateCSVFile_returnMessage>

    I am running this locally on my machine to test, is there something I am missing?
    David Williams
    Private
    Private
    Veteran Member
    (3064 points)
    Veteran Member
    Posts:1032


    Send Message:

    --
    08/19/2010 10:14 AM
    If the filename will always be the same what you can do is take a copy of the file and upload it into ProcessFlow to be used as an email attachment and then have your output overwrite that file everytime you run your job. The flow will pick up the latest version to send in the email as an attached file.
    David Williams
    Ward Mitchell
    Veteran Member
    (205 points)
    Veteran Member
    Posts:81


    Send Message:

    --
    08/19/2010 10:22 AM
    [quote]
    Posted By ConsultDavidW on 08/19/2010 10:14 AM
    If the filename will always be the same what you can do is take a copy of the file and upload it into ProcessFlow to be used as an email attachment and then have your output overwrite that file everytime you run your job. The flow will pick up the latest version to send in the email as an attached file.
    [/quote]

    What type of node do I use to upload the file into the flow? The FileAccess. Some history for you, the file will change 12 times of year (so monthly) and the name will have the month numbe rin it, but it can be replaced many times a month on demand by users.

    TBonney
    Private
    Private
    Veteran Member
    (585 points)
    Veteran Member
    Posts:249


    Send Message:

    --
    08/19/2010 10:32 AM
    Ward,
    If you have PFI, you can use the File Access node to write the file. Specify the file path and name (path should be %LAWDIR%\bpm\emailattachments). Then, use an email node to attach the file, by specifying that same directory from which to retrieve the deisred attachment file.
    David Williams
    Private
    Private
    Veteran Member
    (3064 points)
    Veteran Member
    Posts:1032


    Send Message:

    --
    08/19/2010 10:33 AM
    Within ProcessFlow Designer, select Tools and then Upload Document to Server. Select the file you want and it will then be available to "attach" within your email node. I don't remember off hand where the file is stored in Lawson but once you know the path you can make that your output and overwrite the existing file monthly.
    David Williams
    David Williams
    Private
    Private
    Veteran Member
    (3064 points)
    Veteran Member
    Posts:1032


    Send Message:

    --
    08/19/2010 10:37 AM
    I wasn't sure if the Email attachment would recognize the file unless you uploaded it first, but give it a try.
    David Williams
    Ward Mitchell
    Veteran Member
    (205 points)
    Veteran Member
    Posts:81


    Send Message:

    --
    08/19/2010 10:40 AM
    Thanks folks, that might be my problem - that I am not running on the server. Our admin cannot seem to turn the RMI services on so that I can upload / download from server, so I am stuck and the present moment and wont be able to figure this out. Let me see if I can get this opened up so I can try to figure this out with the FileAccess node.

    ConsultDavidW: In my email node I have in my flow, I already am pointed to the FileAccess node; but I have not uploaded said file to server for the flow to detect. So I am wondering the same thing.
    Ward Mitchell
    Veteran Member
    (205 points)
    Veteran Member
    Posts:81


    Send Message:

    --
    08/19/2010 10:46 AM
    On second thought though on this as well, the only reason for attaching the file would be for folks to download the file. I can just as easy write to the location already and be done with it and not email it. This might be a better way to go about it. I am still going to work with our admin and figure out the RMI issue though. So I think I might call it good.

    Thank you all for your prompt help today with this!!!!!
    Lyle Zaehringer
    Senior Developer
    Private
    Basic Member
    (20 points)
    Basic Member
    Posts:8


    Send Message:

    --
    03/15/2016 4:31 PM

    Since the suggestions here seem to involve overwriting data to the same file by every Work Unit for a process, isn't there danger that another Work Unit for the same process running concurrently could pickup the file that was written by, and therefore intended to be picked up by, a different concurrent Work Unit? Or vice versa, that another Work Unit for the same process running concurrently could write to the file before the other process had picked up the file it had written to, resulting in data being overwritten before it was picked up? Shouldn't there be some safeguard to ensure that the file written by the FileAccess node for that Work Unit was the same file picked up by only that same Work Unit? If so, how can this be done when the Email Node > Browse only allows you to specify the location of the attached file.

    In other words, process A has Work Unit A and writes to file A, process A has Work Unit B writes to file A. Both Work Units get kicked off at the same time and try to write to file A, then try to pick up file A.


    I am similarly trying to attach a CSV file (of records currently collected by a MsgBuilder node) to an email.

    Thanks very much!
    Lyle
    Woozy
    Private
    Private
    Veteran Member
    (3487 points)
    Veteran Member
    Posts:707


    Send Message:

    --
    03/15/2016 5:33 PM
    Hi Lyle - that is certainly a concern in cases where the flow isn't scheduled or otherwise managed. When I have situations like this, I will typically append a datestamp, datetimestamp, or workunit number to the end of the filename that is written. This ensures that every file is unique. If the filename and path is stored in a variable, then you can use that variable as the attachment name in the email node.

    Good Luck!
    Kelly Meade
    J. R. Simplot Company
    Boise, ID
    Lyle Zaehringer
    Senior Developer
    Private
    Basic Member
    (20 points)
    Basic Member
    Posts:8


    Send Message:

    --
    03/16/2016 11:25 AM
    Perfect. I think I'll use both datetime and work unit. Thanks very much, Woozy!

    Now I just need to figure out what the email node does when there is no file to pick up (the file contains errors and often there are none).
    Woozy
    Private
    Private
    Veteran Member
    (3487 points)
    Veteran Member
    Posts:707


    Send Message:

    --
    03/16/2016 11:47 AM
    Hi Lyle - It will either send anyway (without the attachment) or it will error. I can't remember which.

    In order to avoid this I usually do one of two things - either I add a counter that increments with each "OK" record, or I determine if the final file length is greater than zero and set a flag. Then I evaluate the counter or flag and branch to the email if appropriate. Good Luck!
    Kelly Meade
    J. R. Simplot Company
    Boise, ID
    Lyle Zaehringer
    Senior Developer
    Private
    Basic Member
    (20 points)
    Basic Member
    Posts:8


    Send Message:

    --
    03/17/2016 4:07 PM

    It errors. So I've done as you suggested, which actually involves two email nodes, one for a file that had no errors (and therefore no attachment) and one for a file that had errors (and therefore picks up the error file attachment). Once again, thanks very much for your help!

     

     

    Woozy
    Private
    Private
    Veteran Member
    (3487 points)
    Veteran Member
    Posts:707


    Send Message:

    --
    03/17/2016 4:13 PM
    Hi Lyle - what exactly errors? Can you attach your log file?
    Kelly Meade
    J. R. Simplot Company
    Boise, ID
    Woozy
    Private
    Private
    Veteran Member
    (3487 points)
    Veteran Member
    Posts:707


    Send Message:

    --
    03/17/2016 4:14 PM
    Oh - wait. I guess it's doing what you meant it to do. I thought you meant it was throwing errors.

    Sorry for the confusion.
    Kelly Meade
    J. R. Simplot Company
    Boise, ID
    Lyle Zaehringer
    Senior Developer
    Private
    Basic Member
    (20 points)
    Basic Member
    Posts:8


    Send Message:

    --
    03/17/2016 4:39 PM
    I've got it working now. I was responding to you being unsure whether an email node would error or not when a file that the email node was expecting to attach did not exist. I've confirmed the node errors however the process completes. But most importantly for me, the email node does not generate an email.

    Here is the error:


    Email Email8310: Email failed with error javax.mail.SendFailedException: Attachment: \\win1362\data\General_Ledger\ErrorEmails\HCHB_GL_Errors_DateTime20165117125147_WorkUnit366.csv does not exist.
    at com.lawson.bpm.processflow.workFlow.flowGraph.FgaEmail.sendMailDirect(FgaEmail.java:681)
    at com.lawson.bpm.processflow.workFlow.flowGraph.FgaEmail.startActivity(FgaEmail.java:518)
    at com.lawson.bpm.processflow.workFlow.flowGraph.FgActivity.execute(FgActivity.java:845)
    at com.lawson.bpm.processflow.workFlow.flowGraph.FgProcess.run(FgProcess.java:2182)
    at com.lawson.bpm.eprocessserver.grid.ExecuteFlowImpl.executeFlow(ExecuteFlowImpl.java:374)
    at com.lawson.bpm.eprocessserver.grid.ExecuteFlowImpl.executeFlow(ExecuteFlowImpl.java:137)
    at com.lawson.bpm.eprocessserver.grid.ExecuteFlowImpl.executeFlow(ExecuteFlowImpl.java:123)
    at com.lawson.bpm.eprocessserver.ProcessFlowEngine.execute(ProcessFlowEngine.java:150)
    at com.lawson.bpm.eprocessserver.ProcessFlowEngine.startProcessAt(ProcessFlowEngine.java:101)
    at com.lawson.bpm.eprocessserver.KBConnection.startProcess(KBConnection.java:125)
    at com.lawson.bpm.eprocessserver.KBConnection.execute(KBConnection.java:95)
    at com.lawson.bpm.eprocessserver.KBConnection.run(KBConnection.java:75)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    at java.lang.Thread.run(Thread.java:745)


    I am constructing the error file name regardless of whether it is needed or not, which is why the email node references a specific file name.

    Thanks for checking in, Kelly!
    Orlando Gray
    Analyst
    Private
    Advanced Member
    (71 points)
    Advanced Member
    Posts:27


    Send Message:

    --
    02/13/2018 5:37 PM
    Thanks all, this thread was helpful!
    You are not authorized to post a reply.