Inspiring Ingenuity

Alteryx, Bicycles and Teaching Kids Programming.


Alteryx: Reading XLSX Directly

Sometimes, it seems that Excel is the most popular database format for our customers.  My response has always been that Excel is not a database, but at the end of the day it doesn’t matter – they have data there that they want to use in Alteryx.  In reality I am sure other formats get used more, its just that I hear about Excel so often because there are so many issues.

Our support for Excel files is through a Microsoft driver.  In the case of modern Excel (XLSX files) that support comes from the Access Database Engine.  There are a bunch of issues with this;  if you have a 32bit version of office installed, you can only have the 32bit driver which makes us have to jump through additional hoops if you have a 64 bit Alteryx.  For various reasons, it is a hard one for us to install as part of Alteryx, so it is left to the user.  This leaves these files unreadable on many machines.

Recently I got the following on our internal support email:

… The issue here is that when you bring the data of the spreadsheet into Alteryx, it rounds up the decimal points to fewer than from data that is showing in Excel. Where the spreadsheet shows 5 decimal points, Alteryx only brings in 4 decimal points. The user doesn’t need the entire decimal range, but at least wants to have the 5 decimals of data. …

Continue reading


Alteryx: Ensuring fields are in a data stream

This week’s question:  How do I ensure fields are in a data stream?

This is a common issue when you are reading data that changes from time to time.  If you use a Crosstab Tool or are reading from an XML file, the output fields can change based on the presence (or absence) of specific input data.  This makes it very hard to write formulas, or really do any down stream processing on those fields.  In particular, what we want to do is ensure fields are present, but NOT change them if they are already in the record. Continue reading


Alteryx: Encrypting Data

Question: Can Alteryx do field encryption and then decrypt the field?  For example Example Social Security or Account Numbers?

I couldn’t help but take the challenge – I have been thinking about encryption a bunch lately…  Of course Alteryx has no built in method for encrypting data at this point, although it is a fine feature suggestion.  Don’t be surprised to see an AES_Encrypt(…) function at some point in the future. Continue reading