We have a routine that loads data from an uploaded Excel spreadsheet. Lots of columns with numbers. The problem comes when one of the columns has no entry. The data is theoretically a number, but when the file is uploaded via the portal interface WebFOCUS creates an A1V format for the no-entry column instead of an I5 like it does all the other columns. The result is that some of our number columns aren't numbers in WF.

So . . . convert type, right? The problem is we don't know what columns this will happen to in advance.

So here's the question -- is there a function (or any other way) to detect if a field is a character type or a number type, so that it can be converted only when it needs to be converted? The IF statement would more or less go like this --

DEFINE FILE INPUT_EXCEL
  COLUMN3_FIXED/I5 = IF COLUMN3 is an alpha type instead of a number type THEN EDIT(COLUMN3)
                                                                          ELSE      COLUMN3;
END

This message has been edited. Last edited by: FP Mod Chuck,

https://webfocusinfocenter.inf...ang/source/edit2.htm

Alternatively, you could add a row of dummy values with the right formats always populated and filter it out in the master based on the value of one of the fields. That way all columns will have a legitimate format but the dummy row is eliminated in all reports.

Thanks for replying David!

That is an examination of the contents of an alpha string. The question I'm asking is a step higher -- is the field defined as an alpha string? From run to run I don't know the format of a particular field.

You too Babak! But EDIT2 balks when you send it an Alpha format. I'm either getting an alpha or an integer, I don't know which. If there was a WF function (or iWay) that simply said, "don't care what it is, I'll convert to alpha" we'd be set. But I don't think that exists?

Can you put a single row with dummy values and filter them out? That way the upload will format the columns correctly. You'll just have to add a WHERE test to omit that one row and I think you can do that using DBA techniques.

When you ask for format of the field, is that the Master File column format or the data content ?

For a Master File column format you can query the catalog with SYSCOLUM to get the USAGE, then change the code.

For content, the format should always be ALPHA and then you can use the function PATTERN to determine of there are alpha chars in the string and convert appropriately.

quote:

file is uploaded via the portal interface

Questions:
Do any of the rows in the column actually have a value?
Depending upon the answer to the first question, what sample size are you using during the upload and could you increase it to all rows?

T

It's the format defined in the Master File. The problem is that the master file is dynamic. It shouldn't be, as it's the same each time someone uploads a file. But the hitch is that columns that don't have a number entry in them default to character instead of integer.

The end-user uploads an Excel file through the portal. When they do a master file is created by WF automatically. It's a short little thing, maybe 25 rows. But it's user-driven so we're trying to make it as simple as possible for them, and bullet-proof in implementation.

That master file does not have a dependable structure in spite of it being the exact same thing each month, due to this odd behavior in the WF software that creates the master file. This was going to be easy-peasy but we have DEFINEs that total up rows and these errant text fields in the defines throw an error.

So yeah, the original question still stands -- any way to query the format of a field in WebFOCUS? As it stands we're likely going to have to re-engineer the process, or put a macro into the spreadsheet that forces zeros into the fields.

Could you possibly take advantage of APP PATH? e.g. where does the synonym get written when the user uploads a file and can you force an APP PREPENDPATH to their domain to ensure that the synonym you want to use gets read first?

T

You're saying build a static Master File.

If the column is supposed to be a number but is read as alpha because it was empty then you could create an IF THEN ELSE to check if the column is missing. If the column is missing, skip the calculation or change it to numeric format, otherwise, it could be used in a calculation. If you have anything other than a missing value then, your user has loaded a real alpha field into a number anyways.

Let's say FIELD1 has nothing in it when it's loaded from Excel. WebFOCUS uploads it into an Alpha field and makes it a null value. Now, let's just calculate a new field in DEFINE that checks to see if the value is null. If it is, then you know it's supposed to be 0 or set it to whatever you want. Otherwise, just set it to the original field value.

 
DEFINE FILE TESTFILE
FIELD1_X/I9=IF FIELD1 IS MISSING THEN 0 ELSE FIELD1;
END
TABLE FILE TESTFILE
PRINT FIELD1 FIELD1_X FIELD2 FIELD3
END 

quote:

Originally posted by BabakNYC:
If the column is supposed to be a number but is read as alpha because it was empty then you could create an IF THEN ELSE to check if the column is missing. If the column is missing, skip the calculation or change it to numeric format, otherwise, it could be used in a calculation. If you have anything other than a missing value then, your user has loaded a real alpha field into a number anyways.

Let's say FIELD1 has nothing in it when it's loaded from Excel. WebFOCUS uploads it into an Alpha field and makes it a null value. Now, let's just calculate a new field in DEFINE that checks to see if the value is null. If it is, then you know it's supposed to be 0 or set it to whatever you want. Otherwise, just set it to the original field value.

 
DEFINE FILE TESTFILE
FIELD1_X/I9=IF FIELD1 IS MISSING THEN 0 ELSE FIELD1;
END
TABLE FILE TESTFILE
PRINT FIELD1 FIELD1_X FIELD2 FIELD3
END 

We gave this one a shot and couldn't make it work without throwing an error when the field is an integer.

Check SYSCOLUM, and handle accordingly.

Should be easy

quote:

Originally posted by Waz:
Check SYSCOLUM, and handle accordingly.

Should be easy

SYSCOLUM is giving me information on about a dozen tables, none of which I've ever heard of. That's all. Is there a way to get it to expand its horizons to include master files I've created?

Before continuing, please advise what version of WF you are using - it could mean a different recommendation! Until you let us know, I am assuming that you are using 8.2.03 at least.

Your very first comment states "We have a routine that loads data from an uploaded Excel spreadsheet". Is this a WebFOCUS routine - I am guessing so otherwise why would you be asking about changing synonym columns!!!

So,

• How are your users uploading the Excel S/S?
  
• Where are they loading it to?
  
• Are they using the same file name each time?

The synonym that is created will have a DATASET attribute that identifies the uploaded file location, so you could make a copy of that synonym and delete that dataset attribute. Then make modifications to the USAGE attributes and place the resulting synonym in an app folder that the users do not have access to.

In your "routine that loads data" (assuming it is WebFOCUS), issue an APP PREPENDPATH to the app folder containing your static copy of the synonym (created above) and follow that with a FILEDEF to the location of the uploaded Excel S/S.

Better still, rather than them upload it each time, just tell them to save it to a central network drive that your "routine that loads data" points at. Then your WebFOCUS load routine could just pick it up from the same place each time.

If you wanted to get clever, you could interrogate the network drive folder to identify the latest file and then dynamically issue the FILEDEF. You could then change the extension of the file so that you do not pick it up a second time.

So many different ways of doing this but, IMHO, changing the synonym each time is not the way to go.

Just my "two penneth" worth.

T

quote:

Originally posted by BabakNYC:
http://forums.informationbuild...507073986#5507073986

SYSCOLUM -- Alright, so, that's half a mile of source code. I'll boil it down to what you need --


TABLE FILE CAR
PRINT *
ON TABLE HOLD AS EDWARDS
END


TABLE FILE SYSCOLUM
PRINT USAGE
BY TBNAME
BY NAME
WHERE TBNAME EQ 'EDWARDS';
END



This produces what I'm looking for, for hold files and for static masters.



Oddly enough, this does not work --

-* Code that does not work:
TABLE FILE SYSCOLUM
PRINT *
WHERE TBNAME EQ 'EDWARDS';
END

Doesn't work at all. Doesn't return rows. Don't know why, but don't care much. Thanks for the tip. This gives me what I need.

SYSCOLUM is a multisegment master, and if you PRINT * it will try to pull from all segments, some of which will have not data.

If you are going to use PRINT * (Never recommended), use PRINT SEG.NAME to get the fields in the NAME segment, etc.

For what it's worth, you can also use CHECK FILE to bring back the formats

    CHECK FILE car HOLD AS INFO
    TABLE FILE INFO
    PRINT FORMAT 
    BY SUFFIX     
    BY FIELDNAME 
    ON TABLE SET PAGE-NUM OFF
    END

Returns:

This message has been edited. Last edited by: Hallway,