Input Data tool with .XLSB producing incorrect field format / metadata

[ryan_e]

I am trying to use the Input Data tool with an XLSB file. The file is accepted by the tool and read, however, the field format / metadata is incorrect for some fields. These fields are being read as Double, even though, towards the bottom of the input file, text is present in the field.

I can't use the Select tool to change the field format after the file has been read, as the data has already been lost at this point.

The following screenshot illustrates the problem. I have the same file being used in two Input Data tools. The first time I've set the tool to have data beginning in row 2 (ie first row is headers). The second tool I've set data beginning in row 1 (ie no headers in file) - this forces the tool to set the field format to string.

I've filtered for null results in one of the fields that is being converted to Double. As you can see in the browse results, there are ~2500 null rows. 

In second example there are no null results.

Is there any way, aside from the workaround I've used, to get around this issue? And why is the input tool not reading all data in the field before settings its format?

Thanks

Ryan

[Aguisande]

Hi @ryan_e 

Is odd that having text present in the file, the metadata and values are Double.

Can you attach your XLSB file? 

[ryan_e]

Attached is an XLSX version of the stripped down file (the forum does not allow .XLSB attachments).

Please Save As this file as XLSB and it should exhibit the problem.

Thanks

Ryan

[Aguisande]

Right away! I'll keep you posted on my findings.

Edit 1: What I found (Very odd by the way) is that if you select First Row Contains Data and later Dynamic Rename (taking the names from the first row) it respects the original data types.