Announcement

Collapse
No announcement yet.

CSV File Reader

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

  • 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
    Last edited by GregD; 05-14-2012, 10:21 AM. Reason: Add attachment

  • #2
    Sample CSV
    Attached Files

    Comment


    • #3
      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.
      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


      • #4
        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
        Originally posted by narupley View Post
        Then on your File Reader connector, make sure to select Yes for Process Batch Files.
        I did that
        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.

        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.

        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

        Comment


        • #5
          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

          Comment


          • #6
            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

            Comment


            • #7
              Code:
              var newMessage = '';
              newMessage = message.replace(/\r/g,'\r\n');
              return newMessage;
              WD

              Comment


              • #8
                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 Files
                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


                • #9
                  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....

                  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

                  Comment


                  • #10
                    Hmm, can you post the latest version of your channel for us?
                    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


                    • #11
                      Having /r as both delimiters and un-checking 'Ignore Carriage Returns' was the key.

                      Greg
                      Last edited by GregD; 05-15-2012, 09:00 AM. Reason: sdc

                      Comment

                      Working...
                      X