285
Hello,
How can I put a date filter on the odata uri when using Excel?
I can make a connection and get the G/L Accounts into Excel. But I also want the user to be able to make date filters.
But when I try to add ?filter= to the connection command text Excel gives an error "We cannot find the table 'G/L Account?filter'... in the datamodel.
Is it possible to make an uri filter on the odata connection in Excel?
*This post is locked for comments
hi,
which excel version do you use?
did you try to import using "Data tab" --> "Get External Data" --> "From Other Sources" --> "From OData Data Feed"?
i tried additional following (using excel 2010):
* On the Data tab --> From Web: giving odata url --> imported --> got a ready to use table with the received data
* "Data tab" --> "Get External Data" --> "From Xml Data": giving the odata url --> imported --> resulted in a textual import of the resulting xml doc.
Hi Jonathan,
thank you for helping.
I understand that I can make filters in the url and that works just fine in the brower in my setup as well.
But I cannot use these urls in Excel. I get an error saying "The table does not exist".
NB: I am not using Power Query.
thank you
Anders
hi,
i published the Item List as webservice and tried the following sample "all items with last modified date equal to 2013-11-07":
localhost/.../Company('CRONUS%20AG')/ItemsList?$filter=Last_Date_Modified eq datetime'2013-11-07'
when executed in the browser it is translated to:
localhost/.../Company('CRONUS%20AG')/ItemsList?$filter=Last_Date_Modified%20eq%20datetime%272013-11-07%27
this worked fine.
the same with "greater than" instead of "equal":
localhost/.../Company('CRONUS%20AG')/ItemsList?$filter=Last_Date_Modified%20gt%20datetime%272013-11-07%27
worked also fine
Hi Tharanga,
thank you for the response.
I still do not see a solution in the links you sent.
If the dataset is large, then I want to make filters before I retrieve the data.
For example this uri does not Work in Excel.
soda.demo.socrata.com/.../4tka-6guv Islands region&source=pr
Excel will give the error that the Table 4tka-6guv?region does not exist.
1. I cannot add the uri to the connection string
2. I cannot add the 4tka-6guv?region=Virgin Islands region&source=pr" part in the command text.
Thank you
Anders
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,214
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (