We receive an Excel Spreadsheet from one of our Customers that Service Connect uses to enter Sales Orders. One of the Columns is mapped to a Character field on OrderDtl and it seems to be capturing, what I can only assume are, formatting characters (or as I call them Evil Goblins ) from Excel???
I’m not an expert in Excel by any means but I’ve tried formatting the column to Text, tried unlocking the cell and clearing formats. But these little monsters keep showing up. Of course if I retype the text it comes through just fine. Is there a way to programmatically remove these hidden formatting characters in Excel before sending the file to Service Connect so I don’t have a bunch of formatting characters showing up in my Epicor Character field?
Need a BPM to “clean up” the imported text. The hardest part will be deciding what to do with non-standard ASCII or non-printable characters.
The easiest (but not necessarily the best) is to remove any character outside of Dec32(space character) through DEC126(tilde). But then you could lose characters like Trademark, Servicemark, degrees symbol, etc…
i had an issue with this before too.
It was caused by a work flow that wrote to Epicor and the data was in HTML from the source so it was putting strange characters into Epicor part descriptions.
My search results say that the 202D is a Left-To-Right Override and 202C is a Pop Directional Formatting character.
Ok, so now that I can see the hidden Unicode characters I guess the next step is to try and develop a macro to search for and eliminate them…? I’ll have to check with my Excel guru on that one.
I wanted to mention that I tried the Clean() function in Excel but that did not work, because it only removes “the first 32 non-printing characters in the 7-bit ASCII code”…Come on! Why not ALL of them!?
@gpayne, I did try saving the Excel file to CSV and the hidden characters showed up as a double-quote in the CSV file. So that was another way to see that there was something there.
Thank you all for your thoughts and ideas. Hopefully we can develop a macro that will clean-up the data in these files before sending them to Service Connect.