web stats
Database table to CSV file - Mirth Community

Go Back   Mirth Community > Mirth Connect > Support

Reply
 
Thread Tools Display Modes
  #1  
Old 04-10-2015, 11:00 AM
Rubicon Rubicon is offline
OBX.2 Kenobi
 
Join Date: Feb 2013
Posts: 98
Rubicon is on a distinguished road
Red face 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
Reply With Quote
  #2  
Old 04-13-2015, 07:52 AM
upstart33 upstart33 is offline
Mirth Guru
 
Join Date: Dec 2010
Location: Chicago, IL.
Posts: 459
upstart33 is on a distinguished road
Default

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}
Reply With Quote
  #3  
Old 04-13-2015, 09:49 AM
Rubicon Rubicon is offline
OBX.2 Kenobi
 
Join Date: Feb 2013
Posts: 98
Rubicon is on a distinguished road
Default

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.
Reply With Quote
  #4  
Old 04-13-2015, 10:08 AM
Rubicon Rubicon is offline
OBX.2 Kenobi
 
Join Date: Feb 2013
Posts: 98
Rubicon is on a distinguished road
Default 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
Reply With Quote
  #5  
Old 04-13-2015, 10:30 AM
narupley's Avatar
narupley narupley is online now
Mirth Employee
 
Join Date: Oct 2010
Posts: 7,098
narupley is on a distinguished road
Default

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.
Reply With Quote
  #6  
Old 04-13-2015, 10:30 AM
Rubicon Rubicon is offline
OBX.2 Kenobi
 
Join Date: Feb 2013
Posts: 98
Rubicon is on a distinguished road
Default

Thank you!!
Reply With Quote
  #7  
Old 04-13-2015, 11:20 AM
upstart33 upstart33 is offline
Mirth Guru
 
Join Date: Dec 2010
Location: Chicago, IL.
Posts: 459
upstart33 is on a distinguished road
Default

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.
Reply With Quote
  #8  
Old 04-14-2015, 08:01 AM
Rubicon Rubicon is offline
OBX.2 Kenobi
 
Join Date: Feb 2013
Posts: 98
Rubicon is on a distinguished road
Default

Quote:
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?
Reply With Quote
  #9  
Old 04-14-2015, 11:39 AM
rdejournett rdejournett is offline
OBX.2 Kenobi
 
Join Date: Jan 2013
Posts: 98
rdejournett is on a distinguished road
Default

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
File Type: xml Monthly Finance Report.xml (48.8 KB, 335 views)
Reply With Quote
Reply

Tags
csv, database reader, output

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -8. The time now is 08:05 AM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2019, vBulletin Solutions, Inc.
Mirth Corporation