Dynamic Input - OleDb Connection strings

[SC1]

I have monthly sales figures (.xls) that contain the same columns, but the could have different numbers of rows (more to come on that). I'd like to combine the 120 files (10yrs of monthly data) into one file for use in Tableau.  
I've started down the path of Directory --> Dynamic Input -->(varied analysis) --> .tde file.  

I cannot seem to get around this Dynamic Input error:
"Error: Dynamic Input (41): OleDb connection strings should be in the form: odb:ConnectString|table"

This is literally my first Workflow from scratch so I could be missing something obvious, but have been unable to solve with help tool.

Additional information:

-Each file will have the following columns "Date, Asset Class, Category, Total USD".  Overtime, certain categories have been added or deleted.

-I'm linking to the first file as a template, then using Field: FullPath; Action: Change Entire File Path.

Thanks for the time and consideration.  Let me know if additional information is needed to help resolve.

Best,

SC

[JordanB · Accepted solution]

Hi SC,

I suggest you add the table name to the end of the full paths you have. For example:

C\Users\Desktop\Test.xls|Sheet1

I would then use a batch macro which will be able to read different field schema, as it seems the field numbers and names will change over time making the module more dynamic. Here is an Article which describes how to do this. 

Best,

Jordan Barker

Client Services Support Engineer

[michael_treadwell · Accepted solution]

Try this: after your Directory Input tool, add a Formula tool to update the [FullPath] field.

[FullPath] + '|Sheet1$'

Of course, this will only work if the data you want is contained in Sheet1 of each workbook.