Error while reading multiple files with similar schema.

[stj1120]

Hi all,

I have created excel files based on the site wise. Usually the schema in all the files should be similar. But, the schema differs to few files. When I checked I got to know that, for the column called 'Calculated Segment' few Sites have data and few are Null. 

Which ever the sites have Null Calculated Segments, they are throwing error as they have different schema. Please check the screenshots. 

Please help to resolve this issue. 

Best,

Teja.

[messi007 · Accepted solution]

@stj1120,

Yes it's because of the dynamic Read.

below how to resolve that:

1- Create a batch macro that will read file by file

2- Make sure that this option is checked in order to read even different schema files

4- In the read part I added the sheet name to the full path

Attached the packaged workflow and macro.

If this solve the need please mark it as solved it will help others,

Regards

[CarliE]

Hi Teja,

The schema looks at field names as well as changes in the data type and size. You are most likely getting that error because of this. To avoid this error, you could build a batch macro where the control parameter updates an Input tool, rather than using a Dynamic Input tool. 

Take a look at this article that explains this issue. There is also an example in that article which should help.

Best,

Carli

[stj1120]

Hi @CarliE and @messi007 

Thank you for your quick and wonderful responses!

But, is that possible to achieve only by using Directory and Dynamic Input tools without using any batch macro?

When I tried using batch macro I get the error as 'The field "Sheet names" is not contained in the record'. 

How this can be rectified? I tried a lot, but no luck. Please help.

Thank you.

Best regards,

Teja.

[messi007]

@stj1120,

As you have a different schema, the only way to do it is using a batch macro

In order to correct the error you have to add the sheet name to the file path before the macro input:

Hope this helps,

Regards

[CarliE · Accepted solution]

Hi!

What you can do is instead of replacing the full path in the macro, you can replace the filename instead. Attached is the package workbook. The macro will reference the filename instead of the full path and inside the macro, we will just replace the file name. All these sheets should automatically have that one "Sheet 1" tab from the file generation workflow you provided.

[stj1120]

Hi @messi007 

I still get the same error.

Please help.

Thanks a lot!

Best,

Teja.