Find and Replace Special characters - Header

[Meghanyeager19]

Hi all! I am fairly new to Alteryx and I am looking to create a workflow to remove the special characters in the headers and replace them with underscore "_" without having to choose each field I want to complete the action above. I will have 10-20 different files that will have 3-8 different header fields that have a special character in them. I was hoping there is a way to create one workflow that would remove and replace without having to create 20 different workflows for each file that needs configuring. 

Thank you in advance!! Love this community - I learn so much from these discussion boards. 

[Ladarthure]

Hi @Meghanyeager19

one way to do it would be to use the dynamic rename tool in the developper category. You can type a formula like replace([name], ' ', '_') for the space or use replacechar if you have multiple fields.

Another way to do it is to use a field info, then use a formula to replace/change the names and finally use a dynamic rename to give the names you want.

with those methods, you could basically use any formula you use in your usual workflows and apply those to the field names.

Arthur

[Emmanuel_G]

Hi @Meghanyeager19 ,

Find attached workflow to edit fields name ine one formula.

Do you want something like this ?

[Meghanyeager19]

Hi @Ladarthure !

For these solutions, I would have to type in exactly the character I want need to remove and replace correct? For these files, I have different characters, in separate header fields, in each file that need to be replaced. For example, one header is "Count #" and I need it to be "Count_" another is " vendor (amount)" and I need it to be " vendor_amount_". So for 20 unique files - I would need to configure each specific header. Is there a way to remove all special characters for each header to be replaced with "_" that could be applied all header fields so I would only have to run each file through the same workflow? 

[Ladarthure]

@Meghanyeager19,

it depends a lot on what you consider a special character, for instance if you want to remove é by e you could use DecomposeUnicodeForMatch function which will replace those. If you want to remove all punctuation for instance, you could use a regex formula like this one (from data cleansing tool) :

REGEX_REPLACE([_CurrentField_],'[[:punct:]]|[\$\+<=>\^`\|~]','_') or 

REGEX_Replace([test], '\W', '_') (which will replace all non words characters)

If you use the field info method, you could have a mapping table of all the the chars you want to replace and combine it with a find and replace tool for instance

[Meghanyeager19]

@Ladarthure Do you have an example flow of a mapping table? I think that might be the best way to do this! I used the dynamic rename tool but only one of the special characters I put into the expression would work for the output?

[Ladarthure]

@Meghanyeager19 please find attached both methods I mentionned, you can be a lot more creative about it but I just made a simple version for example