Hi Everyone,

I'm in the process of exporting all of the data from a WebFocus 5.1 application to SQL Server and am running into two issues that seem fairly basic.

1) A number of the YYMD fields to not seem to be exporting correctly. I'm exporting them as DATEFILED/A25 and the column shows up as a char-25 in SQL Server but apparently WebFocus isn't doing the conversion automatically.

To expand on this a little further, the main issue I'm running into is that some dates are 01999131 which should be January 31st of 1999.

2) Is there a simple command to export the columns using the field_name and not the alias? I want to be able to do a PRINT * and not have to do PRINT COL1 AS COL1 COL2 AS COL2 etc. (ie - all my columns are showing up in sql server as crdno instead of creditno)

Here's my code for your reference. I would be extremely grateful if anyone could point me in the right direction.

Have a good day!
Michael

SET HOLDLIST=PRINTONLY
SET HOLDATTR=ON
TABLE FILE CRUCC
PRINT CREDITNO AS CREDITNO DATETIMEIN DATAENTRYPERSON SECUREDPARTY ADDRESS CITY STATE ZIP ZIPEXT DATEFILED/A100 AS DATEFILED RENEWAL FINANCIALSTMNT FILENO DATESATISFIED COLLATERAL
ON TABLE HOLD AS wf_crucc28 FORMAT SQLMSS
END

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

It sounds like you have two problems, a problem with Date fields and a problem with the HOLD file contents.

The example date 01999131 is not a standard format date, therefore it must be in a numeric or alpha field and not in a date field, is that correct? How do you get January 31st 1999 from this data? How would December 14th 1999 be coded, 19991114, or 11999214? You will have to create DEFINE or COMPUTE statements to transform the "date" field if you want to store this in a SQL Server Date field.

What's stopping you from coding your print statement as PRINT *? The HOLD file should contain the field names and not the aliases.

Hi Francis,

I agree with the point that I have two problems... ;-) This is the first time I've seen WebFocus, so I'm trying to learn on the fly...

Ok, starting with the dates. The DATEFILED field has a format of YYMD in the master file. I assumed that this was a date field and is formatted correctly in the table 95% of the time. There are a few situations where the date starts with a 0, hence me thinking that it was a valid date, but the month or day only used one character for some reason. Again, this was a big assumption on my part.

In regards to print *, this is what I tried from the get go. If I generate a hold file and format it is a csv, I get the full column names, whereas if I export the data directly to SQL Server using the SQLMSS adapter, I end up with the alias'es being the column names. If I specify every single column using AS xxx then I get the correct column name.

Any suggestions or comments that you may have are greatly appreciated.

Hope you have a good night.
Michael

PS - Here is the master file for this table.

FILE=CRUCC, SUFFIX=FOC
SEGNAME=ROOT_SEG, SEGTYPE=SH2, $
FIELD=CREDITNO, ALIAS=CRDTNO, FORMAT=I10S, INDEX=I, $
FIELD=DATETIMEIN, ALIAS=DTIMEIN, FORMAT=A18, $
FIELD=DATAENTRYPERSON, ALIAS=DPERSON, FORMAT=A10, $
FIELD=SECUREDPARTY, ALIAS=SPARTY, FORMAT=A120, $
FIELD=ADDRESS, ALIAS=ADDR, FORMAT=A60, $
FIELD=CITY, ALIAS=CTY, FORMAT=A30, $
FIELD=STATE, ALIAS=ST, FORMAT=A2, $
FIELD=ZIP, ALIAS=ZP, FORMAT=A5, $
FIELD=ZIPEXT, ALIAS=ZPEXT, FORMAT=A4, $
FIELD=DATEFILED, ALIAS=DFILED, FORMAT=YYMD, $
FIELD=RENEWAL, ALIAS=RENL, FORMAT=A1, $
FIELD=FINANCIALSTMNT, ALIAS=FINST, FORMAT=A1, $
FIELD=FILENO, ALIAS=FILENO, FORMAT=A20, $
FIELD=DATESATISFIED, ALIAS=DSAT, FORMAT=YYMD, $
FIELD=COLLATERAL, ALIAS=COLL, FORMAT=A120, $

If you simply print the DATEFILED field, do you get format error messages for the ones that have only one character? I think that you would. If so, you may have to fix that problem before continuing.

I've never seen the PRINT * issue, but then I haven't worked with FOCUS DB's in a very long time. You MAY be able to create an alternate master for the FOCUS DB and change the ALIASes to the FIELDNAMEs.

Hi Francis,

The data displays fine if I export the data to a csv file. The bad records look like so:

02000818
02000727
02000703
01990624
02000728
02000824
02000807
02000504
02001113

I'm wondering if should put some conditional logic in to see if the first character is a zero, if so then parse out the date slightly differently...? I need to look into how I can accomplish something like this.

I'll probably end up manually writing the scripts to export the data to get the field names, modifying anything on this system makes me a bit nervous as this is production data.

Thanks again for your help,
Michael

Michael, you could DEFINE a new DATEFIELD,

DATEFIELD2/YYMD = DATEFIELD;

This should transform the date correctly. I still can't believe that the date is displayed with the leading zero.

Cheers,