Exporting data to the world
Contents
1. DEPARTMENTS Master Set 2 Entry Offset DEPARTMENT NAME X20 1 DEPARTMENT NO Il 21 lt lt Search Field gt gt Capacity 20 20 Entries 2 Bytes 22 References Migrating Data to the World Link the Files Using Suprlink at gt link invoke suprlink from suprtool input emp link dept output empdept xeq exit from suprlink 9 Suprlink uses the common sort key department no to link the files For Techies creating composite output records composed of the link record The common key appended to the input record department no appears in both the input and link files but will not be duplicated by Suprlink in the output file References Migrating Data to the World Create File Using STExport at gt export invoke stexport from suprtool input empdept date yyyymmdd heading Address heading add Bank Account ID heading add Work Phone Number heading add Department Name output empout xeq exit from stexport 10 Date Format For Techies The Date command specifies how date fields are written to the file Here we have specified that dates are in yyyy mm dd format with slashes No Headings The default is not to have a heading record Fieldnames The Heading Fieldnames command creates a heading record with the names of the fields as recorded in the self describing input file These usually are the names of the IMAGE SQL fields2. Fo Date Delimiter po l Four Digit Years Decimal Separator MS Access can import data from a variety of source applications Not For Techies surprisingly Suprtool and IMAGE SQL are not listed so we ll use the generic delimited text format We ll just use the MS Access defaults for delimiters and separators because they are also the defaults in STExport References The MS Access manual explains the various import options Migrating Data to the World The Import File Text Delimited Without Column Headings Fernandes Karen 24386 1 1786 E 30th Vancouver BC Canada Grinham Robert 5557 1 307 2222 Edinburgh Richmond BC Cana With Column Headings Employee Name Employee Number Status Address City Provin Fernandes Karen 24386 1 1786 E 30th Vancouver BC Canada Grinham Robert 5557 1 307 2222 Edinburgh Richmond BC Cana Fields in Different Sequence With Column Headings Address Bank Account ID Bank Name Birth Date City Countr 1786 E 30th 006 2407 11896 First National 1958 04 01 Vancouv 307 2222 Edinburgh 001 2547 66983 Toronto Dominion 1970 02 14 It is important that MS Access know where to find the fields in the import record The separator character in this case a comma separates the fields and the text delimiter character here double quotes marks
3. Suprtool is a trademark of Robelle Solutions Technology Inc Migrating Data to the World 5 What s Inside Pages Importing IMAGE SQL into MS Access a 3 How data looks in each application Using STExport eee 10 Transfer the file to the PC e Errors while importing E ee 4 m Why use STExport ee Exporting from ALLBASE SQL e 17 2 For brevity s sake this tutorial shows examples only from MS Access For Techies STExport can of course export data to many other platforms and applications STExport allows control over the format into which you convert date type numeric type and floating type fields You can therefore format your data in a way acceptable to your target database References STExport is fully documented in the Suprtool User Manual and the Suprtool Quick Reference Guide Migrating Data to the World Import IMAGE SQL Data into MS Access at gt Extract the IMAGE data using Suprtool and Suprlink Create the export file using STExport m Transfer the file to the PC Import the file into MS Access Suprtool mp UPOO gt STExport gt Suprlink 3 Importing to a PC application is similar in concept to almost any job For Techies where Suprtool is used to feed IMAGE data to a program The only These examples show extra wrinkles here are using STExport to reformat the output and STExport iX version transferring the file to the PC 3 8 and MS Access for Windows version 2 0 Refe
4. Migrating Data to the World Migrating Data to the World at A Robelle Tutorial Mike Shumko August Copyright 1996 Robelle Solutions Technology Inc A robelle The ability to migrate data from IMAGE databases and MPE files to For Techies other platforms is becoming more of a necessity all the time Past methods of exporting data from MPE relied on fixed formats which did not always permit the data to be imported easily into other applications and which required considerable work from programmers A new tool however can make this process easier STExport is the newest module of the popular and powerful Suprtool database utility In Migrating Data to the World you will learn how to use STExport to export data from your HP 3000 or HP 9000 to just about any other platform all without your having to write a custom program Migrating Data to the World is presented by Robelle s Mike Shumko Mike has just celebrated his tenth anniversary at Robelle During his decade with the company he has spent much time as a technical support person and trainer helping users of Qedit and Suprtool to get the most from this software References See the other papers Robelle Solutions Technology Inc Toll free 1 888 762 3553 and tutorials being Suite 201 15399 102A Avenue Telephone 604 582 1700 presented by Robelle Surrey B C Canada V3R 7K1 Fax 604 582 1799 at this conference E mail support robelle com Web www robelle com
5. User Specified Headings References Add your own headings to match the names in the MS Access file All Heading commands except the first have the Add keyword Put in your own delimiter quotes and separator commas if they are expected by MS Access Defaults We re taking the defaults of quoted text fields with trailing blanks suppressed comma separators and variable length columns 10 Migrating Data to the World The Resulting File on the Host at gt Address Bank Account ID Bank Name Birth Date City 307 2222 Edinburgh 001 2547 66983 Toronto Dominion 19 1786 E 30th 006 2407 11896 First National 1958 04 01 11 The output from STExport has all the data we need to supply to MS For Techies Access e First record has the names of the fields e Data values separated by commas e Non numeric values surrounded by quotes e Date fields in yyyy mm dd format Notice that the field sequence in this file is not the same as the sequence shown on page 7 of the workbook Because the first record contains the list of field names MS Access will be able to import the data correctly mapping the file s fields into the correct database fields References 11 Migrating Data to the World Transfer the File to the PC a o File Transfer x Local Host Local File Names Host File Names D Transfer Protocol WRQ Reflection z HP Host Tran
6. ains Field Names box on the Import Text Options menu Because this box was not marked MS Access assumed that the fields in the import file were in the same sequence as those in the table MS Access also tried to read the first record as data when it really contained field names The first field in the table Employee Name was loaded without incident from the import file even though the first field in the file really contained an address All MS Access cared about was that it got quoted text not exceeding the length defined for Employee Name The second field Employee Number was a problem however because in the second field of the import file there was another quoted string Bank Account Id instead of a number References 15 Migrating Data to the World Why Use STExport STExport lets you Strip trailing spaces from text fields Control the format of dates and numbers Add field names to the first record Specify the delimiter and separator characters Thank you Michael Shumko for your recent subscription to our fine magazine Scientific American You will soon receive your first issue along with your free gift the Binford 9000 Particle Accelerator Blah blah blah Obviously for some applications it s critical that text fields have their trailing spaces removed STExport gives you control over the format of the data going into the import file whereas the PRN option of Suprtool s Output command does not hav
7. e any flexibility STExport Formatting Commands e Columns e Date e Delimiter e Floating e Heading e Quote e Sign e Spaces e Zero 16 For Techies Confusing terminology STExport calls the character that appears between fields a delimiter MS Access calls this a field separator STExport calls the character used around byte type fields a quote MS Access calls this a text delimiter References 16 Migrating Data to the World Exporting from ALLBASE SQL ats open allbase employee db humres select from humres employees sort department_no output emp link xeg select from humres departments sort department_no output dept link xeq Let us assume that the original data is in an ALLBASE SQL environment instead of an IMAGE SQL database You can still use Suprtool to extract the data into self describing files There are two differences use the Open command instead of a Base command and use an SQL Select command instead of a Get command The rest of the job is the same feeding the two files into Suprlink then processing the resulting file with STExport Using the flexibility of the Select command you could even extract all the fields you need in a single pass The command would be something like this Select address bank_account department_name from humres departments Dept humres employees Emp where Dept department_no Emp department_no If you use as a column
8. e will be appending records to the table tim Table Employees Cit Province State 1 307 2222 Edinburg Richmond 1 1786 E 30th Vancouver The table is defined like this For Techies Employee Number is Name Type Size the index item to the Employee Name Text 50 table It s configured Employee Number Number Long 4 as non duplicating Status Number Long 4 Address Text 50 City Text 50 Province State Text 50 Country Text 50 Postal Zip Code Text 50 Home Phone Number Text 50 Sex Text 50 Social Insurance Number Number Long 4 Hire Date Date Time 8 arital Status Number Integer 2 Spouse Name Text 50 onthly Salary Currency 8 Bank Name Text 50 References Bank Account ID Text 50 Birth Date Date Time 8 Title Text 50 Department Nam Text 50 Work Phone Number Text 50 Last Review Date Date Time 8 Vacation Days Number Integer 2 Migrating Data to the World Import Text Options EMPOUT TXT x IX First Row Contains Field Names ox Table Options _Cancet_ Data Source Microsoft Access Text Delimited Text Fixed Width Microsoft Excel 2 0 4 0 Microsoft Excel 5 0 O Create New Table Lot KS A z Lotus wR Fmi le Employees RA Paradox 3 X Specification Name amp j Paradox 4 X File Type Windows ANSI RA Text Delimiter f Field Separator Dates Times and Numbers Date Order YMD TT Leading Zeros in Dates Time Delimiter
9. list the department number will be extracted twice because it is in both tables You could then create the Empdept file immediately bypassing the Suprlink step and going straight to STExport 17 For Techies References 17 Migrating Data to the World Importing into Oracle UX at Load the file into the Oracle table The load specs are in their own control file sqlload userid username userpass control loaddate ctl log load log 18 You can import the data into an Oracle UX database with the For Techies SQL Loader utility To execute SQL Loader you can do sqlload userid username userpass control loaddata ctl log load log where sqlload is the filename of the SQL Loader program userid is the username and password to use to connect to the database control is used to point the file containing the load specifications shown on the slide log is requesting SQL Loader to write the various messages generated during the load operation including error messages References 18 Migrating Data to the World Importing into Oracle UX O e The Control File load data infile users mike oracle data empdept append into table employees fields terminated by optionally enclosed by employee_name employee_number status address city province_state country postal_zip_code home_phone_number sex social_insurance_number hire_date marital_status spouse_name monthly_salary bank_name bank_accou
10. nt_id birth_date title department_name work_phone_number last_review_date vacation_days 19 load data load command For Techies infile name of the data file append into table destination table insert assumes the table is empty append creates new rows replace deletes existing rows before loading truncate is the same as replace fields terminated by character used between column values optionally enclosed by character used around text strings employee_name column names in the same sequence as the data file References 19
11. ou have a field in your table which is a non duplicating index The same concept is at work in IMAGE SQL master datasets which cannot have two records with the same key value To generate the error shown above I simply repeated the import that was successful on the previous page When records cannot be imported MS Access creates a new table called the Import Errors table which describes the error that occurred and the field and row record number that the error applies to You can delete the Import Errors table after you have analyzed the errors References 14 Migrating Data to the World Names or Fields Don t Match at e Microsoft Access Can t import table or query No records found or all records contain errors e Table Import Errors Hans Hendriks v Peron Field Row O eee Employee Number Type Conversion Failure Employee Number Type Conversion Failure Employee Number Import Errors are the result of MS Access seeing data in the import file For Techies that it cannot load into the table Some typical errors are If importing a text file takes an unexpectedly Field Truncation long time many errors e Type Conversion Failure are happening To Key Violation cancel importing press CTRL BREAK e Validation Rule Failure e Null in Required Field e Unparsable Record What caused this error All I did to create the error shown above was forget to check the First Row Cont
12. rences Migrating Data to the World The IMAGE Data Looks Like This i gt get employees list xeq ADDRESS 307 2222 Edinburgh BANK ACCT 001 2547 66983 BANK NAME Toronto Dominion BIRTH DATE 19700214 CITY Richmond COUNTRY Canada DATE HIRED 19920304 DEPARTMENT NO 10 EMPLOY STATUS 1 EMPLOYEE NO 5557 HOME PHONE 604 574 2627 MARITAL STATUS 2 NAME Grinham Robert POSTAL CODE V9H 2R6 PROVINCE CODE BC REVIEWED DATE 19960501 SALARY 4000 00 SEX M SIN 689521478 SPOUSE NAME TITLE Administrative Clerk VACATION DAYS 15 WORK PHONE 604 244 4000 x2587 4 EMPLOYEES Master Set 1 For Techies Entry Offset ADDRESS x20 1 BANK ACCT X20 21 BANK NAME X20 41 BIRTH DATE I2 61 lt lt YYYYMMDD gt gt CITY X20 65 COUNTRY X20 85 DATE HIRED I2 105 lt lt YYYYMMDD gt gt DEPARTMENT NO Il 109 EMPLOY STATUS Il 111 EMP LOYEE NO I2 113 lt lt Search Field gt gt HOME PHONE X20 117 IARITAL STATUS Il 137 AME X20 139 POSTAL CODE X10 159 PROVINCE CODE X2 169 REVIEWED DATE I2 EVL lt lt YYYYMMDD gt gt SALARY I2 175 lt lt 2 gt gt SEX x2 179 References SIN I2 181 SPOUSE NAME X20 185 TITLE X20 205 VACATION DAYS Il 225 WORK PHONE X20 227 Migrating Data to the World The MS Access Data Looks Like This The table already exists in an MS Access database W
13. sfer Type shew Hostries fasci x ee If File Exists pr Ask User x Gal fi dumbo sys v Close 12 Suprtool has no built in file transfer capability You have a range of For Techies options to get the file from the host server to the PC including WRQ s FTP client capability Reflection file transfer and FTP is included in MPEAX 5 0 References 12 Migrating Data to the World Import the Data into MS Access at gt Select File Microsoft Access Import to Employees finished 2 records processed No errors were detected List Files of Type Drives Delimited Text txt E d drive_d 13 MS Access lets you know the outcome of the import operation For Techies References Migrating Data to the World Errors While Importing Microsoft Access Import to Employees finished 2 records processed 2 errors O occurred Error descriptions with associated row numbers of bad records can be found in Microsoft Access table Import Errors Hans Hendriks Table Import Errors Hans Hendriks Bg Emor Field Row T Key Violation 2 _ Key Violation It s pretty easy to get errors during the import step Usually errors For Techies happen because the import file doesn t match the layout that MS Access is expecting More on this later A common error is the Key Violation which results when y
14. the start and end of text fields If we are creating a new table then there is no need to synchronize the import file with anything All we need to do is to put the fields in the desired order If the first row of the file contains the field names then those names will be used If not the columns will be named 1 2 3 etc If we are appending the data to an existing table then either the fields in the import file must be in the same sequence as those in the table or the first row of the file must contain field names The names must match the names in the MS Access database though the sequence need not be the same Note For our example we will create a file that has all the fields required but we ll use whatever field sequence is produced by Suprtool and Suprlink We ll put field names in the first record so that MS Access will know how to load the data For Techies References Migrating Data to the World Select the Data Using Suprtool at gt get employees sort department no output emp link xeq get departments sort department no output dept Link xeq 8 The Employees dataset has department number while the target MS For Techies Access table has department name We have another IMAGE SQL dataset that associates department numbers to names so we extract that data and use Suprlink to add the department names to the file we ll feed to MS Access The Departments dataset looks like this
Download Pdf Manuals
Related Search