web stats
CSV File Reader - Mirth Community

Go Back   Mirth Community > Mirth Connect > Support

Reply
 
Thread Tools Display Modes
  #1  
Old 05-14-2012, 11:14 AM
GregD GregD is offline
Mirth Guru
 
Join Date: May 2010
Posts: 234
GregD is on a distinguished road
Default CSV File Reader

I'm trying to import a CSV file and use each row for a separate outbound HL7 message. In the channel there is an inbound message template for the CSV in the source transformer, and there is an outbound HL7 template in the outbound destination transformer. The sample channel is a very slimmed downed version of a channel I'm trying to build. I receive an error and I'm not sure what to do with it.

I have tried many variations of /n/r, /n, /r, /r/n for the record delimiter, thinking that might be the problem. I tried changing my CSV template to a single row, thinking that might be the problem. In the real channel, which is an FTP reader, I tried checking 'Process batch files'. I tried sending in a CSV file with just 1 row. No matter what I do I keep getting the same error message. I'm not sure what to try next.

The sample message I send in has 3 rows, so it seems to be processing 3 rows. The phone number is in Column8, but I'm just not sure why it is not processing the rows correctly. I'm not sure what to do with "Element type "column8Phone" must be followed by either attribute specifications, ">" or "/>". " Why isn't it just 'column8'

I have attached a sample channel and a test CSV file.

Greg
Attached Files
File Type: xml CSVTest.xml (8.2 KB, 491 views)
File Type: txt CSVError.txt (15.4 KB, 199 views)

Last edited by GregD; 05-14-2012 at 11:21 AM. Reason: Add attachment
Reply With Quote
  #2  
Old 05-14-2012, 11:22 AM
GregD GregD is offline
Mirth Guru
 
Join Date: May 2010
Posts: 234
GregD is on a distinguished road
Default

Sample CSV
Attached Files
File Type: txt Sample.txt (267 Bytes, 510 views)
Reply With Quote
  #3  
Old 05-14-2012, 12:52 PM
narupley's Avatar
narupley narupley is online now
Mirth Employee
 
Join Date: Oct 2010
Posts: 7,126
narupley is on a distinguished road
Default

Basically to receive discrete messages for every CSV row, you'll need to set the record delimiter (\n should be fine, and Ignore Carriage Returns couldn't hurt) and check Split Batch by Record. Then on your File Reader connector, make sure to select Yes for Process Batch Files. Finally, you'll need to test it by actually picking up a file, rather than by manually sending it a message.

Now, regarding that error you're seeing. I suspect that it has to do with the fact that you're converting XML to HL7 v2.x in your source connector, but you never actually do any transformation, so the raw message that gets passed to the destination connectors is just a malformed HL7 serialization attempt. Go ahead and look at the encoded message for your Source connector in the Channel Messages screen to see what I mean. You have a couple of options... if you eventually plan to "flesh out" the channel by actually transforming the delimited text into HL7, then you'll need to either do that in the Source connector, or do it in the destination connector and change the Source outbound protocol to Delimited Text. If instead you don't plan on doing that transformation (it looks like right now you just have an entire message in the outbound template of your destination connector), then you can just put some dummy message in your Source outbound template, or just change the Source outbound protocol to Delimited Text.
Reply With Quote
  #4  
Old 05-14-2012, 02:38 PM
GregD GregD is offline
Mirth Guru
 
Join Date: May 2010
Posts: 234
GregD is on a distinguished road
Default

Quote:
Originally Posted by narupley View Post
Basically to receive discrete messages for every CSV row, you'll need to set the record delimiter (\n should be fine, and Ignore Carriage Returns couldn't hurt) and check Split Batch by Record.
This is how the sample channel was set up
Quote:
Originally Posted by narupley View Post
Then on your File Reader connector, make sure to select Yes for Process Batch Files.
I did that
Quote:
Originally Posted by narupley View Post
Finally, you'll need to test it by actually picking up a file, rather than by manually sending it a message.
Ugh! This is very annoying.

Quote:
Originally Posted by narupley View Post
Now, regarding that error you're seeing. I suspect that it has to do with the fact that you're converting XML to HL7 v2.x in your source connector, but you never actually do any transformation, so the raw message that gets passed to the destination connectors is just a malformed HL7 serialization attempt.
I did have a single transformer step in both the source and destination.

Quote:
Originally Posted by narupley View Post
Go ahead and look at the encoded message for your Source connector in the Channel Messages screen to see what I mean. You have a couple of options... if you eventually plan to "flesh out" the channel by actually transforming the delimited text into HL7, then you'll need to either do that in the Source connector, or do it in the destination connector and change the Source outbound protocol to Delimited Text. If instead you don't plan on doing that transformation (it looks like right now you just have an entire message in the outbound template of your destination connector), then you can just put some dummy message in your Source outbound template, or just change the Source outbound protocol to Delimited Text.
I was using the tmp object to rebuild the HL7 message in the outbound template. The error was happening before it ever got them.

By posting the message to the FTP site and letting Mirth grab it I am no longer getting the error. However, it is now only processing the first row of the message and not treating every row as different message. Here's what I have:

Summary Tab, set data types, inbound and outbound are delimited
Record Delimiter /n
Split Batch Records checked
Ignore CR checked
Split Batch by delimiter /n

Source connecter
Process Batch Files checked

In the Source Transformer I map 3 variables. My inbound template has 3 rows. When I drag an element to the transformer mapper it wants to add the array element (e.g msg['row'][0]['column1'].toString()) Does that seem correct?

For the destination I map the variables to the temp object (e.g. tmp['PID']['PID.5']['PID.5.1']) I drop a file with 25 rows on the FTP server and I get one message out with information only from the first row of the file.

Greg
Reply With Quote
  #5  
Old 05-14-2012, 03:41 PM
GregD GregD is offline
Mirth Guru
 
Join Date: May 2010
Posts: 234
GregD is on a distinguished road
Default

Ok, so it seems the original problem was due to the fact that I was manually sending the file with the 'Send Message' menu option. This next problem is because Mirth is seeing the file as one long record. It is not breaking the message apart in rows.

I wrote a chunk of code and determined that the message uses ASCII 13 as a delimiter. On the Summary Tab, Set Data Types, Properties I have \r as both the Record Delimiter and the Split Batch Delimiter and Mirth is not splitting on rows. I also tried it with \r in just the Record Delimiter and nothing in the Split Batch Delimiter. Same thing. On the Source tab l I have Process Batch Files checked.

Is there anything else I need to do to get Mirth to split these messages? What am I missing?

Greg
Reply With Quote
  #6  
Old 05-14-2012, 04:54 PM
GregD GregD is offline
Mirth Guru
 
Join Date: May 2010
Posts: 234
GregD is on a distinguished road
Default

Does Mirth not recognize just ASCII 13 as a delimiter? If I open the file and manually change ASCII 13 to ASCII 13 & 10 and change the delimiter in the data type properties to \n\r Mirth will process the files.

I've wasted so much time on this today I could have written another channel which would convert these files to a CSV format which Mirth can read and then send them to the channel I am writing.

Maybe a deployment script? So I don't spend 3 more hours trying to figure out the proper way to escape an escaped character in Mirth (I fell down that rabbit hole once before) what would be a script that would convert ASCII 13 to 13 & 10, so I can finish what was supposed to be a few hours work today?

Greg
Reply With Quote
  #7  
Old 05-15-2012, 01:19 AM
wello007 wello007 is offline
OBX.3 Kenobi
 
Join Date: Dec 2007
Posts: 160
wello007 is an unknown quantity at this point
Default

Code:
var newMessage = '';
newMessage = message.replace(/\r/g,'\r\n');
return newMessage;
Reply With Quote
  #8  
Old 05-15-2012, 05:47 AM
narupley's Avatar
narupley narupley is online now
Mirth Employee
 
Join Date: Oct 2010
Posts: 7,126
narupley is on a distinguished road
Default

Mkay, the sample.txt file that you provided had CRLFs instead of just CRs, so I based my advice on that. If you want to split on CRs, then you need a Record Delimiter of \r and Ignore Carriage Returns unchecked.

Mirth only pays attention to the first character in the Record Delimiter field, so when you set it to \n\r and feed it CRLFs (with Ignore Carriage Returns checked), then it will process the files just fine.

You can set up a preprocessor to modify messages so that they have CRLFs instead of CRs, but that will not help you, because the preprocessor runs after the Source message receiver has parsed out the batch file. To help clarify, I've attached a couple screenshots of what the protocol settings should look like. Note that this is assuming that the files you're picking up have CRs in them; if you try to pick up files with CRLFs you'll get some unexpected results (intermediate messages beginning with line feeds, and then an extra message at the end consisting of only a line feed). Of course, you can make it work for both cases by splitting the batch with some JavaScript:

Code:
var i = -1, str = '';
while ((i = reader.read()) != -1) {
	str += String.fromCharCode(i);
	if (i == 13) {
		reader.mark(1);
		var j = reader.read();
		if (j == 10)
			str += String.fromCharCode(i);
		else
			reader.reset();
		break;
	}
	else if (i == 10)
		break;
}
return str;
In any case, just remember that a lot of little pieces have to come together to make it work the way you want it to. If you change one of the pieces and not the others accordingly, then all bets are off.

Just one final comment: It looks like you're putting some variables in the channel map in the source connector, and then mapping those variables back into the outbound message of the destination connector. Why not skip the variables, let the message pass through to the destination as delimited text, and just map the values from msg to tmp directly?
Attached Images
File Type: png Capture1.PNG (23.5 KB, 269 views)
File Type: png Capture2.PNG (5.6 KB, 174 views)
Reply With Quote
  #9  
Old 05-15-2012, 08:35 AM
GregD GregD is offline
Mirth Guru
 
Join Date: May 2010
Posts: 234
GregD is on a distinguished road
Default

I will play around with this again, but I obviously have some switch set incorrectly, a slash wrong, or check box checked wrong. I had an Access db open so I wrote the chunk of code below for a sanity check. The code below splits the file correctly, so this file has ASCII 13 as a delimiter and I can't seem to get Mirth to parse it. I'm not saying this is Mirth, but....

Quote:
Dim iFreeFile As Integer
Dim sFileParts() As String
Dim sFileChunck As String
Dim k As Integer

iFreeFile = FreeFile
Open "H:\20120510100347734.csv" For Input As iFreeFile
sFileChunck = Input(LOF(iFreeFile), #iFreeFile)
sFileParts = Split(sFileChunck, vbCr)
Close

For k = 1 To UBound(sFileParts)
Debug.Print sFileParts(k)
Next
Reply With Quote
  #10  
Old 05-15-2012, 08:36 AM
narupley's Avatar
narupley narupley is online now
Mirth Employee
 
Join Date: Oct 2010
Posts: 7,126
narupley is on a distinguished road
Default

Hmm, can you post the latest version of your channel for us?
Reply With Quote
Reply

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 10:45 AM.


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