Basic Input questions and some errors

Question

Hi,

I have a few basic questions but seems did not find related answers from the community, hope everyone can help!

1. When we input an excel file, there are two options (one with $sign and one without dollar sign). I actually tried and found the one without $ sign consist of less rows of data. But what is the real difference and why some of the excel will not have two option?

2. Is there are way to check the content of field and find out the special case? For example, a ID field consist of 5 digit numbers but somehow there are some wrong input with other charactors. Field summary seems can do similar thing but not exactly.

3. When I use "Union"/"Join" tool, sometimes it will prompt an error "unhandled field conversion", but when I removed and re-drag an union tool, the error goes. How can I handle this?

4. When I revise the path to relative path, sometimes it cannot get the correct directory and need to point to that file with full path once. Then all other input can get the same directory, is there a way to get it directly?

These questions can be skipped becase the work around is too easy to do but I really want to know the actual casue and solutions.

Thanks :)

MikeB · Accepted Answer

I can answer your question #1

Before we wrote our own Excel driver we used the Microsoft Access 2010 driver to do all Excel reading and writing (of xlsx files).

One of the things that driver did that caused confusion like what you're seeing is that when you wrote a Sheet to a file, the driver actually wrote both a sheet and a named range with the same name!  The named range defines the *original* rectangle of the data written when the Sheet was created. So, if you later add more data to the sheet, the named range doesn't reflect all of the new data. Our new driver does not do this, so when you write a sheet you get only a sheet now. However, there are still lots of files out there that were written with the Access driver. We still support that driver as "Excel Legacy" in the format list.

So, when you specify Sheet1$ vs Sheet1 you are actually identifying the sheet itself (with the '$') and the name range (without the '$').

In Excel you can see these named ranges by going to the Formula Tab and clicking on the Name Manager. That will show you all of the named ranges defined in the file.

There is also a dropdown list above cell A1 that will contain the named ranges. If you select a named range from the dropdown list, Excel will select the cells that make up the range. I would bet that if you did this, you'll see that the original named range only includes a subset of your present data.

You can manually delete the named range in the Name Manager and then back in Alteryx you will only see the sheet (with the '$' on the end).

I hope this helps clear up your first question.

jdunkerley79 · Accepted Answer

For #2, there are various approaches. I think the most flexible is to use the Regex tool. The RegEx tool are is powerful but can be a little daunting.

If you use it in Match mode and use the expression \d{5} it will return true. You can then feed this into a filter tool.

Have attached a simple demo.