Dynamic Input Not Detecting All Fields from Excel Files

[patrick_mcauliffe]

I have a file that I receive (Microsoft Excel 97-2003 Worksheet) which I recevie on a regular basis.

The file always has the same number of fields, although the number of rows may vary.

Every time I receive the file I save it to the same directory.  Once I have enough, I use the Dynamic Input tool to compile them into a single sheet and then distribute via various outputs/formats.

The problem I'm running into is that I will often get this message:
"The file (yourfilenamehere) has a different # of fields than the 1st file in the set and will be skipped"

I have finally been able to narrow it down to the root cause of that file just doesn't have a value for the first record past the header in the last column.

So, a file that works will look like this:

 

And a file that doesn't work will look like this:

I thought that maybe since it was detecting a header with no record that delclaring the first row as data would resolve this; but surprisingly it didn't.

Even if I set the dynamic input to treat the first row as data, it would still skip the file if there was an empty set in that last field.

The field comes from a government feed, so the chance of it being changed at the source is almost impossible.

Aside from opening up, checking the file, and inserting adummy value every time.... any ideas on how to automate a fix?

[MarqueeCrew]

Instead of a dynamic input, have you tried with a wildcard (*)?

\filename*.xls

I tested this and as long as your sheet names are the same, it will read without a problem.

thanks,

Mark

[patrick_mcauliffe]

Not sure I understand.

My directory tool is set to the directory where all files are stored, the file specified is"Report*.xls"

That feeds to a dynamic input.

The dynamic input uses "Report1.xls" as a template, with the action to change full file path.

I tried to use an input tool with a wildcard, but that wouldn't even let me complete the "open file" dialog.

[MarqueeCrew · Accepted solution]

If you use a standard input tool, select a filename and then for a table, select the table name sheet1. Then modify the filename with a wildcard. That should work if all sheets and all columns are the same.

[patrick_mcauliffe]

I understand now.  Thanks!  That worked!