Excel Formatting Characters showing in Epicor Field

Hello,

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 :ghost::space_invader::alien::smiling_imp:) 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?

Thank you for any help you can provide!
-Heather

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…

1 Like

if you save the excel to csv and then process the csv would it lose all of the formatting junk?

1 Like

Can you do a find/replace in excel before they get transferred? If you can, you can probably make a macro to do that for you.

1 Like

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.

1 Like

FWIW - I use a tool called Fee Clipboard Viewer 3.0 to examine text for irregularities.

For example in the text below (copied from a post above)

shows that the quotation marks are actually non-standard ASCII


(highlighting mine)

Looking at the clipboard from various formats, can reveal more info.

When i examaine the same text as Unicode, it shows different values for those fancy quotation marks.

image

1 Like

Pretty Cool! Thank you @ckrusen for recommending this program.

This is what I found…

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.

Could you use Regex?

we use it in pour BPM to assure user does not create bad partnums,

foreach(var part in ttPart)
{
	if(!new System.Text.RegularExpressions.Regex("^[a-zA-Z0-9_-]*$").IsMatch(part.PartNum))
	{
		return true;
	}
}

return false;

Then detect a “bad” char et replace it?

Pierre

2 Likes