Get the most out of
your Robelle Tools
Often when the request comes to us, the users have already exported the data out of Excel and are trying to get the data into Suprtool and subsequently back into their database.
The biggest problem facing users is how to get data that does not have leading zeroes or is left aligned into Suprtool. We have a future enhancement planned to deal with this issue. However, there are some very simple steps that you can take now to get the data into a format more usable by Suprtool or COBOL, and subsequently Image.
Typically in Excel,
numbers entered directly into Excel use the general or
the number formats. When they are saved as a txt file the
numbers are difficult to import into Suprtool, because they are
usually right aligned and have a decimal point in them.
The best way to get numbers into Suprtool or COBOL is if they have leading zeroes when they are exported from Excel. (See Figure 1 below for the before view of the data) Once this is done, they can be defined in Suprtool as a Display type.
As you can see below, when
you save the file in Excel as a text file, the file has the decimal
point in them and they are right aligned. To import them into a
database using Suprtool, would require multiple passes.
A simple change to the number format from Number to Custom, as shown above, results in data that can be easily imported with Suprtool or COBOL.
This changes the data in the Excel spreadsheet to look as shown in Figure 3:
When you save the data to
a text file, then you can easily import the data into Suprtool.
Notice that you must
define the beginning position of the fields as they appear in the
file. If you had to have the numeric fields in say double integer
format, then the task becomes:
For columns with two decimal places, changing from the two decimal places to the custom format will cause the number to be a rounded whole number. For example 12.53, would become 13. In order to get rid of two decimal places you just have to multiply by 100. To do this, just put 100 into any blank cell (see Figure 4:)
Select the cell and
choose Edit, Copy. Then select the range of values and choose Edit,
Paste Special. Choose Multiply and click OK. Then delete the cell
that contains the 100.
These techniques work well if the data has no negative values. If the data has negative values, you have to make two passes. Lets say the field num1 has a negative number and the sign is in position 30 of the input file, the task then becomes: