Import Data

Import Data provides knowledgeable users with the ability to add records to Deacom tables. This function should be used with caution, and the assistance of Deacom support, as the imported records are checked only against database constraints and may not be compatible with data created and maintained by the main application.

Security exists to control access to this transaction.

Before performing any import, it is recommended to fully test the import in a training environment with a current copy of Production data. It is also recommended to backup Production databases before importing data.

Calculations for imported data will not fire and all checks, including defaults, will be bypassed. To reapply calculations and checks, each piece of data must be re-saved individually after import in the new location.

Any technical support assistance to fix failed data imports is considered a billable project.

System Navigation

Tools > Import Data

Import Data pre-filter

Button/Field	Description
Report Type	Choose Standard
Profile	Option to select a specific Profile to filter
Profile Starts With	Option to filter based on the first few characters of the Profile Name
Display	Filters profiles based on a status of Active, Inactive, or All
Expired	Filters for Expired, Non-Expired or All Profiles

Customer Criteria tab

Custom criteria allows users to search using fields not normally found on the pre-filter form.

Edit Data Profile form

Opened via the "New" or "Modify" buttons.

Edit Data Profile buttons

Button	Description
Import	Used to bring in a data import Profile. Clicking the icon will open an Import File dialog box. You will need to click the folder icon on the far right, which will open a dialog box allowing you to navigate and select the correct file. Once selected, finalize the import by clicking the Import icon on the top left.
Export	Click to export the Data Profile to be saved or imported into another system.
Save	Saves the current Profile.
Undo	Will undo recent changes made to the Profile.
Load	Displays the results of the mapped data from your sources in the Records tab.
Run	Runs the Profile and will attempt to insert or update the data as directed. Users will be prompted with an option to show any exceptions (if applicable) and if desired, export the list to Excel and save locally. The user prompt is "Do you want to see Exceptions now?"
Clear	Clears the current Profile.
Template	Creates a spreadsheet with all the fields from the Destination table showing which fields are required and what supporting tables are referenced.
View History	Displays all the previous run attempts associated with the selected Profile.
Export Results	If clicked, a mover screen displays the fields from the Records tab. Select and move the fields to the right side. Click Continue to export the data in the Records tab to an Excel spreadsheet.
Validate Profile	If clicked, will perform a check on the data import profile to ensure that for any field on the profile, if it's related to another database field, that a valid record exists. For example: validate that to_biid exists as dmbill.bi_id in the Bill To table. Added in version 17.03.006
Map Profile	When clicked, Sources are loaded and if any column names match Deacom table names the Profile is built automatically using those Fields. Mapping also occurs automatically when a new profile is created using the Source Type of Excel Upload and no Destination has been chosen yet. For each column in the source, if the column name matches a Field in the field map grid, the system will set the Expression to the column name. If the xx_id field is a column in the Source, the system will set the Profile Type to "Update", otherwise it will be set to "Insert". Added in version 17.03.005.

General tab

Button/Field	Description
Profile Name	After the Profile is imported, the Profile Name will display here. This name can be changed. Only pre-approved non-expired Profiles can be used in the Import Data tool.
Destination	The table or tables where the data will be imported or updated. The selection here will impact what is displayed in the lower right quadrant, the Fields tab. Any password fields will be encrypted before saving to the database.
Conditions	Used to filter out data that should not be included in the update.
Profile Type	Options are Full or Partial. If set to Full data is being inserted into tables. If set to Partial data is being updated.
Profile Notes	Memo box used to store notes related to the selected Profile.
Expiration Date	Displays the expiration date for the selected Profile. Profiles will not run after the expiration date.
Active	Indicates if the Profile is active.

Sources tab

Button/Field	Description
Connect	If clicked, will validate Server, Username, Password, and Database connection. It will also populate the drop down menu in the "Table" field below.
Source Type	Deacom SQL - if selected will populate the server information from the Deacom ini file. Excel Upload - allows users to load/upload Excel files directly. Do not change Source Type
Source Name	Displays the name of the selected Source.
Map From	Displays the field/column or an expression using the fields/columns from the current Source to use when linking Sources.
Map Table	Drop down listing all the other Sources. The Source selected in this field is the Source that is being linked.
Map To	Displays the field/column or an expression using the fields/columns from the mapping Source table that will be used to join to the Map From field.
Notes	Memo box used to store notes related to the selected Source.
File	Defines the name of the file to be imported when using a Source Type of External File.
Source Path	Defines the location for the file that will be imported. If the DATA path is defined in the Deacom.ini file it will be appended to what is here or the full UNC path can be entered and DATA path is ignored.
Destination path	Defines the location where the file will be imported to. Used to move load files to another folder after an import completes.
Sheet	Used only if the Source Type is External file and an Excel spreadsheet. Identifies the sheet position of the data to be used. An integer must be used as it is identifying the physical location of the sheet, not the sheet name. If left blank, it will default to sheet 1.
Server	Contains the server name when using a Source Type of Deacom SQL, Pervasive, or SQL Database. Need to include server/instance name if applicable.
Username/Password	Indicates the applicable username and password for accessing the database for the Server listed above.
Database	The name of the database that will be accessed. The user will receive a prompt if attempting to access a database that the current environment cannot. In version 17.03.007.0000, the length of this field was increased from 30 to 60 characters to allow longer database names.
Table	A drop down menu that will show tables or views that are available based on the database you've connected to above. You must click on the "Connect" button for this drop down to be populated. Not used if doing a Custom Query. As optional licensed features are activated, more selections will be available in this field.
Custom Query	Displays the query used to gather data from the database.

Records tab

When the "Load" button in clicked, the system will display a preview of the data in the Records tab from the sources mapped in the Sources tab.

Record # of # , in the top area, indicates the number of combined fields/columns in the load file.

Record # of #, in the bottom area, indicates the number of records in the combined load file. Clicking the back or forward arrows at the top will show the previous or next data record. Click the search funnel at the top to enter a specific record number then click Apply to skip to that particular record.

Fields tab

The Fields tab displays the fields from the selected Destination (table or tables) and any fields, values, or expression to be loaded, which will be displayed in the Expression column. All fields are used when the Profile Type is Full. Only fields that are not Skipped are used when the Profile Type is Partial.