Excel SQL syntax

[jeffv]

Hi,

Quick question...  when I use the input tool and bring in an excel sheet on the sql editor window it shows a query something like

   Select * from 'Sheet1$'

I'd like to understand a bit more about the sql syntax support by the alteryx driver but can't seem to find anything.

Is it possible to specify field names such as

   select field1, field2, [field with spaces] from 'Sheet1$'?  I tried testing with fields, square brackets around fields, and a few other guess with no success.

Any pointers to understanding the syntax better?

Thanks,

Jeff

[jeffv]

Also curious on syntax allowed in where clause such as

select * from 'Sheet1$' where field1 = 1 and field2 in ('a', 'b', 'c');

I know I can't filter once loaded but on large files I'd rather limit it while importing.  Possible?

[LordNeilLord]

Hey @jeffv

Yes you can write standard SQL syntax when importing Excel sheets, you just need to make sure you import them in Excel Legacy Mode

SELECT * FROM `Orders$` Where `Ship Mode` in ( 'Standard Class', 'First Class')

[LordNeilLord]

Or if you want to select specific columns:

SELECT `Order ID`, `Ship Mode` FROM `Orders$` Where `Ship Mode` in ( 'Standard Class', 'First Class')

[jeffv]

@LordNeilLord,

Thanks for insight!  Is it possible to use the legacy driver with importing multiple sheets from multiple workbooks?  ie one of the features I've found is instead of a specific file being able to say something like *.xlsx.  When I do that the sql editor code slightly changes to just show the tab name `Sheet1$`.  So I assume in that case I could not implement selecting specific fields or adding a where clause... right?

Also just to confirm when selecting fields you surround the field with ` (I believe they are called accent characters?) rather than apostrophes '... correct?

[LordNeilLord · Accepted solution]

Hey @jeffv

If the workbooks have the same schema and the same sheet name then you can use the wildcard input with the SQL.

You're correct that it does change the query when you use the wildcard but you can overwrite this...another thing to note is the "test query" function throws an error but again it still works.

And finally correct again when naming a column or a sheet you need to use the ` (the thing above tab) whatever it is called....when specifying values use normal apostrohe

[Brad2]

@LordNeilLord 

Glad to see the functionality exists, but I can't figure out how to input the SQL. When I go to enter my query in the "Table or Query" row within the configuration panel, there's no option to enter SQL, only select a sheet. If I click on the ellipses, still no option to enter a query. Not sure what I'm missing. 

[Marcin_Koziana]

When I tried this the following error popped out "SELECT `Region` FROM `Match$` is an invalid Sheet/Range specification for Excel". The field 'Region' is present as well as Match tab. SELECT * FROM `Match$` works fine. Am I missing anything?

[FreeRangeDingo]

Does this still work in 2019.4.8?  I am trying to do this and it doesn't appear that I can write SQL against Excel in Legacy mode.  Is there a driver to install separately?

[FreeRangeDingo]

Did you get an answer?  I don't see one.