import numbers with brackets

[JokeFun]

I have a monthly xlsb file input which is now imported to the workflow by using the Read_All_Excel_Files macro.

It works most of the time. But issue came up for a certain column during some of the months. This column is supposed to be numbers, some of which can be shown as 0, or -, and some are just blank which is then shown as NULL in the workflow. And also the negative numbers are shown with (), just like (123,456.78).

In most of the cases, it goes well and the column is treated as double format.

But for some of the months, error message pops up saying it is a string format and cannot convert (123,456.78) to a number.

So I am thinking of creating a dynamic flow that if it is number format then it goes left and if it is string format, then remove the bracket by using formula REGEX_Replace([field1], "[(,*)]", "") and then convert to number format.

However, I don't know how to make it happen. Filtering or Detour do not seem to work here.

[Qiu]

@JokeFun 
How is number of the target columns?

If a few, we can go ahead and do the RegReplace, then going to downward.

If many, I will suggest a combination of Transpose and Cross Table before use regreplace.

[Emil_Kos]

Hi @JokeFun,

If you are using this macro you can identify which files aren't being correct.

Filter them out and load them once more separately? After loading separately just unify the data type for both and join those the two input together?

I am assuming that if you will load only incorrect one they will be loaded as a string and it will look correct. 

[JokeFun]

I just re-examined the files and noticed the one with problem is due to many rows are blank, especially from row 2 to the following and Alteryx takes it as V_WString instead of double. And thus those numbers are taken as (123,456.78) and cannot convert to number automatically. I also noticed those normal files also have the blank cells at this column but not so many.

So as a manual intervention solution, I removed all those blank rows since it happened these records (entire row) are not for current period.

But I do have this confusion on the impact to the workflow due to data source format change, which though is supposed to be the same.

Just like another case I experienced where a column is for dates, but sometimes it is imported as string format like 2020-12-31, and sometimes it is number format as 44196.

[Emil_Kos]

Hi @JokeFun,

Can you input the file name into the data. Remove all the data for the file names that are loaded incorrectly and load them separately? 

After that amend one of it to have a correct data type and join them together.

Thanks to that you will be sure that your data didn't get corrupted.