Run SQL query while connecting to Excel / CSV file

[oleksandr_titorchuk]

Hi All,

Is it possible to use SQL syntax while connecting to Excel or CSV file via Input Tool?

I know that you can adjust XML for Input Tool to something like:

<File OutputFileName="" RecordLimit="" SearchSubDirs="False" FileFormat="25">File.xlsx||| Select * From `Selections$A1:C5`</File>

But I am rather interested in more complex query like:

<File OutputFileName="" RecordLimit="" SearchSubDirs="False" FileFormat="25">File.xlsx||| Select [Column1], [Column2] From [Selections$] WHERE [Column1] = "5"</File>

 Thank you.

KR,

Oleksandr

[oleksandr_titorchuk]

Hi All,

Is it possible to use SQL syntax while connecting to Excel or CSV file via Input Tool?

I know that you can adjust XML for Input Tool to something like:

<File OutputFileName="" RecordLimit="" SearchSubDirs="False" FileFormat="25">File.xlsx||| Select * From `Selections$A1:C5`</File>

But I am rather interested in more complex query like:

<File OutputFileName="" RecordLimit="" SearchSubDirs="False" FileFormat="25">File.xlsx||| Select [Column1], [Column2] From [Selections$] WHERE [Column1] = "5"</File>

 Thank you.

KR,

Oleksandr

[danilang · Accepted solution]

Hi @oleksandr_titorchuk 

The Input and Dynamic Input tools don't support SQL syntax when connecting to .xlsx files using the built in .xlsx driver.   You're limited to Sheet Name(or Named Range), Start Data import on Line X and row Limit.  They import the entire range of data defined by these parameters and then you can use the various Alteryx Tools to recreate what your input SQL would have done.

If you really need to use SQL when connecting to .xlsx file, you can define an ODBC data source that points to the file and the use the Generic ODBC driver to read the data.

Dan

[oleksandr_titorchuk]

Thank you, Dan!

[jiuh_park]

It is possible for xlsx. Just open the xlsx file in legacy mode and click the 2 dots on item3(Table or Query)

There you can use the SQL Editor to query against xlsx

Beware that you have to put back tick and not single quote around column name

Refer to the below link for an example using where statement

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Excel-SQL-syntax/m-p/158757/highlight/...