Data Tools only work for Windows XP. Click Here for a solution

Jul 28

Written by:
7/28/2011 7:53 AM 

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.

Procedure

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

1. Record Length
2. Define Field Length, Data Type, and Set Data Field Properties
3. Use the Export Functionality

Record Length

In this section you will see how the Data Parser for C-ISAM reads the limited amount of metadata in a C-ISAM data file, automatically disregards the file header, and detects and displays each record correctly in the built-in visual parser window.

1. When the Data Parser is launched you will see the Source Connection window.
2. At the top of the window, click on the Source Connection box arrow and select the C-ISAM on the Factory Connections tab.
3. Back in the Source Connection window, click the Source File/URI arrow and select the SrcCISAM.dat file from the folder where you saved the file (see Design Considerations above).
4. Click Open.
5. Click on the Structured Schema arrow and select Manually Parse from the list.

You should now see the data records in the Data Parser window. The visual parser detects the record separator automatically.

The data records should display in a uniform manner although there are no data field definitions yet. Go to the next section to parse and define the records.

Define Field Length, Data Type, and Set Data Field Properties

In this section you will define the length, data type, and field properties for each field in the data records.

It is important to define the fields from left to right. If you define a field size incorrectly, it throws off the definitions for every field thereafter.

1. Click the last character of the first field in the top row of data.

This is the pale blue row beneath the yellow ruler. In this example, this is the final ""s"" in ""Us"" of Conversions R Us. A field marker (a double-ended arrow) displays at this position in the yellow area beneath the ruler.

In this case, it is easy to see where the first field ends because it is an ordinary text field and ends right where an incremental numeric data field starts.

See the record layout in the table below. The table provides the field type, size, and other data type properties needed to define each of the fields in the records.

Note: To delete a marker, click the ruler hash mark and the marker disappears. Then, create a new marker in the correct position.

2. Beneath the data parsing area is a Field Name box. Highlight the default field name, Field1, and rename the field Company.
3. Click the yellow area directly above the field. The Contents box shows the data for that field formatted according to the Length, Data Type and Properties you set up.

Remember to NOT click the blue area unless/until you are ready to set the length of the second field.

4. For Field 2, count four positions on the ruler and click the mouse to mark the end of the second data field. A marker displays at the 20th position and the Property Size value is 4. Rename the field RecordNumber.
5. Click the Data Type arrow and select Zoned Decimal from the list of data types.

The Offset box in the lower left displays the starting position of each field.

6. Enter the Property values for the second field. Click the Signed arrow and select No.
7. You do not want to display figures after the decimal point for this field, so in the box labeled Decimals, highlight the default value and type 0 (zero).
8. Press the UP arrow on your keyboard to move the ""focus"" to a different property value.
9. Count four hash marks to the right of the last field's arrow and click your mouse on the ruler. Rename Field3 to DegreeOfVar.
10. Select Type as Smallfloat.
11. Under Property, scroll down to Decimals and type 3.

Note: The Contents box is the best visual cue when trying to set the data type and properties of a field correctly. If you know what the field should contain, you are better able to judge whether you have set the field length, data type, and data properties correctly. When you parse a file for which you do not know the schema/structure, you may need to go through the process of choosing each field's length, type, and properties by trial and error. You must click in the yellow area below the ruler each time you want to view a field's unpacked contents in the Contents box.

12. Use the information in the table below to continue defining the remainder of the fields, starting with Field 4.
13. Do not place a field marker at the end of the last field, as this is also the end of the record. Instead, in the Field Name list, select Field9, rename it Serial Number, and make sure the Size is 9.
14. Click the Incremental Save button when you are satisfied with the results of each field.

Caution: When you are finished parsing the data, you must save your changes, because they are not automatically saved as you go along. If you close the Data Parser without saving, you must start over. In the toolbar, click Incremental Save.

15. Click OK to close the visual parsing window.
16. Click Save Map.
17. Type Parse_CISAM_Sample and click Save. Your schema work is saved.

C-ISAM Sample File Schema Table

Field No.	Name	Type	Size	Content of First Record	Other Properties
1	Company	Character	16	Conversions R Us	(None)
2	RecordNumber	Zoned Decimal	4	1	Precision: 4; Signed: No; Sign Pos: Trailing; Style MicroFocus COBOL; Scale: 0; Decimals: 0
3	DegreeOfVar	Smallfloat	4	35.957	Precision: 7; Signed: Yes; MSB First: Yes; Decimals: 3
4	Bytes	Zoned Decimal	9	3456680	Precision: 9; Signed: No; Sign Pos: Trailing; Style: MicroFocus COBOL; Scale: 0; Decimals: 0
5	ErrOccur	Smallint	2	True	Precision: 4; Signed: No; MSB First: Yes
6	Reversal	Display Sign Trailing	10	-99756	Precision: 10; Signed: Yes; Sign Pos: Trailing; Style: MicroFocus COBOL; Scale: 0; Decimals: 0
7	Cost	Comp-3	4	20763.25	Precision: 7; Signed: No; Sign Pos: Trailing Separate; Style: MicroFocus COBOL; Scale: 2; Decimals: 2
8	FileNumber	Character	10	file 7668	(None)
9	SerialNumber	Character	9	DB9785500	(None)

View Parsed Data

To view a representation of the unpacked source data, click on the Source Data Browser button in the button bar. If the schema is correct you will see a visual representation of the unpacked data in a tabular (row and column) format.

If the data appears to be incorrect or inconsistent, go back to the beginning and locate the setting(s) and/or properties that need to be changed.

Use the Export Functionality

In this section you will export the unpacked data to a CSV text data file.

1. In the main button bar, locate the Run Map button and click on it.
2. On the Connect Info tab, click the Export File Type arrow.
3. Select ASCII (Delimited) from the list.
4. In the Target File/URI box, enter the folder path and desired filename for the CSV text file that will be created when you export the unpacked data from the Data Parser.
5. On the Properties tab, find the Header property and change the default value from False to True.
6. Click on the Run Export button below the Properties grid.
7. Click the Save Map button in the button bar. Enter a document name and click Save.

Be sure to retain the .parse.xml file extension.

View Your Target Data

If all worked as intended, you just created a target data file that contains the unpacked source data in a CSV data file format. To view the contents of the target data file without having to exit the Data Parser, click on the Target Data Browser button in the button bar. You can verify that the data is accurate and complete by also opening the Source Data Browser and placing the source and target browser windows side by side on your workstation's desktop.

Data Parser Troubleshooting Notes

• If the Data Parser arrows do not exactly mark where a field's data begins, this is not necessarily a problem. Look at the field's Size value and check the Contents box to make sure the data in that field is unpacked and is displaying correctly.
• If there are extra characters or if characters are truncated at the start of records, the Start Offset value is incorrect. Extra characters indicate that the StartOffset value needs to be increased. Truncation indicates that the StartOffset value needs to be decreased.
• If patterns in the data slant to the left or right, the record length is incorrect. If the patterns slant to the left, decrease the record length. If they slant to the right, increase the record length.