File Loads from Desktop

General Load Guidelines

DATABASICS allows data to be entered into the application through load according to a specification agreed in the solution design.

A “profile” will execute the load from files.

Loads must conform to the following guidelines:

1. Load Files Without Header Row

   • Load files should not contain a header line unless specifically indicated.

   • If your data file includes a header row with column names, you can use the new option to skip the first row during the import process. This is helpful for CSV or delimited files where the first row contains column names that should not be treated as data.

2. Import Options
   When importing your data, select the appropriate file type and ensure you select the option to skip the first row if it contains column headers. The available import options are:

   • CSV File Without Quote: Used for CSV files that do not contain quotes around data values.

   • CSV File Without Quote-SKIP-FIRST: Used for CSV files that do not contain quotes around data values. This option will skip the first row if it contains column names.

   • Fixed Width Text File: Used for files with fixed-width columns.

   • PIPE Delimited Text File: For files delimited by pipes (|) instead of commas.

   • PIPE Delimited Text File-SKIP-FIRST: For files delimited by pipes (|) instead of commas. This option skips the first row if it contains column names.

   • Standard CSV File: Used for standard CSV files that may contain quotes around data values.

   • Standard CSV File-SKIP-FIRST: Used for standard CSV files that may contain quotes around data values. This option will skip the first row if it contains column names.

   • TAB Delimited Text File: For files that are tab-delimited.

   • TAB Delimited Text File-SKIP-FIRST: For files that are tab-delimited. This option skips the first row if it contains column names.

   • XML File: For XML formatted files.

3. File Format Requirements

   • All load files should be presented as tab-delimited .txt files, unless using one of the above options for CSV

   • No double quotes (") are allowed around data fields in any file type.

4. ID Fields

   • ID fields must be upper case and alphanumeric, using the characters [0-9] [A-Z] [-_.]. Special characters and spaces are not allowed.

5. Full Loads

   • File loads will be full, meaning all active records will be included in the load. Any record not included in the load will be set to inactive.

   • This load approach is configurable with an alternative partial setting, where only new or changed records will be included in the load file. The load will still include an active y/n flag to indicate whether the record is active.

Create load file from Excel

Example of how to create load file

1. Add list in excel
   Make sure the Code (LOACTION COLUMN in example below) is formatted as TEXT (excel will strip the 0s if not changed).

   Open

   

2. Remove Line 1 (Header line)

3. Save as “Text (Tab Delimited) (.txt)”
   
   

   Open

   

4. The resulting file will look like the following if opened in Notepad.

Accessing File  

1. Access the profile by going to Admin > Data Piping > My Profile Task.

2. Find the profile to import the data you want to load and click the “Run Now” link.

3. Click on “Browse” to search for the timesheet file.

4. Click on “Open” once file is located.
   - This will create a “fakepath” location in the interface

Load file

1. Click “Run”
   
   

2. On the Job Execution Query screen, click  “Refresh” button as indicated in the screenshot below until the job status is “Completed” (Warning: do not use browser refresh button).

3. Click on the completed  job link to view the status of the load and possible warnings/errors.

4. Click on the paperclip attachment icon to view the list of failed records, and address warning messages, if applicable.

5. Sample the data in the application to confirm the information has loaded successfully.