Suggestions for improvement to Import Filter

Discussion of issues, suggestions and bugs of Devart Excel Add-ins, our product line for connecting Excel to external data from cloud applications and databases
Post Reply
hansi9990
Posts: 3
Joined: Thu 11 Apr 2019 05:36

Suggestions for improvement to Import Filter

Post by hansi9990 » Thu 11 Apr 2019 05:49

It would be nice if it were possible to save the filter settings in connection with the database and the object in profiles.
It would also be good if there is another option "New workbook" in the window "Preview Data" in the Excel Import Options.

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: Suggestions for improvement to Import Filter

Post by Pinturiccio » Mon 15 Apr 2019 16:33

hansi9990 wrote:It would be nice if it were possible to save the filter settings in connection with the database and the object in profiles.
If the "Allow saving add-in specific data in Excel worksheet" check box is selected in the connection editor window during import, then the select query with filters is stored. In this case, when you click Get Data on an imported worksheet, we show the previously used query with filters.
hansi9990 wrote:It would also be good if there is another option "New workbook" in the window "Preview Data" in the Excel Import Options.
We will investigate the possibility to create a new workbook when data is imported and post here when we get the results.

hansi9990
Posts: 3
Joined: Thu 11 Apr 2019 05:36

Re: Suggestions for improvement to Import Filter

Post by hansi9990 » Tue 16 Apr 2019 18:49

Cool, thank you. :)
Another Question: Is there a filter possible to remove dublicates? (that only the older entries are stuck when sorting in ascending order for a defined column)

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: Suggestions for improvement to Import Filter

Post by Pinturiccio » Sat 20 Apr 2019 15:38

It is not possible to achieve such results via filters. However, you can use your custom query to select the oldest results for duplicate fields. Let's consider the following example:
1. We have a field date_f, where the date when a record was created is stored.
2. We have a field Name, which can contain duplicates.
3. We will use table name TESTING for this example.
4. If the Name field is duplicate, then select the record with the oldest date_f.

The result query should be the following:

Code: Select all

SELECT t.*
  FROM TESTING t
  INNER JOIN (SELECT Name, min(date_f) as MinDate from TESTING GROUP BY Name) temp
  on t.Name=temp.Name and t.date_f=temp.MinDate
You can write your own query in Excel Add-in on the "SQL Query" tab: http://prntscr.com/neju8j

Post Reply