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.

[girkarkamlesh]

Greetings! 

Came across this problem today in 2025! but glad to see this community found this issue earlier.

what i noticed with xlsb data set is that, if you have plenty of data starting with Numbers it assigns data type of double or of any form suitable for numbers, irrespective whether you have some string in end. Converting String to null in the end.

In case of Aguisande

i believe you have selected first row contains data, making header as first row, which will change the data type of first row to string.

and hence it was showing values. 

I couldn't find solution to this but two work around

1. To convert xlsb to xlsx which is mentioned by ryan_e

2. If still continue to use xlsb then open the file and convert the specific column to Text Format in Excel would solve the problem 

Thanks