A client emailed me regarding the following error they were receiving with a Dynamics GP 2010 eConnect SOP Invoice integration.
eConnectException: '.', hexadecimal value 0x00, is an invalid character. Line 5, position -248.
Based on the error message, it appeared that there was some type of invalid character in the CSV source data file. But when we looked at the CSV file in a text editor, everything looked fine.
So I ran the integration in debug mode in Visual Studio, and when the eConnect insert failed, I checked the XML data that was submitted. The results were interesting.
Trans Number: 205112482
Borrower: THOMAS JEFFERSON
Title Company: & #x0 ;
Commitment Number: & #x0 ;
File Name: & #x0 ;
Lien Type: FIRST
Loan Type: CONVENTIONAL
(I've had to add spaces to the hex string so that Blogger doesn't strip them out)
The text above is a long comment string that is being inserted with the SOP invoice header. It seems that there is an odd hex character in three of the field values.
I had to look up that hex character, and learned that it is the hex value of the null character. Apparently eConnect is not a fan of the null character, so that is causing the error.
This is all that I see when I open the file in UltraEdit and view in text mode. Looks fine.
But if I view the file in the UltraEdit HEX mode, I see this.
Notice the "00" values in the data on the left, and the period in the representation on the right. That's our problem.
So how do we deal with these rogue null characters? Ideally, the source data file would be corrected so that they are not inserted in the file in the first place. But in case they do show up in the data file again, it's fairly easy to remove them using Regular Expressions.
So I added this simple function to strip the null character from the field value.
Once I applied the RemoveNull function to the invoice comment text, the invoices imported fine and the error went away.
I've never encountered the "null" character before, but fortunately it was a pretty easy fix.