Move Data by Glenn Delahoy (C) Copyright 2004 All rights reserved Description ----------- Move Data provides a method of migrating data between various database systems, spreadsheets and text files. It provides a way to edit data within databases where there is no editor available and also producing text output. Please read this document completely before proceeding to trash your database. There's a few gotchyas and hidden surprises you may not see coming. Features -------- Reads From ---------- Comma delimited text files (first row must be column names) Tab delimited text files (first row must be column names) dBase tables Paradox tables Access 97/2000 tables Oracle tables Info Central v7.0 lookup tables Writes To --------- Comma delimited text files Tab delimited text files Clipboard (ready to paste to spreadsheet) HTML tables dBase tables Paradox tables Access 97/2000 tables (database must exist) Oracle tables (database and table must exist) Info Central v7.0 lookup tables Allows you to modify the SQL select statement used to retrieve rows from the source tables (dBase, Paradox, Access, Oracle) so that you can restrict, reorder and otherwise modify the rows to be added to the destination table. You can choose to append new source data to existing data in the destination table or overwrite existing data with the new data. Things To Consider ------------------ In all cases except Oracle, if the destination table does not exist it will be created. The data that's actually added to the destination table depends on the columns that exist there. If the table is created on the fly by Move Data then it will look pretty much like the source. If the table already exists, only those columns that match columns in the source table will be updated. To successfully migrate from CSV/TAB files you need to ensure the first line of the text file contains the column names thus: "COLUMN1","COLUMN2" etc Remember, in all cases (except html and clipboard output) if the destination table exists, the source data will be appended. If you do not want to keep the original data in the destination table, you should delete or truncate the destination table before migrating. If you select an Access table as the destination, the Access database must already exist and you must supply the correct password if it is protected. If the table does not exist in the database, it will be created. It is not possible to create Oracle tables on the fly, there are just too many questions to be answered. So before you move data to Oracle you need to ensure the destination table exists. dBase and Paradox require the BDE. Working on replacements for these. Meanwhile you can get the BDE from my site below listed with MoveData. Data types are not always successfully migrated. It depends on the source and destination systems. For example if the data source is a text file, the fields are all treated as text. All InfoCentral fields are output as text (see below). Sometimes there is no equivalent data type when migrating between systems. In this case the closest data type is used or it is converted to text. In these situations you can usually use the destination database system and some sql to massage the data further. Sometimes the paste from clipboard function in Excel doesn't get the data type right. In this case you should migrate your data to a csv file and use Excel's Import Text File function. In Office 2000 this is found on the Data|Get External Data menu. This tool gives you the opportunity to define the data types for each column in the csv file. Selecting to overwrite existing destination table data and creating a brand new destination table doesn't necessarily mean the same thing. Consider a source table with ten fields and a destination table with five fields. If you choose to overwrite the destination data, all data in the destination table will be deleted before the migration but the five fields will remain. The migration will attempt to match up the fields in the source table with the fields in the destination table. The result will be some data from the source table will be lost. If you were to migrate to a new table, all fields from the source table will be recreated in the new destination table and all data will be moved intact. The downside of deleting destination tables, particularly dBase, Paradox and Access tables is you may lose indexes and keys. Info Central v7.0 ----------------- Info Central is a personal information manager distributed by Corel. It ceased production a number of years ago and is no longer supported but many people still use it. MoveData provides a means for modifying the internal InfoCentral lookup tables that the application itself cannot modify, specifically: WPICNAME Name lookup table WPICAREA International and local area telephone code lookup table WPICZONE Time zone lookup table WPICCTRY Country timezone and telephone access code lookup table WPICSTAT North America state and area lookup table In each of these files there may be fields that are unknown and are labelled as such. If you happen to know what they are please let me know. All fields in InfoCentral are input and output as text. Any non text fields are converted as required internally. For example you can generally coax the Zone file Index field into a number field in Access but the GMT field should be a text field because MoveData converts it to text on the way out and converts it back to a time on the way in. Some files have a field called TIMEZONE which points to a record in the WPICZONE file. If you make changes make sure this number refers to a valid zone record. In theory, you can append new data to an existing InfoCentral data file. However, this is not recommended and InfoCentral will probably choke on it. Your best approach is to migrate a complete copy of the source data and overwrite the original file, making sure you have a backup copy of the original. InfoCentral Formatting Notes ---------------------------- The most common use of MoveData for InfoCentral will be to migrate to a csv file, use Notepad to make some changes and migrate back to the original InfoCentral file. MoveData will do its best to make sure the data from the source file fits the InfoCentral format but you need to be careful to provide it with sane input. If in doubt, check the existing records and follow the patterns. In the WPICSTAT file, ALL fields must contain data. MoveData will right pad any blank or short fields with spaces as necessary. The AREACODE field normally contains three characters. The ABBREV field normally contains two characters. The TIMEZONE field is a number but make sure it is contained within quotes. If you're not sure what the time zone is for a given entry, look for a location elsewhere in the file that is close and use that number, otherwise set it to "0". In the WPICSTAT file, the CHILD field indicates whether the entry is a top level parent, a child of a parent or a child of a child. For a top level parent the child value must be 0, for a child of a parent the child value must be 8441 or for a child of a child, the value must be 8442. Check out the first three entries for "Alabama" to see how the child field works. Notice how the main "Alabama" entry has a child value of 0, "Northern Alabama" has a child value of 8441 and "Birmingham" has a child value of 8442. The WPICZONE file is used to look up time zone information for InfoCentral's time zone designations, which are 1 through 51 for the standard global time zones. The GMT field starts at "-12:00" and goes through to "+12:00". The DSTOFFSET field indicates the time offset when daylight saving commences and is always "+01:00". Make sure the entries are listed in GMT order and renumber the INDEX field if necessary. Mike Koenecke (http://macros.koenecke.us) is maintaining updated InfoCentral database files; look for new ones there. He wrote this last bit and wishes to thank Glenn for solving a problem that has vexed InfoCentral devotees for years. WARNING ------- There's always a chance something will go wrong during a data move. You should always back up your important data files and test the migration on a *copy* of your data, never use the original/working files or tables. Tested on Windows 2000/XP, Access 2000, Oracle 7.3, BDE 5.10. ---------------------------------------------------------------- Release Notes ------------- Version 1.1 ----------- Change 1: Fixed problem with migrating Access to Access where the target table does not already exist. Change 2: Added clipboard option for tab delimited files. Version 1.2 ----------- Change 1: Added code to accomodate large font setting on Windows. Version 1.3 ----------- Change 1: Fixed bug in copy to clipboard function where it was run after migrating to a text file. Change 2: Big speed improvement in HTML output. Change 3: Moved clipboard option to the output selection box. Change 4: Added support for some Info Central v7.0 internal lookup tables. Change 5: Removed some unnecessary code dependencies. Change 6: Added Overwrite/Append options to destination tables. ----------------------------------------------------------------- Comments/suggestions to: support@delahoy.com http://www.glenn.delahoy.com