Schema Mismatch for Loading File

[kjvan026]

I have a query that I run which has approximately 3M lines. When the query exports, there are three tabs for all the results.  I have attempted to utilize a dynamic input to import the data into a flow. However, now all tabs are loading due to a schema issue.  I pinpointed it down to an issue with a column sometimes being interpreted as V_string and sometimes V_Wstring.

My work around is just to load the three tabs separately but I prefer not to do that as sometimes that are more and sometimes tabs depending on the data size and this is creating its own error issues. Again, I have a work around, but its not preferrable. I disconnect inputs when tabs are less and there is a risk that I won't reconnect appropriately.

Is there a better work around where I can force the right schema? The data always comes from the same source but I have no control over the data and my only option is excel. 

[KGT]

The quick solution that I would employ here is a batch macro. I highly recommend looking into it, even if you've never dealt with macros before. With batch macro experience I would be past this issue in around 5-6 minutes. As someone new to macros, it may be a 30-45 min adventure to apply this and understand it. (very big CI on that timing)

A dynamic Input is going to care about the metadata and so you're going to keep getting that error unless you force a type. The easiest (but not entirely accurate) way of doing this is to check "first row contains data" on the input template. That will force the headers to be the first row and possibly force a certain field type. I say that it's not entirely accurate as you still have no control and if it is a wide character somewhere in the data causing the V_WString then this probably won't work.

There are several other workarounds but they are way more complicated than the batch macro.

If these are xlsx, then the below will work, if they are xls, it gets more complicated, but if they are csv, there may be another way.

This would be the main workflow for import:

And this is the Macro. You can force whatever you want in the select tool. Note that if the datatype is already correct, then you can select (forced) for that type.