.XLS file limitations that produce NULL values

[JohnPo]

Question

When importing a .xls file using the Input tool, Alteryx produces NULL values for specific cells, why does this happen?

Summary

By design, Alteryx uses either the Jet or ACE drivers when importing .xls files. In short, it’s not really an Alteryx issue but rather a limitation on the Microsoft drivers, but there’s always a workaround available, and we’ll dig into it later.

One of the common reasons this happens is due to the logic both the JET and ACE drivers follow. By design, they look at the first eight rows in your document when identifying the data types and their lengths, when it comes to handling Legacy Excel files. Translation, please? If at least one cell within the first eight rows of your file contains more than 255 characters, then the drivers will set the data type as “memo” (i.e., a length that can hold up to 32,768 characters). Now if the data type doesn’t exceed 255 characters within the first eight rows the driver sets the data type as a string with a 255-character restriction. How does this relate back to you? Well, if any of your cell values that are after the first eight rows do surpass 255 characters, then the driver will assign a NULL value to those cells. Confusing, right? It’s a good thing all is not lost, at least not yet.

Solution

The user has the choice to work around this issue or fix it in their registry. The workaround includes the following:

1. From theInput DataTool configuration, check option 6: "First row contains data." This will force all fields as a V_String.
2. Configure theDynamic Rename Toolto "Take Field Names from First Row of Data."
3. Use the Auto Field Tool to assign the best field type for each data column correctly.

The fix for this involves changing a setting in your registry. These instructions are being provided as well as documented from Microsoft, as Alteryx cannot be sure that changing this setting will not cause problems with other applications. For more information on this issue:http://support.microsoft.com/kb/189897.

To change the Registry setting***

1. Go to Start > Run and type "regedit"
2. In the registry go to HKey Local Machine > Software > Microsoft > Jet > 4.0 > Engines > Excel
3. Double Click: TypeGuessRows
4. Change the value to 0 (zero)

Another workaround is to save the .xls file as a .xlsx. It’s well known that Microsoft hasn’t updated the legacy file extension since 1997. If this is an option you desire to exercise, fear not, there is a Macro for that, especially if you need to convert files by the masses.

Quick Shout out to DanJones for creating a fantastic app that takes a directory path and converts all xls files to csv or .xlsx respectively.

• https://community.alteryx.com/t5/Data-Sources/Error-opening-an-XLS-not-xlsx-file/td-p/47085/page/2
  • The post is located all the way in the bottom.

Disclaimer, when it comes to converting a .xls as .xlsx, please review if your workflow has a lot of formatting enabled, if so, please remember that sometimes this will potentially create inconsistencies with your data. It’s also possible to lose some data in the process.

Appendix

*** For performance reasons, setting the TypeGuessRows value to zero (0) is not recommended if your Excel table is enormous. When the value is set to zero, Microsoft Excel will scan all records in your table to determine the type of data in each column.

Source: https://help.alteryx.com/2018.1/index.htm#FAQ.htm?Highlight=xls

Thanks for reading! If you have any questions, please message away!

[davidhe]

so wanted to add a correction to the registry edit path in regedit: 

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft.

rather than 

HKey Local Machine > Software > Microsoft

[davidhe]

in-progress additional note: my experience thus far is that none of the workarounds will work unless you're willing to rip the 64 bit versions of your office drivers in favor of 32 bit, because you have to install the 32 bit access driver. 

So, it appears that only the .xls to .xlsx conversion is still possible. That's my next step. 

[Felipe_Ribeir0]

Hi @JohnPo  and @davidhe ,

I know that this post is a little old, but i built a workflow that convert any amount of xls files into xlsx and parse them.

Convert .xls into .xlsx and parse the input - Alteryx Community

Maybe this is useful for someone.

Thanks.

[davidhe]

very nice @Felipe_Ribeir0 , no need to install access drivers? 

[Felipe_Ribeir0]

Hi @davidhe , no need. It is using python to convert the files.

[davidhe]

@Felipe_Ribeir0 well done sir, i'll keep this in mind!