Robelle Sponsor Message

April 2003

Get the most out of your Robelle Tools

Importing Data from Excel to the 3000

By Neil Armstrong

In previous columns, we have shown how to get data out of various databases and into other applications, such as SQL Server, Oracle and Excel. Occasionally we get requests on how best to put data back into Image or Eloquence databases from various applications, typically Excel.

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.

Prepare Excel Data for the Host

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.

Neil Armstrong 12.00 14626
Bob Green 12.00 15253
Janine Edwards 14.00 18123
Trenton Roach 34.00 22154

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.

Neil Armstrong 0000000012 0000014626
Bob Green 0000000012 0000015253
Janine Edwards 0000000014 0000018123
Trenton Roach 0000000034 0000022154

Once you have transferred the data to your host system, the trick is to define the input numeric fields as display type. For example the code to get the data input in a format that is usable in Suprtool would be as follows:

Input importf
def name,1,30
def num1,32,10,display
def num2,43,10,display
ext name
ext num1
ext num2
out excelf,link
exit

Import as Double Integers

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:

input importf
def name,1,30
def num1,32,10,display
def num2,43,10,display
def dbl-num1,1,4,double
def dbl-num2,1,4,double
ext name
ext dbl-num1 = num1
ext dbl-num2 = num2
out excelf,link
exit

Using this method, Suprtool is capable of coercing the imported display data fields num1 and num2 into double integers (or other numeric storage types) in a single pass.

Decimal Places

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.

Negative Values

These techniques work well if the data has no negative values. If the data has negative values, you have to make two passes. Let’s say the field num1 has a negative number and the sign is in position 30 of the input file, the task then becomes:

input importf
set squeeze off
def name,1,30
def num1sign,31,1
def num1,32,10,display
def num2,43,10,display
def dbl-num1,1,4,double
def dbl-num2,1,4,double
if num1sign = “-”
ext name
ext dbl-num1 = num1 * -1
ext dbl-num2 = num2
out excelf,link
xeq
input importf
if num1sign <> “-”
ext name
ext dbl-num1 = num1
ext dbl-num2 = num2
out excelf,append
exit

We are planning a new Suprtool function to require only a single pass for accepting negative and positive values, so stay tuned.

This article is archived at www.robelle.com/tips/st-export-excel.html#import on the same page as our article on Exporting from the 3000 to Excel. You might also be interested in two other articles on exporting to Oracle (www.robelle.com/tips/st-export-oracle.html) and exporting to XML (www.robelle.com/tips/st-export-xml.html).