Recognizing Headers with different line break formats

This one is probably easy but I can't find the solution anywhere.

In excel the shortcut Alt+Enter will create a new line in a cell:

First Name	First Name
header is 1 line	header is 2 lines

Both of these headers have exactly the same characters and spacing but the left one is formatted as 1 line and the right is formatted with 2 lines. Unfortunately the files I work will come in both formats.

When I unite two files with headers with this formatting difference that are otherwise identical the data doesn't match because Alteryx is reading them as unique headers. took me a looooooong time to figure this out since in the Select tool they appear exactly the same!

How do I get Alteryx to read ONLY THE HEADERS as the same when there are line breaks in them like this?

Workflow

Connectors

Preparation

Accepted answers

All comments

Prometheus

@Rob48 You can use the Dynamic Rename tool to rename your fields by replacing the embedded newline with a space. I used this expression: regex_replace([_CurrentField_], '\n', ' ')

Dyn Change.PNG

Rob48

Thanks for your response, but bhis one doesn't quite word for me. Could you mock it up with 2 separate files, one with the headers in the 1 line format and one with headers in the two line format? and can you attach your workflow so I can play with it? Thanks!!

Rob48

I've attached the file I'm testing with, two tabs with the same data but headers in different formats

Headers - One line - 08Sep2023.xlsx

Prometheus

@Rob48 I used the Dynamic Input tool to open up your data because I wanted to see both sheets at once, but you can use an Input Data tool and configure it the same way with the check box for First Row Contains Data unchecked. After that, I used the Multi-Field formula with the same regex_replace expression I used above -- regex_replace([_CurrentField_], '\n', ' ') -- to swap out the embedded newline with a space, but this time that value is in the first row of data. Then I used the Dynamic Rename tool to rename the fields with the first row of data. I attached the workflow so you can play with it.

Dynamic Input Config.PNG

DI Output.PNG

MF Regex Replace.PNG

Dynamic Rename.PNG

Rename Headers.yxzp

Rob48

would you mind reattaching as a .yxmd? it's coming as a package and my work's system won't let me download it.

Prometheus

@Rob48 See if this works for you. Just map it to the dataset you posted.

Rename Headers.yxmd

Rob48

how would the workflow differ if I needed to set it up for two separate files? What I typically see is a new file every week so I'm pulling the data not from individual tabs in a single file but from separate files.

I have an existing workflow that has several separate input data files from past weeks that feed into a Union tool to process through a single flow. All of the files in the existing flow will have the headers as single lines. Then I receive the new week's file and that one will have some of the header names with line breaks, so when I feed them into the existing Union tool it interprets them as different headers and adds columns and messes up the flow.

Attached are two files, I'm looking for a mock up that would get both files into the same format so they could then feed into the Union tool and be processed together correctly.

Headers with ONE Line - 08Sep2023.xlsx

Headers with TWO lines - 08Sep2023.xlsx

Prometheus

@Rob48 You could bring each file in with a separate Input Data tool, use a Data Cleansing tool after the One Line dataset to remove null columns, then union them together. As with the last workflow, don't forget to map the Input Data tools to your data sources.

Remove Null Columns1.PNG

Union Output.PNG

Rename Headers2.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703