Data Provider for Spreadsheets

Imports data from data sources. At present, the supported external data formats include CSV, HTML, XML, and LibreOffice Base files.

Data may be imported from either local storage, such as CSV files, or from external sources, such as HTML Web Pages.

Additionally, data can be manipulated using various transformations before being loaded into the sheet.

Per a accedir a aquesta ordre...

Des de la barra de menús:

Trieu Dades ▸ Proveïdor de dades.

Des de la interfície en pestanyes:

On the Data tab, choose Data Provider.

Des de les barres d'eines:

Proveïdor de dades

Database Range

The database range to receive the data from the provider. Choose the range from the available dropdown list.

Format de dades

El tipus de dades que s'importaran.

URL

L'URL del proveïdor de dades. Si el proveïdor és un fitxer local, introduïu-ne el camí i el nom. Si és un servei web, introduïu-ne l'URL.

Identificador

The target ID for HTML provided data or Xpath for XML provided data.

Transformacions

The provided data can be transformed using the available transformations from the dropdown list. You will need to provide further information depending on the transformation. For example, you can set the sort order for the sort transformations.

Delete Columns

Deletes columns listed in Columns. Enter the list of columns indexes separated by semicolons.

Delete Rows

Deletes rows containing the value entered in Lookup value entry and found in Column index number.

Swap Rows

Swap row position between the first and second rows.

Split Columns

Separates the column in two on the occurrence of the separator string.

Merge Columns

Merges columns adding the separator string between each merged value.

Transformació de text

Transforms text data in the list of columns.

To Lower: transforms all uppercase characters to lowercase.
To Upper: transforms all lowercase characters to uppercase.
Capitalize: capitalizes the first character of each word.
Trim: removes spaces from the data, leaving only a single space character between words.

Sort Columns

Sorts rows ascending or descending by the values in the column index number.

Funcions d'agregat

Adds the result of an aggregate function to the bottom of the column.

Sum: the sum of the column data.
Average: the average value of the column data.
Maximum Value: the maximum value of the column data.
Minimum Value: the minimum value of the column data.

Numeric

Applies numeric functions on columns.

Sign: returns +1 for strictly positive numbers, -1 for strictly negative and 0 for zero values.
Round: rounds numbers to a certain number of decimal places.
Round Up: rounds numbers to the nearest upper integer.
Round Down: rounds numbers to the nearest lower integer.
Absolute value: returns the absolute value of the numbers.
Log with base e: returns the natural logarithm of the numbers.
Log with base 10: returns the logarithm base 10 of the numbers.
Cube: returns the cube of the numbers.
Square: returns the square of the numbers.
Square Root: returns the square root of the numbers.
Exponent: returns the exponential of the numbers (e^x).
Is Even: returns +1 is the number is even, 0 if the number is odd. Decimal numbers returns 0.
Is Odd: returns +1 is the number is odd, 0 if the number is even. Decimal numbers returns 0.

Replace Null

Replaces null or missing data in the list of columns with the supplied text.

Data i hora

Performs date and time transformations on data in the list of column indexes.

The date and time values depends on the locale settings.

Date String:
Year: returns the year of the date.
Start of Year: returns the date value of the first day of the year.
End of Year: returns the date value of the last day of the year.
Month: returns the month number of the date.
Month Name: returns the month name of the date.
Start of Month: returns the date value of first day of the month.
End of Month: returns the date value of the last day of the month.
Dia: retorna el dia del mes.
Day of Week: returns the day of the week.
Day of Year: returns the day of the year.
Quarter: returns the quarter number of the date.
Start of Quarter: returns the date value of the first day of the quarter.
End of Quarter: returns the date value of the last day of the quarter.
Hour: returns the hour of the date.
Minute: returns the minute of the date.
Second: returns the second of the date.
Time: returns the time of the date.

Find and Replace

Finds and replaces values in Column index number.

Afegeix

Click Add to include the selected transformation to the transformation set. Transformations are included at the bottom of the list.

If the order of transformations is important, plan the sequence of transformations in advance.

Suprimeix

Suprimeix la transformació de la llista.

Aplica

Applies the transformations to the provided data and displays results in the preview area for inspection. The data is not loaded in the spreadsheet until you press OK.

The data formatting can be set with a right-click on the preview table column headers. The corresponding database range column is then selected. Format the cells with the context menu.

Actualitza el proveïdor de dades