Dash (-) causing error - How to fix it using batch macro?

[udles5666]

Hello Community 

I have an excel dataset with 50 tabs having 48 columns in each tab. The structure of the column doesn't change but, few values of a field have a - (dash) which i think is causing an issue for Dynamic input tool.  Can you please help me resolve this? I tried to build a macro but got stuck and couldn't figure out a way forward. Shall i be using a macro though for it? 

Appreciate help in advance. Workflow with dummy data attached. Thanks heaps :)

[Gaurav_Dhama_]

Yes, you will have to use macro for this scenario.

Why? Because in one sheet you have just normal letters, while in another sheet you have special characters like -. What happens?

Normal characters will result is data type v_string, while data with special character will result in v_wstring. These two have different ways they handle string. So, you get schema error.

[JosephSerpis]

Hi @udles5666 have a look at the solution from this post it uses two batch macros the first get a list of the sheets so it's dynamic then a formula tool builds the filepath including the sheets and then retrieves all the data from those sheets.

[dreldrel]

Just an idea - maybe you could use an input tool in the batch macro and then just give the file path of those 50 tabs, which can be generated by a formula tool in Alteryx. If this helps, feel free to like the comment and select as a solution

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Batch-Macros-for-Multiple-exce...

[udles5666]

Hey @JosephSerpis - Apologies for the delayed response.
I am struggling to formulate this macro.

Can you please guide me step by step if possible? I had a crack at it - but, no go. I get stuck where the macro is only pulling up data from one of the tab. There's something I am doing wrong but, can't get hear around. Can you please help me resolve this? Attachments added incl. macro. 

Thanks heaps!  

[PangHC]

@udles5666 it simply because you not change the sheetname. so it just repeat sheet1 with x time of sheets count

[udles5666]

Thanks @PangHC - What should i change the setting to get the desired output then? 

Thanks,

[PangHC]

@udles5666 it should change whole value. so no check required in bottom. 
then feed the similar structure string to it. 
where [filename]|||[sheetname].

for example:

[Qiu]

@udles5666 

I modified your macro a bit with a Dynamic Input tool inside the batch macro.

[udles5666]

Hi @Qiu 

This looks good from the screenshot but, the flow/package you have attached doesn't showcases the same. May be you attached the old (original) attachment. Can you please paste the correct (your) one? Thanks,