Impromptu date format when saving in csv format

[Allan59]

Impromptu experts

I am running a cognos script (see below) to extract and save data in ascii csv format. Unfortunately (for me) this format will only save date fields in the ISO format - YYYY-MM-DD.

I need to save the dates in the format DD/MM/YY

Is there a script command that will do this ?

Thanks

Script -:

Sub Main ()

Dim objImpApp As Object
Dim objImpRep As Object
Dim textfile As String
Dim targetfile As String
Dim X as Integer
Dim aline as String

Set objImpApp = CreateObject("CognosImpromptu.Application")

objImpApp.OpenCatalog "D:\Impromptu\VSD\Catalogs\SL5C_Sales Orders.cat", "Creator"

Set objImpRep = objImpApp.OpenReport("D:\Impromptu\VSD\Reports\Data 2 GBD\SO_Orders_4_GBD_143.imr")

objImpRep.ExportASCII "V:\SO_Orders_4_GBD_143.csv", False

objImpRep.CloseReport
objImpApp.Quit
Set objImpRep = Nothing
Set objImpApp = Nothing

End Sub

 

[griffindm]

Allan,

You may want to examine the low-level file input/output options that exist within CognosScript. You can pull data into the macro either using GetDataValue or using the SQL options. Then you can reformat the dates within the macro to any format you desire.


Regards,

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[Allan59]

Thanks griffindm

Can you give me an example of the script I need or point me where I can find it

 

[flex13]

I think the date format may also depend on what your PC's regional settings are set to. Have a look at that.

 

[griffindm]

Allan,

The Macro Editor contains excellent sample macro code for doing file I/O. Look at the help for the Open, Write, Print, and Close commands. flex is correct about the regional settings for some of the data format options. You can override this with some of the options within the macro language. It's a little awkward, but here is some sample code:

Private function MakFileName (x)
if IsDate(x) then

   'removes year, month and day component
   year_a = year(x)
   year_b = Mid(Cstr(year_a),3,2)
   month_a = month(x)
   day_a = day(x)

   'makes sure that the month and day are 2 characters long (won't pass otherwise)
   if len(month_a) <> 2 then month_a = "0" & month_a
   if len(day_a) <> 2 then day_a = "0" & day_a

   'bring together in a format that Impromptu will use
   x = year_b & month_a & day_a
   'x =  month_a & "/" & day_a & "/" & year_a 
else
   x = ""
end if
MakFileName =  x
end function

Regards,

Dave Griffin



The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com