I'm trying to display an expiration date in a CSV file. The data from the database is 1205. I would like to format this to 12/05, however it's displaying strange. When selecting it straight from the database

 CARD_EXP AS 'Expiration Date' 

it displays 1205.

With this

 CARDDT/A5 = EDIT (CARD_EXP, '99/99'); 

it displays 5-Dec.

When just displaying the month

 EXPMON/A2=SUBSTR(10, CARD_EXP, 1, 2, 2, EXPMON ); 

it displays 12.

When just displaying the year

 EXPYR/A2=SUBSTR(10, CARD_EXP, 3, 4, 2, EXPYR ); 

it displays 5.

When trying to combine the month and year

EXPDT/A6 = IF CARD_EXP EQ MISSING THEN '.'
      ELSE EXPMON || '/' || EXPYR ;  

it displays 5-Dec again.

Can someone explain what I'm doing wrong. I have it working fine for HTML and EXCEL with

 -** PUT '/' in Expiration date:
EXPMON/A2=SUBSTR(10, CARD_EXP, 1, 2, 2, EXPMON );
EXPYR/A2=SUBSTR(10, CARD_EXP, 3, 4, 2, EXPYR );
EXPDT/A6 = IF CARD_EXP EQ MISSING THEN '.'
      ELSE EXPMON | '/' | EXPYR ; 

and it displays 12/05.

This is an older version of a product using 5.2.3.

Thanks.

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

How are you "displaying" the contents of your CSV file? using Excel?

If so, keep in mind that Excel may be trying to be smart and recognize 12/05 displaying as 5-Dec. Changing the cells' display format within Excel might help.

Please open first your CSV file using notepad or a more decent text editor to verify what content you actually have there.

If you see 12/05 then WebFOCUS did its job and now you have to deal with Excel but that's a different story.

Thank you for the feedback. Yes the contents are "displayed" using Excel. I tried your suggestion and opened it with a text editor and the contents are as expected, ie displaying 12/05. What would be your suggestion for "dealing" with the issue in Excel.

Not using Excel?

Sorry, I don't know ... it all depends on why you need a CSV file in the first place. If it is to be consumed by another application then this Excel thing should not impact you.

But if it's needed in Excel for whatever reason, then why not produce the file in EXL2K format?

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

The way I found is a bit "manual".

1. Run the report but choose to "save" the file instead on opening it directly
2. Start Excel with a new blank worksheet
3. Data > Get External Data > From Text and choose the .csv file
4. Specify Delimited, Comma and make sure to choose Text under "Column data format" for each field you don't want to be converted

That works with Excel 2007. I don't know if your business users would like it but that's the best I could come up with. Sorry.

Thanks for the explaination I've decided to give the user the date as 12.05 in CSV. Not ideal but it gets the point across.

quote:

What would be your suggestion for "dealing" with the issue in Excel

I agree with njsden.

Excel does what excel does. It reformats when it likes.