Incorrect Column Type Preventing ETL

mburstein

I'm trying to use Magic ETL to transform a dataset using rank&window and remove duplicates.  My ETL keeps failing due to the error "Failed to parse data as integer for column".   I attempted to input a "Set Column Type" step connected to input but the ETL is failing at the "Input Data Set" stage and never reaches that point.   The input dataset is based off a connection to another application (JIRA) and I don't see any way to cast the column type appropriately on the initial dataset.  I don't even need the column in question (not including it in my final Select Columns) but it's still blocking the transform.

 

Is there a way to actually fix the column type in the initial dataset?  Or is there another approach I should be using for this transform? 

 

I reviewed some of the other posts regarding similar issues but was unable to find a solution.

mburstein

I ended up fixing the issue by creating a new data set and importing a wider range of entries. The larger sample size correctly cast the column type and I was able to finish the ETL.

 

Thanks for the suggestions.

NewsomSolutions

Let me recap to make sure I'm understanding your problem correctly.

 

You have an Input - then a Set Column Type then whatever....

 

But you have an error at Input (which is the first action in your ETL) or the error is on the fact the SCT can't convert some peice of data?  Most likely your problem is that you have a non-numeric value somewhere in your initial data set preventing the concersion.  

 

If that is the case, what you need to do is add a "Replace Text" action in.  If you know it is something that is not a number is in there but don't know what it use the option for "regex' (gear button on step 2 in Replace text" and put in \D*  .  Then for step 3 put in replace text.

 

I hope that helps.

Matt

 

**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.

 

mburstein

Thank you for your reply. Unfortunately, this does not solve my problem. 

 

The problem IS that there is a non-numeric value in the column.   The column in question is cast as a number in the dataset, however it should NOT be a number as there are valid values in the column like "PM-123".  Since the column is a number, it can't be selected in a "Replace Text" action.  This is why I was attempting Set Column Type.

NewsomSolutions

What is your input data source?  And is Domo assuming your initial data type is Numeric but they're wrong?  Domo determines datatype sometimes b/c of the first set of rows....if the alpha numberic is outside that sample set then it may be incorrectly assuming the wrong datatype.  

 

I'm guessing your Set Col Type is tyring to turn it to TEXT already, but if your error is on the input...maybe the solution is force Domo to put a sample with alpha characters early in the sample set. Bad way of doing things, but just thinking of possible solutions.

mburstein

I ended up fixing the issue by creating a new data set and importing a wider range of entries. The larger sample size correctly cast the column type and I was able to finish the ETL.

 

Thanks for the suggestions.