ExistingListDataProvider

With the ExistingListDataProvider, users can access their own Excel files without having to enter and configure them centrally. To do this, the user selects the Excel file via file selection on their device. A single worksheet is read from the selected workbook.

info

For "managed" CSV or Excel files, which are stored centrally, for example, CsvDataProvider or ExcelDataProvider are more suitable.

Structure of the Excel file

primedocs reads the worksheet row by row starting at cell A1 and imports each row as a record. It does not recognise formatting, titles or empty rows as such. The worksheet must therefore contain a simple, "clean" table:

• Start in cell A1: The table begins in the first row and the first column. Empty rows or columns before it are read as rows or columns of their own. If the first row is empty, it is still treated as the header row when a header row is selected: the columns are then named A, B, C and so on, and the actual headings appear as the first record.
• Header row (optional): Row 1 contains the column headings. Each column should have its own unique heading. primedocs completes empty and duplicate headings, see Empty and duplicate headings. A title or description above the header row is not possible.
• One record per row: Each row below the header row contains exactly one record. primedocs imports intermediate headings and subtotals as additional records, and a record spread over several rows becomes several records.
• No empty rows: Empty rows within or below the table appear as empty records. This also applies to rows that are only formatted (for example with borders or a background colour). Therefore delete unneeded rows below the table completely (for example via the context menu of the row headers) instead of only clearing their content.
• No merged cells: With merged cells, the value is only in the first cell and the other cells are empty.
• Only one table per worksheet: Move other tables, notes or analyses to a separate worksheet. With EnableCustomSheetSelection="true", the user can select the right worksheet.

Example of a correctly structured worksheet:

	A	B	C	D
1	First name	Last name	City	Start date
2	Anna	Muster	Bern	01.03.2024
3	Peter	Meier	Zug	15.08.2025

Hidden or filtered-out rows are read as well. A table formatted as an Excel table (Format as Table) works, provided it starts in cell A1.

With or without a header row

When loading the file, the user specifies with a checkbox whether the table has a header row. By default, it is selected.

• With a header row: The headings serve as column names in the mapping dialog. If a heading matches the Id of a schema field (ignoring upper and lower case), primedocs maps the column automatically, see Interplay with Mapping and Schema.
• Without a header row: Row 1 is already treated as a record. The columns are named A, B, C and so on, as in Excel, and are mapped manually in the mapping dialog.

Empty and duplicate headings

So that every column has its own name, primedocs completes empty and repeated headings:

• Empty heading: the column gets its column letter, for example B.
• Duplicate heading: from the second occurrence on, the column gets a counter, for example Name (2).

If a name formed this way already exists as a heading, primedocs keeps counting, for example B (2).

Example: the header row Name, (empty), Name, (empty), Name results in the column names Name, B, Name (2), D and Name (3). If a schema field has the Id Name, primedocs maps only the first of these columns automatically. The other columns can be mapped manually in the mapping dialog.

Values and formats

primedocs imports the value of a cell, not how Excel displays it:

• Numbers are imported without number format, for example 1234.5 instead of CHF 1'234.50.
• Dates are imported in the format YYYY-MM-DD, for example 2024-03-01.
• Formulas return the value last calculated in Excel.
• Leading zeros (for example in postcodes or personnel numbers) are only kept if the column is formatted as text.

File format

Excel workbooks in .xlsx format are supported, and primedocs Desktop also accepts .xlsm. In primedocs Web, the file may be at most 5 MB. CSV files and the old .xls format are not supported.

In primedocs Desktop, the workbook may remain open in Excel while it is loaded. primedocs then reads the last saved state of the file.

Configuration

The ExistingListDataProvider can optionally be given a name so that users can recognise it more easily (for example "Excel export from CRM"), but otherwise requires no further configuration.

<ExistingListDataProvider DisplayName="Excel Export aus CRM" 
 DefaultSheetName="Daten" EnableCustomSheetSelection="true" AutoAddAllEntries="true"
 SkipMappingIfComplete="true" />

In addition to DisplayName, the following optional attributes can be defined:

• DefaultSheetName: Name of the preselected worksheet in the Excel file. If not defined or if the worksheet is not found, the first worksheet is used automatically.
• EnableCustomSheetSelection (true/false): Displays a drop-down menu to the user for manual selection of the worksheet.
• AutoAddAllEntries (true/false): Automatically adds all entries in the list so that the user does not have to select them manually.
• SkipMappingIfComplete (true/false): Skips the mapping dialog if all fields can be mapped automatically, i.e. if the columns in the Excel file match the configured Mapping or Schema.

If the worksheet read contains no record (for example an empty sheet or one with only a header row), primedocs Web reports "The sheet contains no entries." and primedocs Desktop "The file could not be read. […]". This can also happen if the worksheet from DefaultSheetName is missing and primedocs therefore falls back to an empty first worksheet. Then select another worksheet (with EnableCustomSheetSelection="true") or load a different file.

Interplay with Mapping and Schema

As with the other providers, the imported columns are mapped to the Schema of the Object or ObjectCollection via a Mapping. If all schema fields can be mapped automatically based on the column headers, the mapping dialog can be skipped with SkipMappingIfComplete="true". Otherwise, the user performs the mapping in the mapping dialog.