Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

The spaces from my input file are disappearing.

MamaGidge
5 - Atom

I have a pipe delimited file, which I am bringing in using the input tool and reading it in as a delimited text file.   

 

input snippit.PNG

 

 

 

 

 

I need to find all fields with extra white spaces, but when I use REGEX_CountMatches, Nothing is returned.  It appears that the white space is cleaned before I even do anything with the file.   I am essentially running a data quality check on a file that a partner of ours will be sending us, so I need to be able to find all of the white spaces in the file, so they can correct this before sending it through an automated process.  

ABC
1       23

 

I need a way to return the spaces after 1 and the spaces before two, but alteryx is cleaning this up!  How do I get Alteryx to keep the spaces??spaces.PNG

5 REPLIES 5
danilang
19 - Altair
19 - Altair

Hi @MamaGidge 

 

I'm not sure if this is standard behaviour or only happens with pipe delimiters, but you can get around it this way

 

WF.png

 

Change the delimiter in your Input to \0(no delimiter).  

 

Input.png

 

Then split the field on the pipe and use a dynamic rename to get the field names

 

Results.png

 

Edit:  I checked with other delimiters and this behaviour occurs in all cases.  According to the main wiki article on CSV, removing the leading and trailing spaces makes this implementation non-compliant with RFC-4180.  It may be something you want to bring up as an issue.

 

 

 

Dan

Thableaus
17 - Castor
17 - Castor

Hi @MamaGidge 

 

Interesting point. 

A workaround would be using no delimiters (actually, \0 delimiter) and Text to Columns right after, but this would take extra work, since you would have to use a fixed number of columns or to do some tranposing if you split to rows.

 

Cheers,

LordNeilLord
15 - Aurora

Hey @MamaGidge 

 

Try this:

 

Import the file with no delimiters and then use text to columns to seperate out the data

 

txt.png

MichalM
Alteryx
Alteryx

Hi @MamaGidge 

 

If you read the file in without a delimiter (use \0 instead of |) and then use a Text to Column tool to parse the file using the pipe, both the leading and the trailing white space will be persisted.

no-delimited.pngno-delimited1.png

 

Hope that helps!

 

Michal

 

 

MamaGidge
5 - Atom

Thanks!  This did work.  I had to make one change.  Since the actual file I am working has 87 columns, I had to change the field length in the input file.  Without changing the field length, I was losing more than half of my header fields.  

 

 

  setup.PNG

 

 

 

Labels