How to push en Excel file as a temporary table in a database and then use it in an query

[matyohan]

Hello, 

I want to push en Excel file as a temporary table in a database (Snowflake for e.g.) and then use in an Input tool. 

My objective is to use this temporary table to limit the scope of a big query. 

Thanks for your help, 

Best regards,

matyohan

[Zok]

Hello,

If it is always the same kind of Data, I suggest you create a table that will be used for that purpose (not temporary then) since creating a table in Alteryx can be tricky (key columns, format...) and you will need to elaborate a macro to automatically create and adjust a new table fitting a new type of data to use it after.

Once that table is created, input your data with INPUT DATA

Then export them to your table with the OUTPUT DATA module. Select "Write to file or Database" /  "Data Sources" and chose Snowflake or other.

Then add the name of the Table (verify it after, it should be the same format as a sheet for Excel (ex: connection_name|||table_name).

Then chose "Delete Data and Expand", then you won't need to delete the table yourself to clear it.

Then use your table_name as a join in the query.

If needed, between input and output, you perhaps will need to put a recordID to have unique lines, then don't forget to create that column in the table to host that Key Column.

[alexnajm]

Hi @matyohan,

You could use the Data Stream In tool to load it as a temporary table, then use the In-DB tools afterwards to build your query!

Data Stream In Tool | Alteryx Help

-Alex

[matyohan]

Thanks for your help. To be more precise, I need to do these two actions in the same workflow.

@ZEL, the datatype can change.

@alexnajm, I don't know how to have an Input tool after a Data Stream tool in a same workflow, is it possible?

[alexnajm]

@matyohan since it's in the same workflow, you don't need another Input Data - just use the anchor after that Data Stream In tool as your data going forward.

[matyohan]

@alexnajmI need to use this temporary table (for example, a list of customers) in the join of an sql query and avoid loading all the records

[alexnajm]

Right, so do something like this:

The Connect In-DB would just be the other table!

[Zok]

You can use Join inDB right after the Data Stream in

Or you can Create a table (not temprary then) and use that name in the query

You can use the same name every time and use the overwrite/drop mode, then your table will be updated with new data

[alexnajm]

@matyohan for clarity, the Connect In-DB tool won't read in any records (unless you put down a Browse In-DB tool) - the In-Database tools are helping to build a query and when it's done it will execute against the database it's connected to. So this solution would work to limit your database on the Excel file!

[alexnajm]

@matyohan make sure to accept one or more of the responses as a solution if this answered your question - thank you!