Pervasive DataTools Weblog

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: