The Import Export Dialog is provided as a means of moving data from one datasource to another by creating and reading a specially formatted ASCII file.

All CCS Accounting Programs are designed to work with a multitude of ODBC Datasources. Inherent with this flexibility is some complexity when data needs to be moved from one type of data source to another. This is most apparent when clients want to import existing data (from older CCS Programs as well as third party programs), or to upgrade to a more capable ODBC driver, e.g., from Microsoft Access to Microsoft SQL Server. To further complicate the process, many of the tables and columns contain primary and foreign key relationships that must be maintained from data source to data source. Since each datasource often defines a proprietary syntax for such relationships, a standard (as far as the CCS Programs are concerned) was needed to move the data reliably to and from any supported data source. The Import/Export Dialog provides this capability.

Note: If you are using an existing CCS Accounting DOS Program, simply place a copy of your "drive.num" file in the DATA directory. You will find this file in the program directory of your older CCS Program(s). When the CCS Program is run and it sees this "drive.num" file, it will use it to locate the older CCS files and automatically import them for you. See Match EMPLOYEE Files in the Utilities dialog for more information on maintaining concurrency with older versions of CCS Accounting.

Advanced Topics:

You do not need to understand any of the topics presented below to use the CCS Accounting program. They are provided for those who seek a deeper understanding of the CCS data management tools.

The two modes, Import and Export, read or write a specially formatted ASCII file which is always named as follows:

TableName + Company# + ".ini"

This file will always be created in, or read from the DATA directory.

Note: The File Name will include the COMPANY number just as it appears in the ODBC data source if applicable to a particular COMPANY.

Note: Many tables used by the CCS Accounting program support automatic initialization. Each time an empty table that uses this logic is opened (at any time, anywhere within the program), the CCS Accounting Program automatically searches for an import file that matches the newly opened table and, if found, uses it to populate the empty table. If no ini file is available, you will receive an error message indicating the failure until the table contains at least one row.

The format of this "ini" file is quite simple; however, it provides a very powerful means of interpretation by the CCS Import/Export routines. The specifics of the file will not be covered in detail here--an overview is provided below.

The following two rows were extracted from the TTITLESCovered99.ini file.

[[TABLE]]:TTITLES_COVERED

[[ROW]]:1

[TITLE_ID]:AC2

[REC_ID]:1

[TEVENTS_REC_ID]:[#RESOLVE_FK,TEVENTS## ,REC_ID,EVENT_ID]:S10151AB [TTITLES_REC_ID]:[#RESOLVE_FK,TTITLES## ,REC_ID,TITLE_ID]:AC2

[[ROW]]:2

[TITLE_ID]:AC2

[REC_ID]:2

[TEVENTS_REC_ID]:[#RESOLVE_FK,TEVENTS## ,REC_ID,EVENT_ID]:S10151AA [TTITLES_REC_ID]:[#RESOLVE_FK,TTITLES## ,REC_ID,TITLE_ID]:AC2

The Table name follows the [[TABLE]] delimiter (double square brackets), and is ignored by the import routine since the filename itself is used to determine the correct import file.

Each new row is indicated by the ROW keyword enclosed in double brackets [[ROW]].

Note: The row number is ignored during import and is provided for information only by the export routine.

Each Column name is enclosed in single square brackets. This example includes the [TITLE_ID], [REC_ID], [TEVENTS_REC_ID] and [TTITLES_REC_ID] columns. The Column name is followed by a ':' and the data that is to be imported or exported for the column or a means to resolve a foreign key link follows the column name.

In the example above, the value for the TITLE_ID column in row 2 is 'AC2'.

The TITLES Covered table includes two foreign keys, [TEVENTS_REC_ID] and [TTITLES_REC_ID], that link into two different primary tables, TEVENTS## and TTILES##, where ## represents the COMPANY NUMBER.

Using the example below, the CCS Program parses the #RESOLVE_FK keyword in the following manner.

[TEVENTS_REC_ID]:[#RESOLVE_FK,TEVENTS## ,REC_ID,EVENT_ID]:S10151AA

The Column Name is enclosed in square brackets [] and is parsed as normal. In this case the column name is TEVENTS_REC_ID.

This is followed by a ':' and another set of square brackets [] that contain the link relationships indicated by the keyword #RESOLVE_FK.

The Primary table is then specified after a comma. TEVENTS##. If the Table name will contain a COMPANY number a set of pound characters ('##') are included. These characters are replaced during import with the actual COMPANY number of the table being imported or exported.

This is followed by a comma and the column name in the primary table that this foreign key is linked to--the REC_ID column in this example.

The CCS Accounting program utilizes an auto increment column named REC_ID if at all possible as a primary key in all tables. Since this is an auto incremental column, it is read only. Therefore the CCS Accounting program will not attempt to write the value specified by a column that was created using the COUNTER type. This poses somewhat of a problem when it is necessary to re-establish a foreign key to primary key relationship for a table being imported and the primary table utilizes an auto increment column. To illustrate the problem, let's takes the TEVENTS table that uses the REC_ID key as a primary key. This column is linked to the TTILES_COVERED table via the TEVENTS_REC_ID column in the TTILES_COVERED TABLE. Now let's say both tables are exported via this Import/Export routine to be imported into another datasource. The REC_ID columns data of the TEVENTS table will be exported, but it cannot be imported back since it is an auto increment column. So if any row was deleted in the original data source, the REC_ID column of the newly imported table will not match the exported table. To resolve this problem, the following logic is implemented by the Import/Export routines:

All Tables in the CCS Accounting Program that contain an auto increment primary key that will have links to foreign tables also contain a secondary unique key . This key is generally the key used to access the record by the user. For example, the unique key for the TEMPLOYEE Table is the TEMPLOYEE_NO column. For the TEVENTS table, it is the EVENT_ID column. However, both tables use the REC_ID column as the actual primary key. Using this knowledge, the Import/Export routines search for the first column in the primary table designated as a unique key. If found, the routines make the assumption that this key can be used to resolve the relationship as well. In the example above, the Export logic located the correct row in the primary table (via the REC_ID) column. It then located a unique key, the [EVENT_ID]:, and then extracted its value and appended it to the export output S10151AA. When the table is imported, the Import routine will reverse the logic and locate the row in the TEVENTS table by searching for an EVENT_ID of 'S10151AA' and if found get is primary key and use this primary value as the value to be imported for the [TEVENTS_REC_ID] column in the TTITLES_COVERED table.

If you are creating an import ASCII file for CCS, you undoubtedly do not have to have this relationship in your existing data source. Simply create the ASCII file using the data and columns of the existing links to provide the information that the Import logic can use to resolve the relationship.

Note: You can import data without the primary to foreign key relationships and then later establish the relationship via your database management program (SQL Query, Enterprise Manager, etc.). Also you can directly populate the CCS ODBC datasource; however, you must insure the data source is properly defined and populated, and all referential constraints are adhered to.

Feel free to Contact Us for help on Importing or Exporting data.

Note: You can also use the Export feature in the Export Reports dialog to export report data in a variety of standard formats such as ASCII, dif, Excel, etc.