D - Data Interchange

This option allows you to exchange data between Datafile Software databases and other applications using one of two methods – the DIF or ASCII text file formats.

DIF – stands for Data Interchange Formatand is a specific standard used by many database and spreadsheet software packages.

ASCII – an acronym for American Standard for Character Information Interchange.Typically a database record translates into a line of text, with each data item separated by a comma – this form is also sometimes known as a CSV (comma-separated variable) format. Other separators include the TAB character, or indeed no separator but output at fixed positions.

This option allows you to send information both in and out of Datafile databases. So after completing the initial parameters in the options definition you are asked for the interchange type.

NB: Once you have selected an interchange type it is fixed for this parameter file (filename specified as parameter 2 initially), if you select incorrectly you would need to either delete this file or change the filename in parameter 2 so it creates a new definition.

The parameters available in both Output formats are reasonably identical and are described together below as one process – highlighting the differences between them before moving on to the input definition.

Output from Database

Enter here the pathname of the file you wish to create from this output definition. The file should be in 8.3 format. Note that F4-Select is available here to help you navigate the folder paths but if creating a new file you will not be able to return the pathname from this process.

This refers specifically to the database item names, which you may want to output to the DIF file in addition to the data itself. DIF formats allow not only for the data, but also for names of rows and columns as well — either for reference (i.e. as "labels”) or as data items in their own right.Option 0 is the usual choice (ignore the database item names); option 1 puts out the database item names as labels; and option 2 puts them out as though they were data for the first record (record zero).

If any of your database data items could contain a comma, or whatever you specify as the separator character below, then you need to ensure that such commas are interpreted as data not as field separators. If the separator is included in the data item the system will automatically place quote marks around that data item, but if you wish to include quote marks for all text items then set this option.

Normally a comma, ASCII code 44. Enter the required code or use <F4-Select> if required. Note the selected character is shown to the right for verification.

Database data items — particularly numeric ones — normally convert to a fixed length of text; for example, an L-type item displays as 14 characters. A value of (say) "9.90” would contain nine leading and one trailing blank characters. In a similar way, text data items may not be completely full, so end with a string of blank characters. To save space in the output file, the interchange procedure can eliminate such leading and trailing blank characters. Set this option to strip out leading/trailing spaces otherwise leave blank to output the full length of each data item, including spaces.

If you find that your data has text characters which interfere with the process which uses the output interchange file, then ask this procedure to ignore those characters.For example, it may be that you use the oblique / and chevrons <> in you database, but these create problems to the receiving process. You can list up to ten ASCII codes to ignore here, entering the equivalent decimal codes for those characters. You can use the <F4–Select> key to choose them.

This option controls whether records are output in the order they were added or, for prime-indexed databases, in the order of the prime index. This setting will be ignored if set to a user defined sequence on the second parameter screen (ASCII only)

If a number is negative, a minus sign is displayed at the end of the text. If you need the negative sign at the front of the number set this option

Output the record number

If you want to write the database record number to the output interchange file set this option. It is written as the first item in each output file line.

Set if you want to apply selection criteria to this process. You’ll be asked for the default criteria after you’ve answered all questions in this screen. You also have the options to ask for run-time changes, whether to use a fixed specified range of record numbers, and whether to save any selection criteria changes to be the defaults offered the next time it is run.

Automatic run - no user prompts

You are normally allowed to change the output interchange file name as well as other aspects at run time.Set if you want to prohibit any of these run-time changes.

Some programs expect the first record of an ASCII file to contain data item names, using them as the prompts for their own processes. Set if you want to output the data item names as the first output record.

For ASCII files only, you can ask to sort the database into a specific order before writing the output interchange file. If so, then you can choose from up to four items from the database on which to sort.

Specify Data Items

Finally you must define which database data items are to be written out to the interchange file, and in what order, as shown in the illustration below. The left-hand number of each of the 160 pairs represents items in the output file in the sequence they are to be written. Specify in the right-hand number of each pair the data item from the database whose value is to be written to the interchange file in this position. You can use the <F4–Select> key to see the database structure and choose data items.

If you select ‘Yes’ the system automatically fills in all the item numbers present in the Datafile database against the item numbers for the output DIF/ASCII file in sequence.Select ‘No’ to complete the data item numbers yourself. Use up and down arrows within this list to change position. Once complete, press the <End> key.

Select ‘Yes’ to confirm the details are OK, ‘No’ returns you to the first data item input prompt, whilst ‘Cancel’ exits from the interchange definition without saving.

Selecting ‘No’ to the save changes prompt returns you to the first definition screen, allowing you to amend the details. <Escape> abandons all changes to the definition and returns to the definition menu, whilst ‘Yes’ updates the definition and returns you to the menu.

Input to a Database

These options take a text interchange file, either in a DIF format text file or in an ASCII text file, and "import” the data into a database. The set of parameters differs between the two types ¾the questions shown below are for ASCII input¾ but all the parameters are described in the order they occur in the parameter screen.

External file pathname

Enter here the pathname of the input interchange file. Note that you can use the <F4–Select> key here to help you find the files you want.

The ASCII import will allow you to use wildcard characters such as ‘?’ and ‘*’ when defining the filename to import, although not within the pathname or file suffix. For example, you can set pathnames such as C:\IMPORTS\*.CSV, or C:\IMPORTS\SALES??.CSV. If multiple files exist that match the entered pathname then they will be processed one after another.

If you use wildcards you must set the next option for ‘Move to History Folder’.

Note – Importing more than one file

If set to ‘Create New File’ but not ‘If Create, Append Records’ then any existing records will be cleared at the start of the import process and then all the files matching the import will be added to the database.

Similarly, if merging the import files into existing data and set to ‘Warn errors if merge’ and to ‘Print Warnings’ then one report will be created to cover errors in any of the import files.

Finally the totals display for records imported will include the count from all import files.

Move to History Folder (ASCII only)

Selecting this option moves the input file into another folder on your system after import. If using wildcards in the import path this must be set. Entered pathname must end with a ‘\’

Normally a comma, ASCII code 44. Key the required code <Enter> here — the print character is displayed to the right so you can check. You can use <F4–Select> to look up and choose the character and its ASCII code.In the case of fixed position input files, reply zero here, and see below for how to handle.

A special text field to allow certain special input files to be recognised and processed. At present the only special file supported is the CPS trade service. However you can also enter REN-YMD within this option to rename the import file with the date, in the format YYMMDD, after processing (although this cannot be used in conjunction with the move to history folder option).

If the input file has no separators (you replied zero above) then it must be a fixed format file and you should set this parameter.

If fixed format input file, and the input fields are all of the same length, then give the field length here. Leave at zero if the input fields could be of differing lengths (the most common occurrence). You’ll be asked to define the input positions below.

These two questions determine whether or not the input file records are separated by carriage return (CR) and line feed (LF) characters. Set these parameters as appropriate, it is normal to set both.

End of data ASCII char

The standard character used to mark the end of an input text file is <Ctrl>–Z, which is equivalent to the ASCII value 26. Enter the ASCII value here.

Ignore ASCII code chars

You can define up to ten characters to ignore from the input interchange file. For example, there might be colons and semicolons in the input file which you decide do not look correct in (say) address lines. Use the <F4–Select> key to choose any you want to ignore, and leave the others at zero value.

Set if you are starting a new database from the interchange data, or if you want to add the interchange data to an existing database. Note that in the former case the physical database must already exist, even though empty of records; the process does not of itself create the database structure.

If you want to merge the interchange data with an existing database — for example, adding budget data to an existing nominal ledger leave this option blank.

If Create, Append Records

If you have set the previous parameter, then you need to say whether you are adding records to the database, or want to start the database with fresh data. Set to add the records from the interchange file to any which may already exist in the database. Leave blank to create a completely new database.

Note

If any records already exist in the database when you try to create a new database, then any existing records are deleted, after warning you first.

If Merge, Create New if No Match (ASCII only)

If merging an import into an existing database then you may have new records in the import file that have not yet been created within the database. Set this option to create a new database records for any import records with a non-matched prime-index or record number. If left blank then any non-matched records are ignored.

If Merge, I/P Sequence for Record Number

If you are merging data from the interchange file into an existing database, then the right database records must be accessed for the update. You must specify which interchange data item is the key to the database. The value of this item is used to find the correct record in the database by looking it up as the prime-index or record number.

This determines in a merge run only whether to overwrite an existing data item if the input item is blank. Sometimes you may want to leave the existing value in the database record unchanged if the input item is blank; at other times you may want to blank it too. Set as appropriate.

Auto audit when complete

This question only applies to a database with a prime or secondary index, to which you have added records. Before you can use the database it must be audited to update the index blocks, set as required.

If a record exists in the interchange file but no equivalent record exists in the database when you are merging data, then if set the first question the system will stop and warn you, and await further instructions. Leave blank if you are happy to let the system proceed without stopping ¾ it will tell you at the end that there were errors. You also have the option to print the warnings rather than display them to the screen.

Ignore if input item null or blank

You can choose to ignore a record from the input interchange file if one of its items is blank. For example, a spreadsheet budget would have sub-totals and totals, plus blank lines for presentation purposes. None of these lines, treated as records, equate to a nominal code, so can safely be ignored if (say) the item holding the nominal code is blank. Key here the item (column) number from the input file that is to be checked for this condition

Automatic run - no user prompts

You are normally allowed to change the output interchange file name as well as other aspects at run time.Set if you want to prohibit any of these run-time changes.

Specify Data Items

Now you must relate the input file items to the data items in the database. The database structure is displayed on the left of each pair, and only those data items that exist in the file are shown. You enter alongside these the sequence number from the input file that is to match to each database data item.

If you select ‘Yes’ the system automatically fills in a sequential number starting from one against all the data items in the database. If you select ‘No’ you must fill in all the items you require yourself.

Against each number, therefore, key the number (column) of the input file item which is to be copied across. If, for example, ACC-CODE is data item number 3 in the database, and it comes from the eighth column in the input file, then key 8 <Enter> against data item number 3 in the list.

Proceed down sequentially until all database data items that you want to input from the input file have got the input file column number against them. Note that you can use the <F4–Select> function here to see the full structure of the database file for reference, and can use the <F7-Option> key to view the import file itself for comparison (if exists at the point of definition).

If the input file is a fixed field length file, such as a report or (say) input from a till roll, then you must also specify the start and end positions of each input field:

Up and down arrows can be used within this list to change position, once required items are matched press the <End> key.

‘Yes’ completes the interchange definition, ‘No’ returns you to the first match item, whilst ‘Cancel’ exit the definition without saving.

Article ID: 1201
Created On: Thu, Jun 28, 2012 at 3:14 PM
Last Updated On: Tue, Jun 20, 2023 at 5:34 PM