This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
When importing a .xls file using the Input tool, Alteryx produces NULL values for specific cells, why does this happen?
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.
The user has the choice to work around this issue or fix it in their registry. The workaround includes the following:
From the Input Data Tool configuration, check option 6: "First row contains data." This will force all fields as a V_String.
Configure the Dynamic Rename Tool to "Take Field Names from First Row of Data."
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***
Go to Start > Run and type "regedit"
In the registry go to HKey Local Machine > Software > Microsoft > Jet > 4.0 > Engines > Excel
Double Click: TypeGuessRows
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.
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.
*** 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.
Thanks for reading! If you have any questions, please message away!
This error is typically thrown when trying to read a file that does not match the selected File Format, but also in rare cases, from a corrupted file.
Error: FileID does not match in the FileHeader
Common causes/ resolutions:
Inputting a yxdb file? Make sure your file type is .yxdb rather than .csv, .kml, etc.
Trying to access a yxdb while it's updating? If you try to access the yxdb before the module is done running, or if the module crashes/errors during the creation of the yxdb, you'll see this error. Use a Block Until Done tool immediately before your Output tool.
Using a Directory tool/ Dynamic Input? Specify the file type. If your Dynamic Input is pointing to a yxdb template, use the *.yxdb wildcard in your Directory tool, rather than the all-extensions wildcard *.*
Running an app in the Gallery? Check 'Replace a specific string' in the Action connected to the Input tool where you specified the file type.
As always, if this does not resolve your error, please open a Support ticket through the Case Portal.
Microsoft released a fix for the XLS issue described below, this fix is included in their November 14, 2017 Monthly rollup.
For your convenience, this is the link to the fix for Windows 10
And this is for Windows 7 and other OS
We’ve been made aware of an issue connecting to .xls files with the File Format: Microsoft Excel 97-2003 (*.xls). The behavior manifests in two ways.
As an error pop-up box:
'InvalidArgument=Value of '0' is not valid for 'itemIndex'. Parameter name: itemIndex'
Within the Results - Messages pane of the Designer or on the Annotation associated with the impacted tool:
‘Error: Input Data (2): Error opening connect string: Microsoft JET Database Engine: Unexpected error from external database driver (1).\3275 = -535434136’
The error box appears when an Input Data tool is configured to open an .xls file for the first time ever (if there are cached tables, then the error box will not appear). The Annotation and Results pane error message occurs when an .xls file is dragged directly onto an Alteryx designer canvas or when a workflow containing an Input or Output tool that connects to an .xls file using the above format is run (within the designer, a server, or on a schedule), respectively.
The error and experience are discussed in this Alteryx Community post, though you can also read about the issue on this external forum and here, documenting this is a widespread experience across many platforms. Towards the bottom of the external forum post, you can see a comment from a Support Engineer on the Microsoft SQL BI team stating that Microsoft recommends not using the Microsoft JET engine drivers as the current solution to the issue. We detail below how to follow that directive within Alteryx.
The root cause of the error comes from Microsoft’s October Security Update (links below).
If you have already had the update installed, must remain using the update, or choose to proceed with the update, you will experience this behavior. In those situations, here are a couple options available to you.
For new or existing workflows:
Within Excel, manually save the .xls file as a .xlsx file and within Alteryx update tools pointing to the old file to point to the new file or Browse to the new file within an Input Data tool
For existing workflows:
Within Alteryx, manually change the File format to ‘Microsoft Excel Legacy (*.xlsx)’ within the affected Input Data or Output Data tool (described below)
For new workflows/new Input Data tools you will not be able to manually Browse out to the .xls file and read it in as you will receive the pop-up error message desribed above. Your options are:
Drag and drop the desired .xls file from the Windows explorer box onto the Designer canvas and then manually change the File format to 'Microsoft Excel Legacy (*.xlsx)'
Drag an Input Data tool onto the canvas and paste the full path of the desired .xls file into the Connect to a Database box within the Configuration pane and then manually change the File format to 'Microsoft Excel Legacy (*.xlsx)'
Option 2 and 3 Step-by-Step
Determine if you have the “Microsoft Excel Legacy (*.xlsx)” option available within your Input Data tool. You can determine this by loading any file into an Input Data tool, and then clicking the “File Format” drop-down. If you have this option, you can skip to Step 7.
Determine the “bitness” of your Office Installation. The file you download will depend on whether you are running a 32-bit or 64-bit version of Office. https://support.office.com/en-us/article/About-Office-What-version-of-Office-am-I-using-932788B8-A3CE-44BF-BB09-E334518B8B19?ui=en-US&rs=en-US&ad=US In this screenshot, I’m using a 32-bit copy of office.
Browse to the Microsoft Access Database Engine 2010 Redistributable page and click “Download” https://www.microsoft.com/en-us/download/details.aspx?id=13255
Select the download that matches your Office “bitness” from before, then click “Next”. The file will begin to download. Save the file to a location that you can run it from.
Run the previous installer you downloaded. You can simply click “Next” or “Install” through the installation utility, as options do not need to be changed. Once you receive the dialog that the setup has completed successfully, you can click “OK” and continue to the next step.
If Alteryx Designer was open prior to the installation, you should close the program fully, saving your work, and then re-open Alteryx Designer. Otherwise, if Alteryx Designer was not already open, you can open it now.
Within Alteryx Designer: Open the workflow with the issue in Alteryx Designer. b. Click on your Input Data or Output Data tool that is experiencing the issue. (Refer to Option 3 above if you are receiving the pop-up error box while connecting to an .xls file for the first time) c. Click the “File Format” drop-down and choose “Microsoft Excel Legacy (*.xlsx)” d. Data should now display in the preview window and the error should no longer appear if you click off the tool or run the workflow.
We understand this is likely to be a frustrating experience for our users and we are investigating what can be done from the Alteryx side to produce a fix for the issue. We are also following the Microsoft posts to understand what their response will be and if they will take any action toward resolution. Please contact Customer Support to report your experience with this issue, as that will allow us to contact any impacted users directly if and when there is a fix made available.
Updates associated with the error:
Windows 7, Windows Server 2008 R2 - https://support.microsoft.com/en-us/help/4041681/windows-7-update-kb4041681
Windows Server 2012 - https://support.microsoft.com/en-us/help/4041690/windows-server-2012-update-kb4041690
Windows 8.1, Windows Server 2012 R2 - https://support.microsoft.com/en-us/help/4041693/windows-81-update-kb4041693
Windows 10 Original - https://support.microsoft.com/en-us/help/4042895
Windows 10 1511 - https://support.microsoft.com/en-us/help/4041689
Windows 10 1607, Windows Server 2016 - https://support.microsoft.com/en-us/help/4041691
These methods should work in most versions of Alteryx.
One of the most common issues we see from clients trying to read in a .csv file is that they are receiving an error starting “Too many fields in row x”, and because of this Alteryx can’t read in the file.
There are a couple of different ways you can resolve this error.
First, in the Input Data tool, you can tell Alteryx to treat read errors as warnings to allow the file to be read in.
This will change the Error to a Warning so that Alteryx can read in the file so you can investigate.
The other method is to read the file in with no delimiter. You can do this by changing the delimiter in the Input Data tool to \0.
Once you have your data parsed back out into its fields, you can use the Dynamic Rename tool to correct your field names, a select tool to remove the original field, and a simple Trim() function to remove the extra delimiter from your data.
This process is illustrated in the attached workflow, created in version 10.1.
What does this error mean?
There was an error opening "[filepath].yxdb": Data at the root level is invalid. Line 1, position 1.
This error occurs when a user attempts to open an Alteryx data file (yxdb) by going to File --> Open Workflow.
This option is specifically designed to open only workflows (yxmd), macros (yxmc), apps (yxwz), and packages (yxzp).
To read an Alteryx data file in to Alteryx please make sure to use an Input Data Tool and navigate to your .yxdb file accordingly. This will insert your data file in to your workflow and enable you to use subsequent Alteryx tools to blend, cleanse, parse, manipulate and augment your data until your heart is content!
I am reading a large(ish) .txt file, with fields separated by semicolons.
Out of more than 2 million records, on about 800 I get the error: "not enough fields in record" from the Input Data Tool.
This error is very similar to the error mentioned in this article. Generally it refers to an issue where a particular row of data has less delimiters than previous rows. There are a couple of ways to resolve this and fix the row that is causing the issue. Both solutions from the original forum post will help to make it so you can read the file into Alteryx. Personally, I prefer the option where you read in the data file without a delimiter (either specify None or use \0). This forces all the data into a single field which you can then easily parse out using the Text to Columns tool as noted in the first article linked above.
Attached is a sample that shows the parsing method to resolve the issue.
The other way to go about it is to use the process below:
In the Input Tool configuration, there is a "Treat Read Errors as Warnings".
If you check this it will typically allow all the records to be imported and then you can determine where the issue is (it should give you warnings on the records with an issue).
Why am I getting the error "InvalidArgument=Value of '0' is not valid for 'itemIndex'. Parameter name: itemIndex" when trying to load an Excel file in and Input tool?
This error has come up when an Excel file that had the extension .xls, but was saved as .xlsx, or vice versa. The resolution to this is to save the file with the original extension.
Sometimes when reading a delimited text file (like CSV) an error like this may appear ‘Error reading “FILEPATH\MYFILE.txt”: Too many fields in record #’
The cause of this error is that too many delimiters were located in that specified record.
For example if I had a text file with the contents below:
You see that at record 6 (assuming x and y are field headers) I have an extra comma. If I’m specifying the delimiter to be a comma, Alteryx has recognized from the first record that there should only be two columns in this dataset. Since it sees three on record 6, it will error.
If you had the opposite case where you started with three commas for each record then suddenly had two like this:
You would just get a warning like this:
'Warning: Input Data (1): Record #6: Not enough fields in record'
And the last record in the third field will just be null.
So how do we get around this? Well we could always go into the text file itself and edit the file. This could be cumbersome if you have a lot of records in the text file. Instead let’s just use Alteryx to solve our problems.
First, let’s change our Input Data Tool to bring in the file as a non-delimited text file:
Now we can attach the Text To Columns Tool to separate the fields by our specified delimiter.
Now that our fields have been brought in it’s up to you to decide how handle that extra field, we could simply delete it or keep it. If you’re still wondering about its contents we can simply use a filter tool where the Record ID will be set equal to the record number that popped up in the error.
Attached to this article is a workflow I built in 10.6 that should illustrate this example.
Every so often we get questions about a .tde (Tableau Data Extract) file that is being output from Alteryx that has a file size of 30k when the original data is much larger. When the file is opened in Tableau this error sometimes comes up: An error occurred while communicating with data Source ‘yourfilename.tde’
When importing a delimited file, have you ever run across an error saying ‘Too many fields in record…’ and it’s something like record number 1478254 and you think to yourself, “how the heck am I going to find that record and correct it?”
Well, let us help you with that.
This message is essentially telling us that some particular row of data, when parsed, has more fields that the first row of data (usually caused by an extra delimiter in that row).
The first step in identifying the row is to change the delimiter in the Input Data configuration window. Change the delimiter from the current delimiter to \0 (that is a backslash zero, which stands for no delimiter). This will read in your file but instead of parsing, it’ll just read in the data as is.
Now just add a RecordID tool and Filter tool so we can filter on RecordID 2 (or 1478254) to find the row that is holding us up.
Now that you’ve identified the row that is causing issues, you could go back to your original data, correct it and try importing it again.
If you were looking closely at the example data, you may have noticed that record 4 also had a different number of fields as row 1. After correcting row 2 and importing , again, we would get an error for row 4. Just follow the same procedure to correct additional errors. This could probably be done through an automated/iterative process, but that will have to wait for another time.
(attached is an example workflow created in Alteryx 10.0)
Error: "system.data.oracle client requires 8.1.7 or greater"
Please bear in mind this error is not related to Alteryx but to Oracle. This error message is returned from the Oracle Database, therefore please check with your DBA about the configuration and set up of your machine and server before reaching out to firstname.lastname@example.org. However, we are always happy to help so here are a few troubleshooting tips!
Which database connection did you use?
If you tried using the ODBC connection, try using the OCI (New Oracle Connection). If you do not know what the TNS server name it can be found in the TNSnames.ORA file. Your DBA will also know this.
What permissions do you have?
Go to the Oracle Client folder.
2- Right Click on the folder.
3- On security Tab, Add “Authenticated Users” and give this account Read & Execute permission.
4- Apply this security for all folders, Subfolders and Files (IMPORTANT).
5- Don’t Forget to REBOOT your Machine; if you forgot to do this you will still face the same problem unless you restart your machine.
*Before changing any permission settings please check with your IT
How many instances of Oracle do you have installed?
Oracle may find it difficult to navigate and select the correct version if it has more than one version installed. If you only need one of them it is better just to remove them all and reinstall the one that is needed.
Do you have the correct drivers installed?
One way to check to see if you have the right drivers installed is to set up the DSN connection through Alteryx, navigate to the ODBC admin and see if any drivers populate for either the 32 or 64 bit connections.
Do you have one of the supported client versions?
Please see the Technical Specifications
Please bear in mind often this error is not related to Alteryx but to Oracle. This error message is returned from the Oracle Datbase side and therefore please check with your DBA about the configuration and set up of your client before reaching out to Alteryx Support
Client Service Support Engineer
Errors from the R tool, and macros using R, such as the Association Analysis tool, can be a challenge to troubleshoot. Since R is very particular about naming conventions, and the R macros are very particular about delimiters, one easy way to preemptively limit these errors is to make sure your field names are R-friendly and compliant.
Best practices is to use only letters, numbers and the underscore. Any special characters will likely upset R. One hint is "Error: No valid fields were selected." This means that one of the Pipe-to-Rows tools embedded in the R macros could not appropriately parse the field names.
For example, the following errors came from the Association Analysis tool, where the field names contain the gamut from asterisks to ampersands:
Error: Association Analysis (4): The Action "select fields A" has an error: ParseError: Expected entity name for reference at line 7 and column 30 while processing "Action_UpdateRawXml"
Error: Association Analysis (4): The Action "select fields A" has an error: ParseError: Expected whitespace at line 13 and column 63 while processing "Action_UpdateRawXml"
Error: Association Analysis (3): The Action "select fields A" has an error: ParseError: Unterminated entity reference, 'test' at line 4 and column 30 while processing "Action_UpdateRawXml"
If you have a thousand fields with every special character, don't fret. Dynamic Rename and regex to the rescue!
Using the Rename Mode: Formula, select all your fields, and replace any non-word character with an underscore:
Crisis averted! Happy Alteryx-ing.
My workflow was doing just great, reading in a bunch of stuff and cranking out new data like a boss. But then I saved the workflow, closed down Alteryx, and now when I try to open that file up again, I get the following error:
There was an error opening "C:\my file name.yxmd" There is an unclosed literal string. Line 3042, position 42.
Does this mean anything to you guys?
The workflow has likely become corrupted, perhaps after saving it across a network, and has dropped something in the save process.
Alteryx creates backups of the workflows (same name with a .bak extension). Try to open the .bak file in Alteryx.
Go to File ->Open and change the "Files of Type" to All Files. The .bak files will show up and you can open the one you are having difficulty with.
Another alternative would be to check if Alteryx auto-saved it prior to your last save. Go to File -> Open Autosaved files.
Why did all of my scheduled workflows fail? What does the error “Logon Failure: unknown user name or bad password” mean?
This error is due to the credentials that are used for the scheduler are no longer valid and have most likely expired. To fix this, the Run As setting has to be updated in the scheduler machine. Someone with local admin rights to the machine should go to the System Settings (Alteryx > Options > Advanced Options > System Settings) then click Next until the “Run the Worker as a Different User” menu is selected and update those credentials.
The error above is usually due to a company firewall restriction. If you are receiving the error above when installing, please download run the individual installers from this page: http://downloads.alteryx.com. You can choose admin or non-admin depending on the permissions on your machine.
If you are still having trouble downloading, don't hesitate to open a Support ticket through the Case Portal.
Within an Alteryx workflow you may have instances where you have to hard code a value or only want to run workflows which have a certain field schema.
Both of these cases may cause errors in the workflow when the value or schema differ to that expected. Fortunately, there are two methods you can use to try and circumvent potential errors.
Option 1 - Conditional macros – CREW Macros
Within the CREW macros pack there will be the conditional runner macro. This macro will allow you to set off another workflow if a workflow fails or succeeds. Therefore, if you are running a list of workflows from a Directory Tool and you want all the workflows to run even if a workflow fails, you can use the conditional runner to manage and continue the flow of workflows if one of them errors.
Option 2 - Expecting a set number of records or values?
You can use a combination of the Filter Tool and the Message Tool to look for patterns in the various records or files you are reading in. An example could be you are using a batch macro to read a list of files in. In each of these files you want to ensure a number of fields are included, so you transpose the data giving you a NAME & VALUE. Based on the NAME field you can use a Filter Tool and Count Records Tool to ensure you have the correct number of fields. In the false node you can have the records which didn't meet the expected value in the filter go into a Browse Tool or Output Data Tool so you can check out that file with more detail. Those records which did have the correct field headers you can crosstab the data back and continue that file through the process.
This is just a quick two ways you can look to avoid errors in a workflow, but I encourage those community members who can think of other alternatives and use cases please post below. this topic can be highly valuable for workflow conitnuity so learning more techniques can only help 🙂
The Message Tool within the Alteryx Designer is your own personal car alarm. This tool can provide you warnings or errors when your data doesn't meet a user-specified criteria or it can set up to tell you when data does not match.
The Message Tool can be set up to pick up records before, during and after the records have passed through the tool itself. This makes it useful for evaluating your dataset at different parts of your workflow.
Question I'm getting an error that says "Type mismatch in operator +" in my formula, what does it mean?
Answer This error often means that you're trying to add a number to a string field. If you are trying to concatenate your fields, you need to be sure that all fields being combined are string values. When Alteryx sees a numeric value and a + operator it wants to add the values together as opposed to concatenate.
There are (at least) two ways to resolve this error:
1) Use a Select Tool to change the field type before feeding the data into your Formula Tool
2) Use the ToString() function to convert the field to a String value within the expression itself
Both solutions are demonstrated within the attached v10.5 sample.