How do I eliminate ?'s (they can be made blanks or 0") before the columns are set up?

[4aec8b415ada494370ba]

I have an occasional question mark in my space delimited data - it might look like:

"Main" "1275D2" "O" "1187155" 1 1.16 "RVB"

"Main" "Stage" "I" ? ? .014 "RVB"

How do I eliminate the ?'s (they can be made blanks or 0") before the columns are set up?

It works great until it hits those ?'s. For some reason it wants to put: 

I" ? ? .0144 "RVB

Into the third column of that row. How do I eliminate the ?'s in my file?

[AMiller_Tri]

Hi @Rick Van Baren? , thank you for your question.

To my understanding, you'd like to remove the question marks? Or replace them with zeros.

Using the find-replace transform, you can ask to find the ?'s, and in the replace field - you can either leave it empty or put a zero.

In this transform's "advanced" options, you can hit the checkbox of "match all occurrences", so all question marks will be replaced.

Here's a picture to demonstrate:

I hope that helps.

Feel free to ask any further questions.

Thanks,

Amit.

[4aec8b415ada494370ba]

Thanks Amit!

Is there a way to replace them before it columnizes the data? The problem is that the ? ? 's causes it to put the entire

I" ? ? .0144 "RVB

into the one column, even though those are the values for that column ("I") and the next four columns. I think because the column the first ? is in is a string field, and it's looking to match the ""'s.

[4aec8b415ada494370ba]

I did find a solution by creating formulas to check for a starting I", and if it's there to substring the values out into the subsequent columns. It works, but it seems to be too specific and clumsy.

[AMiller_Tri]

Hi @Rick Van Baren? ,

I'm happy you found a solution.

However, I'm not sure I fully understand the columns problem.

Would you be able to supply a screenshot of the table, as it is with the question marks? Not sure how the question marks being there would block column separation, but I could be missing some visual aid.

[4aec8b415ada494370ba]

A picture is worth a thousand words....

The first and second ? should be in columns 12 and 13, the .125 should be in col 14, and the MXW should be in col 15.

It only does this on the rows where col12 and col13 are ?.

[AMiller_Tri · Accepted solution]

Thanks @Rick Van Baren? , I now have a better understanding of the scenario.

You're saying this is a space-separated-values file? meaning a space is the delimiter between each 2 columns, and none of the columns have space between them?

• Do the question marks appear in the original file as well? before being imported to Trifacta?

While I'm not sure as to the source of this scenario, you can try and do "remove structure" from the file upon import, and then parse the file yourself, including column delimiter. That way you can make sure it captures the question mark as well.

See docs here

I hope this helps.

Feel free to ask any further questions.