Alteryx not reading in every cell of data in XLS file

[bjschwartz3]

I'm reading in a XLS file but some of the records are showing up as NULL even though they are in the data. For example, I'm reading in the following XLS file but the column 'Expiry Date' doesn't show up from the input tool in Alteryx.

  

edit: It seems to be forcing certain rows to a particular data type. The 'Expiry Date' field is being marked as a DataTime field which causes the field to become NULL.

[Qiu]

@bjschwartz3 
So you solved this by yourself.😁

[bjschwartz3]

I still need the text 'Expiry Date' to show up. Is there a way to disable automatic field types for a column?

@Qiu 

[AkimasaKajitani]

Hi @bjschwartz3 

You can use "First Row Contains Data" option as text field. 

If you check the option, all Field will be as String Type fields. Because the field names are text.

[bjschwartz3]

The problem is the top of the spreadsheet is mostly NULLs so even when that is unchecked, quite a few columns are changed to another format other than V_WString.

  

.

[AkimasaKajitani]

Hi @bjschwartz3 

Do you mean the xls file has no header?

If it is true, I don't more no idea.

When the xls file has the header, First Row Contains Data option make the header as data, so all field may be String type.

[bjschwartz3]

The header is located on row 11.

[AkimasaKajitani]

The header is only row 11?

If that is true, I can't think of any other idea.

[csmith11]

We were able to implement this approach inside a Batch macro to resolve this exact issue. Great advice!

[jdminton]

Have you tried changing the starting line to 11? You can leave the headers on and change the starting point to record 11. This should fix it for you.