Send query results to delimited text file (*.txt)

Sort:
You are not authorized to post a reply.
Author
Messages
HDAustin13
Advanced Member
Posts: 31
Advanced Member
    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
    Veteran Member
    Posts: 468
    Veteran Member
      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
      Veteran Member
      Posts: 74
      Veteran Member
        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
        Veteran Member
        Posts: 1127
        Veteran Member
          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
          Advanced Member
          Posts: 31
          Advanced Member
            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
            Veteran Member
            Posts: 1127
            Veteran Member
              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
              Advanced Member
              Posts: 31
              Advanced Member
                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
                Veteran Member
                Posts: 1127
                Veteran Member
                  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
                  Veteran Member
                  Posts: 1127
                  Veteran Member

                    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.