Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Connect in Database and using excel file to query data

Highlighted
7 - Meteor

I'm currently using the Connect in Database tool to connect to an odbc database.  That works fine.  I also have an excel file with a list of names.  I need to take that excel file and bump it up against the database and only pull records that match.

 

I can't seem to figure out what tool I need to do this.  It looks like the Data Stream In for the excel file but if it is, I'm missing something.

 

Thank you for your assistance.

Highlighted
11 - Bolide

Hi there, 

 

If I am understanding your question correctly, the workflow should look something like this, using the data stream in tool to write the excel file into a temp table and then joining that to the existing indb tool.

Greg_Murray_0-1580309647436.png

Highlighted
7 - Meteor

That is what I thought but what setting do you use in the data stream in tool to connect to the excel file?  I can't seem to figure out how that connection works?

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Hi @cowannbell ,

 

it should work as you expect:

 

29-01-_2020_15-55-39.png

Connect the Input tool (the tool you use to prepare the data for uploading to database) to the Data Stream In tool.

 

29-01-_2020_15-56-02.png

The Data Stream In tool needs the connection, in addition, you have to decide, if a permanent or a temporary table should be created (for a permanent, a table name is required). Should upload data to your database. 

 

Best,

 

Roland

 

Highlighted
7 - Meteor

Yep, I have an error that says unable to find connection.

 

In the connection name I would have to select  the connection type and when I create a new connection, excel is not an option as a data source.

Highlighted
11 - Bolide

hi @cowannbell

 

The connection name should be the same as the one you use for the Connect In-DB tool. You want to write the excel file into a temp table in the same database as the table you are going to join against.

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

You have to use the connection you already used in the Connect IN-DB tool, there is no new connection required. Excel file is loaded to Alteryx using the Input Data tool and data is moved from Alteryx to the database using Data Stream In tool.

Highlighted
7 - Meteor

I can't write to that database, I can only read from it.

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

If you have no write access to the database (and no chance to be granted), it will not be possible to use the IN-DB tools as you intended. But - it's possible to use the Data Input tool to load the data you need to Alteryx and perform the operation there. Drawback is, that you will need to transfer all records to Alteryx instead of only downloading the needed rows. 

Highlighted
7 - Meteor

Oh, okay. 

 

Thank you for the input.

Labels