Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Excel SQL syntax

jeffv
8 - Asteroid

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

9 REPLIES 9
jeffv
8 - Asteroid

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
15 - Aurora

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')

 

ExcelSQL2.PNG

LordNeilLord
15 - Aurora

Or if you want to select specific columns:

 

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

ExcelSQL3.PNG

jeffv
8 - Asteroid

@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
15 - Aurora

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

 

ExcelSQL4.PNG

Brad2
7 - Meteor

@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. 

 

Config screen.PNG

Marcin_Koziana
5 - Atom

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
11 - Bolide
11 - Bolide

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
11 - Bolide
11 - Bolide

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

Labels