PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 01/10/2019 9:11 AM by  David Williams
Send query results to delimited text file (*.txt)
 8 Replies
Sort:
You are not authorized to post a reply.
Author Messages
HDAustin13
IT programmer
Austin ISD
Basic Member
(29 points)
Basic Member
Posts:15


Send Message:

--
09/10/2018 10:59 AM
    Hello All,

    I am working on a project that requires an IPA to send query results to a format of delimited text file (*.txt) which use special characters like (|,~, ^) to separate each record.

    Can any one please advise on how it can be done in IPA (IPD Designer CU70)?

    Thanks in advance,
    Han
    Ragu Raghavan
    Private
    Private
    Veteran Member
    (924 points)
    Veteran Member
    Posts:322


    Send Message:

    --
    09/10/2018 11:22 AM
    Maybe use the MSGBUILDER node to build the record and FILEACCESS to write to a file?
    For example if results from query are QRY1_EMPLOYEE, QR1_FICA_NBR, QR1_LAST_NAME
    in the MSGBUILDER node,
    <!--QRY1_EMPLOYEE-->|<!--QR1_FICA_NBR-->|<!--QR1_LAST_NAME-->
    This will create a pipe delimited line, which you can accumulate and then write to a file
    Joan Herzfeldt
    System Analyst
    Genesis Health System
    Veteran Member
    (147 points)
    Veteran Member
    Posts:55


    Send Message:

    --
    09/10/2018 11:38 AM
    I am working on the same thing. Normally I would use a message builder (as suggested) But in this case I only need to create the file and FTP it to the vendor AND there is A LOT of data. Since I'm most comfortable using SQL to create views, I created a view that contains all of the fields I need, formatted the way they need to be in the file. Essentially I'm using a syscmd node in IPA to create the file on the landmark server and then an ftp node to send it. The syscmd opens a cmd window and uses bcp to create my file. Unfortunately I do not have a lot of information on bcp as my dba set it up and told me the basics.
    I'm sure others do it differently, but this work for me, for now.
    my syscmd node:
    bcp "select distinct HeaderRec as mldata from ghs.viewname UNION ALL select ML_EligData as mldata from ghs.viewname" queryout -c -T -S -d LAWSON
    David Williams
    Private
    Private
    Veteran Member
    (3088 points)
    Veteran Member
    Posts:1040


    Send Message:

    --
    09/10/2018 12:25 PM
    You can create a SQL Query and set it to ‘Generate CSV from resultset’ and then use an Assign JavaScript to replace the comma to your specific special character.
    David Williams
    HDAustin13
    IT programmer
    Austin ISD
    Basic Member
    (29 points)
    Basic Member
    Posts:15


    Send Message:

    --
    01/10/2019 8:36 AM
    Hello David! Thank you for your response. Could you please give me a sample on how to use an Assign JavaScript to replace the comma with the pipe.  I am trying to do something like this (~~Field1|field2|field3~~) in the File then execute it with SysCom but it is not spooling the output correctly. Can you please advise? Thank you.



    David Williams
    Private
    Private
    Veteran Member
    (3088 points)
    Veteran Member
    Posts:1040


    Send Message:

    --
    01/10/2019 8:48 AM
    Once you have your SQL results, you will have data that is line separated records and comma delimited fields.
    In JavaScript, you would use the replace option to switch your commas to pipe var.replace(/,/g,"|")
    David Williams
    HDAustin13
    IT programmer
    Austin ISD
    Basic Member
    (29 points)
    Basic Member
    Posts:15


    Send Message:

    --
    01/10/2019 8:56 AM
    I am getting there with the sql results and trying to replace the comma to the pipe '|'. I have done in an Assign JavaScript for a line but not for a file of 2000+ line of records. Can you please advise more on it? Thanks.
    David Williams
    Private
    Private
    Veteran Member
    (3088 points)
    Veteran Member
    Posts:1040


    Send Message:

    --
    01/10/2019 9:06 AM
    If you assign your SQL results to a variable, you can then treat it as an array (you don't have to declare it as an array)
    s=SqlResults.split("\n")
    for (i=0;i {
    x=s
    x=x.replace(/,/g,"|")
    output=output+"~~"+x+"~~\n"
    }
    David Williams
    David Williams
    Private
    Private
    Veteran Member
    (3088 points)
    Veteran Member
    Posts:1040


    Send Message:

    --
    01/10/2019 9:11 AM

    Email me and I'll send you the script - I can't figure out how to get it to display correctly.

    david@consultdavidw.com

    David Williams
    You are not authorized to post a reply.