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

danilang

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