Pasting from Excel to Text Input extremely slow (crashing Alteryx)?

[ylamlih]

Hello Community! My first post so apologies if I'm making (or have already by posting this) any rookie mistakes 🙂

I'm working on my first workflow which is being used on a weekly basis and looking to find a way to share this workflow with other users, which is currently difficult as it is pulling several files from my local computer.

In order to get rid of the many Input Data tools I've got in order to drive the various "lookups" I used to do in Excel, I figured the easiest thing would be to turn them into Text Input tools so all is available from within the workflow.

I quickly found that Alteryx lets you copy and paste cells from Excel directly into the Text Input, and the first test with roughly 200 rows and 2 columns seemed to go fine aside from it looking a bit hard for Alteryx to do (took about 12 seconds).

The second run, with 41k rows and 2 columns, is still running at the moment which it has for over an hour now :(, I can see from my task manager that Alteryx is still using between 20-40% of my CPU so it does seem to still be processing my copy paste request and hasn't actually crashed (yet).

Anyone has any tips for me as to how I should be doing this differently as copy pasting from Excel into Text Input does not seem to be the most ideal way to get data into Alteryx.

Thanks in advance!

[TomWelgemoed]

Hi @ylamlih ,

I think the copying & pasting function is really only for small amounts of data.

When you have large datasets, save them into a file to a place you can access with Alteryx, and then simply drag & drop that file into the canvas. That should work way faster!

Best,

Tom

[ylamlih]

Thanks for your quick response Tom!

I see.. is dragging and dropping onto the canvas not leaving me with the same issue of not being able to share the workflow with someone not having access to these "lookup" files?

So what I'm trying to overcome is for Alteryx having to get data from a specific location (that being either my local pc or any shared drive location), and rather just have the data available within the workflow.

Apologies if that is exactly what you're suggesting and I'm missing your point here.

[grossal · Accepted solution]

Hi @ylamlih,

that's an interesting issue you figured out. It's quicker on my pc but it still takes "forever" (a couple minutes with 50k lines).

Here is how you could work around this issue:

Relative path

Instead of using absolute path, you should provide relative paths and send the whole folder to your users.

How can you do this?

Input path: ".\example.xlsx" tells alteryx that the example file is in the same folder as the workflow file is. You could also create a "Data" folder next to your workflow and use ".\Data\example.xsx" to have a "clean view" for your users.

Let me know if this helps.

Alex

[VianneyM]

hi @ylamlih,

Thank you for joining to the community. 

The text input tool has a limitation for the import  "The Text Input tool is limited to 1000 total cells on import" (you can find this information within the help page of Alteryx)

https://help.alteryx.com/current/designer/text-input-tool

For such a big files I recommend that you import your data using an input data tool https://help.alteryx.com/current/designer/input-data-tool

Best,

Vianney

[ylamlih]

@grossal,

Thanks for the tip, this is definitely a workaround I could live with seeing the lookup files are not that big, and with Alteryx taking .2 of a second to input data, not having the data within the workflow is not that big of a disaster I guess 🙂

[TomWelgemoed · Accepted solution]

Hi @ylamlih ,

Another tip if you want to share your workflow is that you can "Export" it, so that it includes the files.

You can do this by:

1. Save your workflow

2. Go to Options -> Export Worfklow. Choose the location where you'd like to save this

3. Send the saved file (.yxwz extension) to the user in question. I believe there are security/encryption options if necessary.

This option is more a developer sharing method than a scalable solution for collaboration. If you wanted multiple persons to be able to run a workflow, I think @grossal 's idea is more the right direction.

Best,

Tom

[grossal]

Hi @VianneyM,

it would be good if the software wouldn't allow it. In the current version (2020.1) it is definitely possible to input more than 1000 lines of data using the Text Input with both ways:

- copy-pasting data from Excel

- Inputting it manually

I have done some tests and it also seems like inputting more lines does not scale linear.

	Laptop	Desktop
1000	< 5 sec	< 5 sec
2000	< 5 sec	< 5 sec
5000	00:12	< 5 sec
10000	00:38	00:13
50000	07:51	05:27

I totally agree that it shouldn't be possible to input more than 1000 lines, that would be bad style for sure but it's currently possible. Is this a bug of 2020.1 or a general issue? 

Regards

Alex

[ylamlih]

Hi @TomWelgemoed,

Had no idea this was possible! Definitely a solution I could use as well 🙂