Write Data to a File
Data Migration can be used to export data from database objects to files.
Write data from a single source
First Define the job.
Create a new data migration job and enter values for Job ID, Description, Procedure Name, View Name, Group ID (optional) andFile Location, Column Separator(according to the file)
In File Configuration it is also possible to enter other characteristics such as Date Format, String Delimiter, Decimal Point, Thousands Separator and Minus Pos.
To write data to a file, the value in Procedure Name must be CREATE_OUTPUT_FILE.
The Rules tab is populated automatically with rules that apply to the type of job created. Other rules may be added.
The File Mapping tab is automatically populated with column names from the view specified in View Name.
Only columns with a value greater then 0 in Pos will be written to the file. Columns that is not wanted in the file can be removed.
With this you are ready with the Migration Job. next is to Execute the job. To execute the job, go the Execute Job form and Start the job online or backgeound. Or else, you can schedule the job.
Once executed online, you will get the Log. Go through it to check any correction needed or you are good to go.
When File Location = OnClient the output will be loaded into the Detail tab. For OnServer, output will be written to the file directly.
Data can be written to a file with the command Export to File... in the Detail tab.
The Export to File... dialog will take default values from File Path and File Name in File Information in the Formatting tab. The Character Set value is not used when File Location = OnClient. The file will be created in the client character set.
Write data from several sources
Data can be selected from several sources. Add sources in Source Name and add alias if necessary. Add join condition and other conditions in the Where column.
The File Mapping tab may have to be changed. Columns that exist in more than one source must have the Column Name prefixed with the source alias. Column Name can not be changed so a new line must be added and the old one removed.
The file header will contain the alias prefix on the column names. This can be avoided by adding alias in Column Name. SQL expressions may also be used in Column Name.
Write data to a server file
- When File Location = OnServer File Path must be a valid directory object. The column has a List-Of-Value with valid directory objects.
- The File Name can contain any of the keywords &DATF, &DATE, &TIME, &USER, &SEQN and/or &GUID. &DATF will be replace with a date in the format specified inFormatting tab - File Configuration - Date Format, &DATE will be replaced with a date in the format YYYYMMDD, &TIME will be replaced with time in the format HH24MISS, &USER with the Foundation1 user id, &SEQN with a sequence number and &GUID with a hexadecimal string.
- If a Character Set is specified the file will be created in that character set, if a character set is not specified the file will be created in the database character set. The Character Set column has a List-Of-Value with valid character sets.