How is Data Provider supposed to work?

[jack_sprat]

I created a target database range ‘importHere’, using Data > Define Range…, and set up Data Provider to import CSV data via http request, and can retrieve data to the GUI (and configure column separators), but Start > ‘Apply and quit’ just appears to quit, with no effect.

I can’t copy-and-paste the data; and there appears to be no ‘transfer to spreadsheet’ button. And the help documentation entry is currently waiting to be written…

has anybody got it to work?

LO Calc Version: 6.3.5.2 (x64)

thanks - JS

Edit: Date Time transformation tool seems to work ‘sometimes’ - I tried using a semi-colon separator and Date Time worked, but I couldn’t split the remaining columns, whereas, with a comma separator, the columns automatically separate, but DateTime transformation doesn’t appear to work.

Edit #2: upgraded to LO v6.4.2.2; The Start and Column menus are now gone, so I don’t have to fret about the Date Time transformation and separator configs not working. ;-(

[anon73440385]

I’d assume you want to use Sheet -> Link to External Data to import csv data from a website. This functionality has been introduced in LibreOffice 6.1 - see also LibreOffice Release Note 6.1 - Link to External Data supports CSV

[jack_sprat]

That certainly looks like a good solution to my specific use-case, but doesn’t address the question re. what Data Provider is intended to do, and how to use it.

[anon73440385]

but doesn’t address the question

That’s why I commented and did not provide an answer

[jack_sprat]

bug report submitted:

• missing menu items

[Ratslinger]

Hello,

I have no claim to understanding much of any of this but do have some initial results for those who wish to mess with this.

Started with new Calc file. Then defined a Range. Then opened Data Provider, selected my range, selected .csv and pointed to my local CSV file. Pressed Apply Changes and got:

Then from menu selected Start -> Apply & Quit and got:

Which is the data from the csv file and placed in the specified range.

Closed the Calc file. Changed the csv filed data for ID 3 under DATA from ccc to xxx, saved the csv file and opened the Calc file getting:

Change is applied.

So Data Provider is working at least to some extent. There must be much more to this but this will require more testing than what I have done.

[jack_sprat]

Thanks @Ratslinger - you got it to do more than I can. What version are you testing? I can’t modify the Database Range field (my defined range doesn’t appear in the dropdown, and the field is only ~5 characters long - see screenshot in edited Q).

[jimk]

@Ratslinger: You figured it out! First I tried creating a named range, but that didn’t work. It may be helpful to edit your answer and tell how to define the range, as that is the key we were missing. Help docs: Defining Database Ranges, Using Data Ranges

[Ratslinger]

@jack_sprat - I am using LO v6.4.3.2 from TDF (The Document Foundation) on Ubuntu 18.04 Mate. It appears your 2nd edit is missing the essential menu items - Start and Column

@jimk

You figured it out!

It happens some times but as stated it is certainly preliminary and only the FIRST steps. Can create crashes in many ways but have also done more with column conversion and other menu items. Promising. Long way to go. Would like to see progress in some of the other ‘Data Provider’ selections and not just .csv

As for the range, used the “Defining Database Ranges” method to create the range.

[jimk]

There is no information about Data Provider currently: how to use calc "data -> data provider" to connect on sqlite db

Pressing “Apply & Quit” runs a method named import:

void ScDataProviderDlg::import(ScDocument* pDoc, bool bInternal)
{
    sc::ExternalDataSource aSource = mxDataProviderCtrl->getDataSource(pDoc);

    for (size_t i = 0; i < maControls.size(); ++i)
    {
        ScDataTransformationBaseControl* pTransformationCtrl = maControls[i].get();
        aSource.AddDataTransformation(pTransformationCtrl->getTransformation());
    }
    if (bInternal)
        aSource.setDBData(pDBData->GetName());
    else
    {
        aSource.setDBData(mxDBRanges->get_active_text());
        if (!hasDBName(aSource.getDBName(), pDoc->GetDBCollection()))
            return;
        pDoc->GetExternalDataMapper().insertDataSource(aSource);
    }
    aSource.refresh(pDoc, true);
    mxTable->Invalidate();
}

To me, it looks like it’s intended to import data into the document. However, nothing seems to happen.

EDIT:

It works as expected when a database range (not a named range) is defined. See @Ratslinger’s answer.

[jack_sprat]

thanks @jim-k; I did think of looking at source code, but had no idea where to start - can you give a pointer as to how to track down the right file to start investigating, or a link?

[jimk]

Well, I cloned the source repo and then used command line tools to search for “Apply & Quit” (only occurs in one place) and “Data Provider” (lots of results). Additional searches eventually led to the import method shown in my answer.