Difference between revisions of "Data import and export"
old>Admin m (moved Integration/CSV to CSV file import and export) |
m (Kpe moved page CSV file import and export to Data import and export) |
||
| (4 intermediate revisions by 2 users not shown) | |||
| Line 1: | Line 1: | ||
From the standard user interface its possible to both export and import CSV data files. | From the standard user interface its possible to both export and import CSV data files. | ||
== Exporting | == Exporting spreadsheet data == | ||
Data exports can be done in a blink of an eye | Data exports can be done in a blink of an eye | ||
| Line 16: | Line 16: | ||
* System export: Use field system names in columns (good for later insert/updates) | * System export: Use field system names in columns (good for later insert/updates) | ||
Note that the | Note that the export allow you to build VERY large data sets, so be carefull when selecting the "All records" option. | ||
== Importing | == Importing spreadsheet data == | ||
In list mode of an solution click the Package import icon. | In list mode of an solution click the Package import icon. | ||
Latest revision as of 13:42, 9 November 2024
From the standard user interface its possible to both export and import CSV data files.
Exporting spreadsheet data
Data exports can be done in a blink of an eye
- In list mode of an solution click the XL sheet icon.
- Choose relevant export options
Note that the export provided respects currect filters, sort order and grouping.
The export options are as follows
- All records: Include all records in range
- All fields: Include all exportable fields
- Explode parents: Include fields from parent records (default in list)
- Explode children: Include fields from child records in denormalizaed format (default in list)
- System export: Use field system names in columns (good for later insert/updates)
Note that the export allow you to build VERY large data sets, so be carefull when selecting the "All records" option.
Importing spreadsheet data
In list mode of an solution click the Package import icon.
Columns in the import file will be matched to fields in the active solution. Preferrably system field names should be used, as these are guaranteed to be unique. A template for the data can asily be built by selecting the "System export" option during export of existing data.
The result of an import operation is another CSV with response on each line in the file, either
- SUCCES
- FAILED [reason why]
In case of inserts an DataID column is also added to the response.
Tip: In case some lines fail. Just remove the lines with SUCCES, correct the faulty ones and try again after saving changes to the response file.
New data (insert)
New records will be created in accordance with matched fieldnames in the file. Missing values will be set to eiather the field default value or simply declared empty.
The presence of "DataID" column will make the import fail (countermeasure againt human errors during imports).
Changes to existing data (update)
Existing records will be mached using one of the following methods
- Lookup by column named "DataID" in the import file
- Match by database lookup of all fields marked with "Is part of a unique key"
On the matching columns present in the file will be updated. Unmatched values will simply hold on to the value they had in the latest revision.
Understand how imports work
Data inserts and updates are carried out by a system user, that has exactly the same priviledges as a normal user. If field permissions are missing field value changes will be ignored, and if restriction is not met the operation will fail as normally. Finally all dynamic effects are the same, including status actions, codeunit execution etc. The later can however be disabled explicitly when uploading data to the server.