Alteryx can't read a tab

Hi there,

I used a macro to read multiple files into alteryx. The macro works well on tabs A, B, C. But on tab D, alteryx can't read anything. It just shows column title but no content in the columns.

The fact that this macro works for other tabs makes me think coding works. I am puzzled what can cause tab D not to be read in correctly. Have you seen formatting issues in excel that would cause the entire tab not be read?

Alteryx flow looks like this

Directory -> Formula([FullPath]+"|||Asset Characteristics") -> Macro

Thanks,

Yiling

Input

Accepted answers

jdminton

You can try this one. I think in the macro setting the check box did need to be checked to replace the path. This one should work though. The macro should also be packaged in here, but let me know if you need it separately. Please mark as solved if it works.

GHG Workflow.yxzp

All comments

alexnajm

Maybe there’s an extra space? Can you read in the file with an Input Data tool but instead of reading a specific sheet, can you import the list of sheet names? Perhaps you could even use this list to Append to the file paths of the Directory tool and use the Formula from there!

binu_acs

@yilingchen02 Are you using a batch macro to read the excel sheets?

jdminton

Can you share a screenshot of the Excel tab D? Also, the workflow would be helpful.

yilingchen02

correct, batch macro

yilingchen02

Screen shots for the spreadsheet (redacted) and workflow is attached. Thanks

workflow.PNG

tab.PNG

alexnajm

Not sure we can see the tab names @yilingchen02

yilingchen02

Tab name is 'GHG'

tabname.PNG

alexnajm

So you can confirm you are trying to read in [FullPath]+"|||GHG" ? And there are no extra characters (like spaces) in the tab name?

yilingchen02

Thanks. Yes that's correct. Four people filled out the same template. Three templates read in fine the 'GHG' tab. Just one template has the problem. For the problem tab we can still read the column header which makes me think they recognize the tab but somehow can't read in the numbers in that tab. As if the numbers are completely hidden.

jdminton

Thank you. Can you share the output that you are getting for this tab? Since we can’t see what’s happening in the macro, I agree that you should try an input tool for this one tab only to see if a regular input works. If you can share the config screen for the input tool, that might help if you are receiving the same result.

jdminton

I know that merged cells can cause an issue sometimes. It looks like column a may be merged down the entire page. Can you confirm? If it is, I would compare to the working GHG tabs and see if it is in those also.

yilingchen02

This is how the output looks like: it has column names but nothing below that.

output.PNG

jdminton

Can you also share the configuration screen from the input tool in the macro? The headers shown in your output don't align with anything visible in the spreadsheet. Also, I may have missed it, but did you try to use a regular input tool on just this tab to see if it worked with the regular tool?

yilingchen02

Great suggestion! On regular input tool the import works on tab 'GHG'. Then the problem seems to be batch macro code? Here is a quick look. Could it be because the batch macro was reference a different tab 'Asset Characteristics' instead of 'GHG'?

macro.PNG

jdminton

We'll nee to look at the configuration for the highlighted tool. It should look something like the second image.

yilingchen02

This?

config2.PNG

jdminton

That piece looks fine. can you click on the input and get the config screen for that? It should look similar to the below

yilingchen02

Thank you. Input looks like this

input2.PNG

jdminton

It doesn't make much sense why this isn't working at this point from an Alteryx perspective unless it is column A on the spreadsheet. Are you able to delete column A from the spreadsheet tab and test the macro after?

yilingchen02

I know it's weird...

Col A does not seem to be the problem...deleted A and the issue remains.

jdminton

If you can, remove the data that might contain sensitive information and include the Excel file as attachment. I would like to try on my system. You can delete the other tabs.

yilingchen02

Thank you. Please see attached. Tab 'GHG' is the problem tab. Let me know if you figure this one out!

Redacted Spreadsheet.xlsx

jdminton

I was successful in importing with both an input tool and a macro. One last thing to check is the formula tool that is updating the path. Can you share the configuration for that tool? If that tool is set up correctly, we might just want to recreate the macro or try in a different workflow to eliminate possible file corruption.

yilingchen02

Woohoo! Thank you so much. Would you be able to share the workflow / batch macro?

The formula config is attached.

formula2.PNG

jdminton

Try updating the formula to [FullPath]+"|||'GHG$'" The only differences are the single quote around GHG and the dollar sign ($). That should read it in correctly though.

yilingchen02

ah, tried [FullPath]+"|||'GHG$'"... it did not fix the issue.

jdminton

GHG Workflow.yxzp

yilingchen02

Thank you very much. It worked.

jdminton

Woohoo!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703