Unusual design in source file, damaging my data (excel)

Hi all ,

I have an unusual default design set in my source excel table:

the amount of 43000 code is positive, but when clicking the cell we can see at the function row that the raw amount is negative! (Screenshot attached). 43000 example.PNG

Surprisingly, Alteryx attracts the designed positive amount instead of the negative raw amount !

Do you have any idea how to solve that issue?

I thought maybe using VBA code on all the .xslx files (before the input) using "Run Command" tool - have any of you tried that before?

Notes:

# The code 43000 is only 1 example of many

# I can not convert all the codes (code * -1) since the formatting does not apply on all of them and we are not quite sure what is the customer's logic here.

# As input, I have over 200 excel files combined together, so any solution to apply on the source file is not relevant in this case

# I saved one of the files as CSV and then imported it into the flow - it did not help (value still saved with the wrong direction due to the formatting settings)

#I tried to correct the data and format immediately after inputting it to the flow [auto-filed tool, regex tool, "tostring" and "tonumber" ect.] but it didn't help.

Thanks in advance !

43000 example.PNG

Workflow

Developer

Input

Settings

Help

Preparation

Run Command

Accepted answers

Stavi

Solved with Alteryx's support :

Using Run Command tool to run .vbs code that cancel all formats at the excel source files

All comments

Luke_C

This is definitely interesting. I agree that a VBA script to update the values would definitely get the job done. I'm unaware of anything built into Alteryx that could help handle this.

You could use the run-command tool or set an event to run 'before run'. I have used this to execute a VB Script file on excels for a different use case, but it worked like a charm

Stavi

Hi @Luke_C

I didn't quite understand the context of your screenshot.

Do I need to set the "Events" and then defined my Run Command tool ?

BR,

Stav.

Luke_C

@Stavi

Sorry for the confusion. You could use the run command tool to run the command, or use the events menu:

The run command tool can be used to trigger the commands as part of the workflow.
The events can be triggered before or after the workflow runs. They work by referencing a .bat, .vbs, or some other executable in a folder location

Since your example requires running a vba script prior to the workflow, either using a run-command as one of the first tools in your workflow, or using an event set to run before the workflow starts would get the job done.

Stavi

Solved with Alteryx's support :

Using Run Command tool to run .vbs code that cancel all formats at the excel source files

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703