Batch Macro - Reading Excel Files - Error (Tool #8: INTERNAL ERROR)

[kreynolds]

Hello all,

I am using a batch macro to read excel files out of a directory into Alteryx. The majority of the files read successfully, but a few return "Tool #8: INTERNAL ERROR". I have copied the entire file path into file explorer and it successfully brings me to the file. There is no difference between the files that don't read and the ones that do. But the same files won't read each time. I can't share the workflow due to sensitive data, but below are some screenshots.

Any ideas on the issue?

[IraWatt]

Hey @kreynolds,

The error says record number 7,8,9 and 10 are the files causing the issue so perhaps compare one of the files with one which works with the Field Info tool. Out of interest how come your not using the dynamic input tool for this?

[TonyaS]

@kreynolds 

What version of Alteryx are you on? Curious if you are on 2022.1 where AMP is the default for new workflows. Or if you are using AMP Engine. 

[kreynolds]

Thanks for the quick response.

So I have looked into the files that it happens to and haven't had any luck finding the difference. The files are very similar. The sheet name that I am reading is the same on all files. Do you know another cause within the file that it could be?

I haven't tried the dynamic input tool yet. Will it work when there is quite a few null cells, and a little variance among the sheets?

[kreynolds]

I am using 2021.1. I'm not sure what you mean regarding the AMP engine?

[IraWatt]

Its perfect for that situation @kreynolds check out the example workflow for dynamic input. It should also give a more understandable error if there is anything up with the files 

[HomesickSurfer]

Hi @kreynolds 

Been there, done that...

Most likely the failing records (files) have too many formatted or occupied columns, perhaps up to XFD (16384 total columns), too many for Alteryx.

You can confirm that in Excel by viewing the sheet and pressing Ctrl+End, or input the failed file individually and view the field count.

Try configuring your input tool to a specific sheet range for example - "$A1:P"

This will omit the undesirable blank columns/fields.

You may also opt to add the below vba code to the file(s) and execute to reset the last cells once or upon demand.

Sub ResetLastCell()
ActiveSheet.UsedRange
End Sub
Sub Reset_all_lastcells()
Dim Sh As Worksheet, x As Long
For Each Sh In ActiveWorkbook.Worksheets
x = Sh.UsedRange.Rows.Count
Next Sh
End Sub

[kreynolds]

Thanks for the idea!

I have checked as you suggested and it doesn't look like there are too many occupied columns. It's a relatively small sheet.

[HomesickSurfer]

Visually it may not appear to have occupied columns....have you checked?  How many fields does Alteryx show for the failing files?

[kreynolds]

So I went through and deleted the rows below the data. If I press Ctrl + End, it brings me to the end of my data too. Additionally, Alteryx won't tell me how many rows it attempts to read, it only says Tool #8: Internal Error.