Announcement

Collapse
No announcement yet.

Database table to CSV file

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Database table to CSV file

    Hello,
    I have a task of generating a CSV file from an SQL database table. We want to email this report to various people. And I'm not sure on to perform this.

    Does anyone have any channels that they could share or point me in the right direction?

    I am using MC 3.2 w/SQL Database.

    Thank you!!!

    Rubi

  • #2
    Do something like this:

    - Select the fields from your SQL Database
    - Build your body variable
    Code:
       var csv = ""
       var delimiter = ""
       var row = ""
    
    ....
    
        row = field1 + delimiter + field2 + delimiter + field3 + '\r\n';
        csv = csv + row
    
        channelMap.put('csv', csv);
    - Build your header with the field names
    Code:
       var csvhead= "";
       var delimiterhead = ",";
       var rowhead = "";
    
       rowhead = 'First Name' + delimiterhead  + 'Last Name' + delimiterhead + 'Paitent MRN' + '\r\n';
    
        csvhead =  csvhead + rowhead
        channelMap.put('csvhead', csvhead);
    - Then, in your Destination Template you will use
    Code:
    ${csvhead}${csv}

    Comment


    • #3
      Thanks Rob!!

      But I'm still confused? Would I need to create two channels? One for file writer and the other to pull that file and e-mail it?

      Rubi.

      Comment


      • #4
        Need to Pull DB table and create a file

        I need to pull data from a Database table and create a CVS file or a delimited ASCII file, this information will then need to be sent to another file directory so I can pull that info and send it out via e-mail.... Once this information has been "sent to the said directory" I will then need to pull file from that directory and send via e-mail.

        Any input is most gratefully appreciated!!! ( Apologize if I didn't make myself clear)


        Thanks,
        Rubi

        Comment


        • #5
          Merged your other thread since it's basically the same issue.
          Step 1: JAVA CACHE...DID YOU CLEAR ...wait, ding dong the witch is dead?

          Nicholas Rupley
          Work: 949-237-6069
          Always include what Mirth Connect version you're working with. Also include (if applicable) the code you're using and full stacktraces for errors (use CODE tags). Posting your entire channel is helpful as well; make sure to scrub any PHI/passwords first.


          - How do I foo?
          - You just bar.

          Comment


          • #6
            Thank you!!

            Comment


            • #7
              Yes, I think two channels will have to be used. One to create the file and another to poll the directory and send it via email.

              Comment


              • #8
                Originally posted by upstart33 View Post
                Yes, I think two channels will have to be used. One to create the file and another to poll the directory and send it via email.
                Would you have a sample channel that I might be able to use?

                Comment


                • #9
                  I do. This does a DB call from a postgres database and sends it to a user as a CSV file via email.
                  Attached Files

                  Comment


                  • #10
                    sqlserver db reader to csv/xlsx file writer

                    hello, i am new to mirth i have one task that source will be sqlserver reader and destination is csv/xlsx file writer.

                    let me explain my issue clearly i use in source section javascript:
                    var dbConn;

                    try {
                    dbConn = DatabaseConnectionFactory.createDatabaseConnection ('net.sourceforge.jtds.jdbc.Driver','jdbc:jtds:sql server://host:1433/Training','user','pswrd');
                    var SQL='SELECT * FROM tblSample';
                    var result=dbConn.executeCachedQuery(SQL);
                    // You may access this result below with $('column_name')
                    return result;
                    } finally {
                    if (dbConn) {
                    dbConn.close();
                    }
                    }

                    in destination template i use $(row1),$(row2)
                    note: row1, row2 are sample table column name.. once the channel is deployed after successfull processing i dont have an errors but when i open that csv file it was showing "text text" but actual data is "10293,shaik" ..i have not created any transformer .

                    please help me out. thanks in advance

                    Comment


                    • #11
                      Perhaps you have text text in your outbound message template?
                      Best,

                      Kirby

                      Mirth Certified|Epic Bridges Certified|Cloverleaf Level 2 Certified

                      Appliance Version 3.11.4
                      Mirth Connect Version 3.8.0
                      Java Version 1.6.0_45-b06
                      Java (64 bit) Version 1.6.0_45-b06
                      Java 7 (64 bit) Version 1.7.0_151-b15
                      Java 8 (64 bit) Version 1.8.0_181-b13
                      PostgreSQL Version 9.6.8

                      Comment


                      • #12
                        @shaik

                        I recommend viewing this thread (don't use the first solution) https://www.mirthcorp.com/community/...d.php?t=217724

                        I also recommend with the example you have shown to use a db reader in sql mode. I don't think you're going to gain anything from being in javascript mode here.

                        This transformation cannot happen without using a transformer.

                        Comment

                        Working...
                        X