Cannot read input data until I put a Browse tool

[maxlo]

Hello all, I encounter a problem when I input and load an excel file in Alteryx using the input tool. 

In the configuration panel, it just shows 1 record of data, which is not the real case of over 50,000 records in the source file.

However, I can read all the data when I add the browse tool. You may refer to below screenshots.

Input tool:

Browse tool:

Can somebody figure out what is the problem? I cannot use any of the functions and tools if I cannot read the data in the workflow.

Looking forward to some of the expert's responses and thanks a lot for your kind help!

[LukeG]

@maxlo 

Good question. I notice in the screenshots, the results window indicates that there are 2638 fields.

That is a very wide dataset, and the Alteryx designer is minimizing the time it takes to process a workflow and minimizing the data that has to be stored in memory by only showing a subset of your data. In this case it is 1 record.

If you would like to see all (more) of your data without having to use the browse tool, I would recommend only selecting the fields that are necessary to your analysis. This way the Alteryx Designer will be able to show you more data without using all of your computer's memory.

Try selecting only the important fields and see what the result is. Hope this helps. Let me know if you have any questions!

-Luke

[BrandonB]

If you take a close look at the results window there are more than 2,600 fields being pulled in. The most likely reason for this is even though there isn’t data in those columns in your excel file, there is probably formatting in them so therefore they are being pulled in. My suggestion is to highlight the columns in Excel to the right of your data and clear formatting in Excel and then run the workflow again.

The second part I would like to explain is the behavior of the right hand anchors of tools compared to the browse tool. The right hand anchors of tools show a 1MB snapshot of your data rather than the full set. The browse tool shows the complete result. Your data has so many columns that they are taking up the 1MB in preview space so therefore it only is showing one row.

Long story short, clearing the formatting on those empty cells in your Excel file should stop them from being read into the workflow and you will see many more rows in the 1MB data preview in Alteryx.

I hope this helps!

[maxlo]

Hi Brandon, thanks for your replies. Maybe in the screenshots it is not presented clearly that, actually there are actually data in all the 2638 fields (but just not all the records/rows have all the fields filled in). Therefore, I think your way of formatting the source document is not possible. Would there be other reasons for this? Thanks!

[BrandonB]

You can increase the default value of 1MB shown in each anchor by going to the options menu and changing it in user settings, but this is done for performance reasons because it will show you the increased data volume at every tool anchor if you do so.

Therefore, you can either change the data preview that is displayed for all tools, or you can leverage the browse tool to display all data where needed. 

What is the reason for the large number of columns? Would you be able to use the transpose tool found in the transform category to reorient this data to rows rather than columns?

[danilang]

Hi @maxlo 

The limitation on the number of rows displayed doesn't stop you from actually building the workflow.  When you add new tools to the workflow, the entire list of field names will be available in the configuration panels.  

If you're using the results window as a guide to help you analyze the fields and data, you might want to consider making a copy of the file(so excel doesn't lock the one your working on) and opening it in Excel.  Display this on a second monitor if you have one. 

Dan   

[ries9112]

It seems like you want to keep all the fields from your original data, but for anyone who runs into an issue where Excel may have detected a ton of fields that are junk and need to be removed, I just wanted to point out a functionality I missed for longer than I would have liked; you can highlight all of the fields you want to remove and use the option Deselect Highlighted Fields rather than clicking on each checkbox individually:

The question seems answered, but here are a couple of screenshot for additional clarity. Whenever you view the results without the browse tool, you will be able to see the top rows only up to a pre-defined amount of MB, which usually ends up looking something like this:

In this example, only the first 6,832 rows out of the total 9,180 are being shown because that's how many rows fit within Alteryx's memory limit for the results preview shown on the anchors.

However, because you have 2638 fields, it seems that two rows would already be past the memory limit, and Alteryx therefore only shows the one row. If you ever wanted to override the system settings for the memory limit per anchor, first open your User Settings:

From the User Settings window go to the tab that says Advanced:

From here you can change the memory limit or disable the limit completely (not recommended).