HowTos

Fri, 05 Mar 2010, 5:22pm

Question:

I have a source text file that contains two fields/columns. In the first column there are product SKUs, each of which is repeated on multiple rows. The second column contains the SIZEs of each of the products that I have in stock. For example, if I have 6 different SIZEs of one SKU, there are 6 rows for that SKU in my source file. How can I combine the data into a target text file that contains just one row for each SKU and each of the available SIZEs on that same row and in size order and separated by a vertical bar?

The format of the source file looks like this:

"SKU","SIZE"

"1111A",8

"1111A",6

"1111A",16

"1111A",12

"1112B",13

"1112B",8

"1112B",5

"1112B",16

"1112B",12

"1113C",6

"1113C",8

"1113C",14

I would like the format of the target file to look like this:

1111A 6|8|12|16

1112B 5|8|12|13|16

1113C 6|8|14

Answer:

Using the Data Loader to CSV Text or the Data Loader to Fixed Text, you can transform the format of the data exactly as you have described and illustrated.

Objective

There is one objective in this sample transformation map, as follows:

Wed, 03 Mar 2010, 3:59pm

Question:

For the past 2 days I get an "Overflow (6)" error message when I launch my DataTools product for the first time each morning. What is causing the error, and how can it be fixed?

Answer:

The Cause

The error is usually the result of “corrupted” window dimension/location values in the “settings” file for the product. This is sometimes caused when the application is closed (intentionally or not) with one or more of the application's windows in a position that is outside the visual boundaries of your computer screen.

The Solution

The solution to the problem depends on which of the DataTools products is affected. Each product within a "category" (i.e., Extractors, Loaders, Parsers, etc.) creates/edits and uses a unique "settings" file each time the product is launched and closed. The "settings" files reside in the following path, depending on the operating system of your workstation on which DataTools is installed, as follows:

On Windows XP:

C:\Documents and Settings\[YOUR_USERNAME]\Application Data\Pervasive\DataTools9\Common\Settings

The following list includes some of the DataTools product category names and the associated "settings" file name:

• Data Joiner - DataJoiner.xml
• Data Loaders - DataLoader_Settings.xml
• Data Extractors - DataExtractor_Settings.xml
• Data Parsers - DataParseSettings.xml
• Data Parser for Unstructured Text - ExtractorSchema.xml
• Data Viewer - Browse.xml

IMPORTANT NOTE: For the Data Parser for Unstructured Text back up the extractor900.mdb file prior to deleting the settings file. Otherwise, you will lose any/all saved extraction scripts. The extractor900.mdb file resides in this folder path (Windows XP): C:\Documents and Settings\jterrell\Application Data\Pervasive\DataTools9\Common

Thu, 18 Jun 2009, 4:28pm

Question:

I have a C-ISAM file that contains packed and binary data. How can I parse and define the structure of the file and export the unpacked data to a CSV text file format?

Answer:

Using the Data Parser for C-ISAM, you can define the structure of the data records by using the visual parser window, and once the data is parsed and unpacked, export the data to a CSV text file.

Objectives

There are two objectives in this DataTools HowTo, as follows:

• Use the Data Parser for C-ISAM to define the schema/structure of a fixed-length C-ISAM data file
• Export the unpacked data to a CSV text file format

Skill Level

• Basic to Intermediate

Skill Set

• Data Parser
• Built-in visual parsing interface
• Basic understanding of C-ISAM data storage formats

Design Considerations

The principal design consideration is to follow the procedural steps below in the order in which they are specified in this HowTo. A secondary consideration is to follow the same basic procedural steps, and in the same order, when you begin to define the structure/schema of your own data file.

The DataTools product used in this DataTools HowTo is the Data Parser for C-ISAM.

The sample C-ISAM data file used in this HowTo is a simple fixed-length data file that contains character, binary, and packed data. The C-ISAM connector requires that the data file and its matching index file reside in the folder on your workstation. The sample data and index files (SrcCISAM.dat and SrcCISAM.idx) were NOT copied to your workstation when DataTools was installed, but you can obtain the sample files by posting a request in the user forum for the Data Parsers. We will zip and email the files to you.

Tue, 16 Jun 2009, 1:39pm

Question:

I have a Btrieve data file that contains packed and binary data. How can I parse and define the structure of the file and export the unpacked data to a CSV text file format?

Answer:

Using the Data Parser for Btrieve, you can define the structure of the data records by using the visual parser window, and once the data is parsed and unpacked, export the data to a CSV text file.

Objective

There are two objectives in this DataTools HowTo, as follows:

• Use the Data Parser for Btrieve to define the schema/structure of a fixed-length Btrieve data file
• Export the unpacked data to a CSV text file format

Skill Level

• Basic to Intermediate

Skill Set

• Data Parser
• Built-in visual parsing interface
• Basic understanding of Btrieve and packed data storage formats

Design Considerations

NOTE: Before you begin the procedures in this HowTo, you must have a Btrieve or Pervasive.SQL Engine installed and configured on your system, and DataTools must be able to detect it. Refer to this page of our web site for details:

DataTools HowTo: Resolve an Error in the Btrieve Parser - Client Missing

The principal design consideration is to follow the procedural steps below in the order in which they are specified in this DataTools HowTo.

The DataTools product used in this HowTo is the Data Parser for Btrieve.

Mon, 15 Jun 2009, 4:45pm

Question:

I have a source file that contains packed and binary data. How can I parse and define the structure of the file and export the unpacked data to a CSV text file format?

Answer:

Using the Data Parser for Binary, you can define the structure of the data records by using the visual parser window, and once the data is parsed and unpacked, export the data to a CSV text file.

Objective

There are two objectives in this DataTools HowTo, as follows:

• Use the Data Parser for Binary to define the schema/structure of a fixed-length data file
• Export the unpacked data to a CSV text file format

Skill Level

• Basic to Intermediate

Skill Set

• Data Parser
• Built-in visual parsing interface
• Basic understanding of binary and packed data storage formats

Design Considerations

The principal design consideration is to follow the procedural steps below in the order in which they are specified in this HowTo. A secondary consideration is to follow the same basic procedural steps, and in the same order, when you begin to define the structure/schema of your own data file.

The DataTools product used in this DataTools HowTo is the Data Parser for Binary.

The sample binary data file used in this HowTo is a simple fixed-length data file that contains binary and packed data. The sample data file name is TUTOR4.BIN and it was copied to this folder when DataTools was installed on your workstation, assuming the product was installed in the default location:

C:\Program Files\Pervasive\DataTools9\Common

Procedure

This tutorial is divided into the following tasks, which you should complete in the order shown:

Thu, 11 Jun 2009, 12:42pm

Question:

I have a source text file that contains a field in which there are dates stored in five different character string date formats. How can I standardize the dates to a single format?

Answer:

Using Map Designer in any of the Pervasive DataTools Data Extractors or Data Loaders, you can transform a variety of date formats into one standardized date format in your target file or table.

Objective

There is one objective in this sample transformation map, as follows:

• Use the Rapid Flow Integration Language (RIFL) to write an expression in a target data field to transform a variety of date formats from the source file into a one standardized date format in the target data file.

Skill Level

• Basic

Skill Set

• Map Designer
• Basic RIFL Scripting
• Basic understanding of date formats and the importance of storing dates in a single, standard format

Design Considerations

The principal design consideration is to choose the date format that fits your business process requirements. Following this decision, the task is to write a RIFL expression that converts any date in the source file to the chosen standardized target date format.

The DataTools product used in this sample transformation map is the Data Loader to CSV Text.

The selected standardized date format in this sample is mm/dd/yyyy.

Procedure

Launch any of the DataTools Data Loader to CSV Text. Create a new transformation map, connect to the source and target data files, and perform the data field mapping and expressions, as follows:

Source Connection

The Source Connection tab is where you specify the source connector, select the source data file or table, and specify any special properties about your source data file. For this sample select the following:

Thu, 14 May 2009, 2:46pm

Question:

There are records in my source file that I do not want to convert to my target file because they do not meet specific requirements. I know I can filter these records out completely, but can they be written to a different target file in the same map?

Answer:

Using Map Designer in any of the Pervasive DataTools Data Extractors or Data Loaders, you can filter records that match or fail specified criteria and write these records to a reject file. The resulting records in the reject file can be inspected, thus allowing you to determine how to handle those records for further processing, or not.

Objectives

There are two objectives in this sample transformation map, as follows:

• Use the Rapid Flow Integration Language (RIFL) to write an expression in the Account No target field to prevent certain data records from being written to the target data file.
• Use the reject records subsystem to write the rejected records to a reject file where the rejected records can be reviewed manually and/or create a separate transformation map to correct the situation(s) that caused the records to be rejected.

Skill Level

• Basic

Skill Set

• Map Designer
• Basic RIFL Scripting
• Event Handlers and Actions

Design Considerations

This transformation map selects records based on values in a data field in the source file. The field of interest is Account No. The goal is to write to the target only the source records that fall within a specified range of account numbers. We accomplish this goal by writing a simple RIFL expression in the Account No target field. The records from the source file that do not fall within the specified range of account numbers will be written to a "reject file".

Wed, 13 May 2009, 1:17pm

Question:

Can I use a COBOL copybook (FD) to define the fields in my COBOL data file? And if so, how?

Answer:

Yes, each of the following DataTools Data Parsers includes a feature that allows you to use a COBOL copybook to parse COBOL data. Detailed steps, including screenshots, are provided for you in the attached PDF document.

• Data Parser for Binary
• Data Parser for C-ISAM
• Data Parser for Micro Focus COBOL

Adobe Reader is required to read the attached document.

Fri, 01 May 2009, 12:21pm

Question:

Is there a way to change the orientation of my data from rows to columns?

Answer:

This "DataTools HowTo" contains an example of a data transformation showing how data stored in a row format in your source data file can be transposed into a columnar format in your target data file.

The DataTools product used in this sample transformation map is the Data Loader to Fixed Text.

Source Connection

The source connector is ASCII Delimited, and the source file is TUTOR1.ASC. This is a sample CSV text file that resides in the main DataTools installation folder on your workstation.

Target Connection

The target connector is ASCII Fixed, and the target file that will be created after running the transformation map will be TRG_TRANSPOSE.ASC. You may write the target file to any folder to which you have access and permissions.

Transformation and Map Properties

To accomplish the horizontal-to-vertical transformation, the Event Handlers and Event Actions must be used in the Transformation and Map Properties window. To access this window click on the View menu and select Transformation and Map Properties from the menu options.

Four Event Actions are created in the Transformation Map Properties. The following describes how to create these Events.

Mon, 09 Mar 2009, 11:12am

Question:

When I try to connect to a Btrieve data file, I get an error 161.

Answer:

A Btrieve Error 161 indicates that the Pervasive.SQL user Count is exceeded. You may need to check with the DBA and request that any "duplicate" login(s) be shutdown, or purchase sufficient licenses for the number of users who need to simultaneously access the Pervasive.SQL database.