Filters gone / lost after each External Data Update

office_libre454 · October 6, 2021, 8:31pm

I used the ‘Linked to External Data…’ option to import data from a CSV file with financial data I download each month to make a report.

I applied some filters to only see a specific month (and filter the older data).

When the CSV file changes, because it was updated with newer data, and I click ‘Allow Updating’ in the orange bar shown each time I open the ODS spreadsheet file, the filter is gone, and I did lose my work setting these filters up. And I have to start all over again.

It seems to be a bug, because when I click again on the ‘Filter’ icon and look at the date column it does recognize the dating format of the earlier period, but shows the new data unformatted.

With ‘new’ data I mean, in the new version of the source file (the CSV) each month it contains the old data + the new data from the new period.

Instead of just reloading the same CSV with some new rows of data, appying the same filters I lose lots of time creating a whole new spreadsheet each month this way.

I searched online but could not find any clue or solution for this problem.

This happens with 2 different CSVs I work with supplied by two different companies, so the problem cannot be the source file.

Any ideas how I can fix this?

office_libre454 · October 6, 2021, 8:42pm

I think I found the issue. If I enter text in the columns besides the CSV filled data it stops working, but if I do not use the empty columns the data updated correctly and the filters do not get lost.

Is there any error log, or general log that I could see what is going on durin CSV import and recognize these errors and fix them?

erAck · October 6, 2021, 9:11pm

Create a “database” table range, at which the filter settings can be remembered. Menu Data → Define Range…

If you just set an AutoFilter it is remembered at an anonymous database range, that may be temporary and get discarded if the data area changes and linked data is reloaded/updated.

Villeroy · October 6, 2021, 9:31pm

There are 5 different ways to link csv in Calc and you have chosen the one which was intended to be used with tabular html pages.
All csv data are database data.
Collect your csv files in a dedicated directory.
menu:File>New>Database…
[X] Connect to existing database
Type: Text
Specify the csv directory and the import settings
[X] register the database
Save the database
Each csv file is displayed as a table in a read-only pseudo-database. This is just a connect to the csv files. There are no data stored in the database document.
WIthin the database you can define as many filters and sort orders as you like and store them as queries.
With a few mouse clicks you can produce nice looking reports.
With forms you may show related data of multiple tables (csv files).
Finally, you can use your csv in Writer and Calc. When linking tables or queries in Calc, you have many advantages that are not availlable with data that are saved in Calc.
[Tutorial] Using registered datasources in Calc

You download the file and replace the old version with the new one using the same file name. Any filtering/sorting queries stored in the database will work with the new data.
In Calc menu:Data > Refresh will update the import area including all references adjacent formulas, charts etc.

office_libre454 · November 16, 2021, 12:51am

Thank you so much for your help.

I got stuck with getting the Base data into Calc because Ctrl+Shift+F4 minimizes the screen and I cannot seem to find the way to get the data into Calc.

On this page they explain Ctrl+Shift+F4 formats the Standard currency format: Shortcut Keys for Spreadsheets - LibreOffice Help

And when I just use F4 it states that: no cell references are found in the selected cells.

Update: I think I found it. I dragged the query into the Calc spreadsheet. Then went to View > Data Sources and when I clicked it, it does show the database connection so I assume it will update it now if there is new data. Somehow the Ctrl+Shift+F4 does not work on my machine.