Impromptu: Undelimited Exporting (without delimiting)

[gasman009]

Does anyone know of a way to export from Impromptu to a text file (such as a fixed length or width text file) in an undelimited format?

This is what CognosKB had to say:
Title: Macro to export data as text with out header and delimiter
Document ID: 74252
Solution description

1)Here is the actual macro syntax:
ReportDocument.ExportASCII FileName [,IncludeHeader] [,UseQuotes] [
,Delimiter] [,EOL]

2)Here is a sample macro code:
...
ImpRep.ExportASCII "E:\test\macro" & ".csv",0,0,,0
...

This is a well written technote, however the problem is that this method uses comma as a default (ASCII=44) for the [,Delimiter] parameter, when there is none specified. If you use (ASCII=0) = one whitespace. I can't win!

This is a sample of my current output (from a 1,600 character line):
125452 20020610 000045833 ...

Which I want to come out as this:
12545220020610000045833...

 

[griffindm]

gasman,

The best way I've found to do this with complete control over the file is through low level file input-output using Impromptu combined with a macro. Check on the Macro language examples for commands Open, Print, and Close. Use these with Impromptu using the GetData method, which returns the data from the Impromptu query. Here is some sample code from macros I've designed. this example uses CSV output, but you can do the same with non-delimited ASCII:

Open TaxFile For Output As #2
fcnt = 1
For fcnt = 1 to filecnt
PString1 = OpenMenu.FY+"|"+OpenMenu.PD+"|"+FileArray(fcnt)
'PString1 = OpenMenu.FY+"|"+OpenMenu.PD+"|"+"GL DOWNLOAD DATA"
HDR = FileArray(fcnt)+"TX"
TRN = "TAX PYMT UPLOAD"
Set CPRep=CPApp.OpenReport(LocCopy,PString1)
CPRep.RetrieveAll
On error goto NoData
rowcnt = CPRep.GetDataValue(5,1) 'Get count of report rows
On error goto ErrorRtn
b = 1
Tamt = 0
For b = 1 to rowcnt 'Do for each row in report
org = CPRep.GetDataValue(2,b)
acct= CPRep.GetDataValue(3,b)
amt = CPRep.GetDataValue(4,b)
amt = str(val(amt)*-1)
LN = "L," +STR(b)+"," +STR(fcnt) +",AJE,,,,N,"+HDR+","+amt
LN = LN+","+acct+","+org+","+TRN+",,,,,,,,,"
Print #2,LN
Tamt = Tamt+Val(amt) 'Running adder for AJE cash offset
Next b
amt = str(Tamt*-1)
LN = "L," +STR(b)+"," +STR(fcnt) +",AJE,,,,N,"+HDR+","+amt
LN = LN+",100-200-001,1.01.001,"+TRN+",,,,,,,,,"
PRINT #2,LN
CPRep.CloseReport
Next fcnt

Close #2

Alternatively you can also pull data directly from the macro into an array using SQLExec and SQLRetrieve and ODBC. More on that if anyone is interested.

For fixed length output, it's useful to take full control over each field (especially if you are writing to an input spec for another app). To do this, I call a function in the macro I created called PadStr. The code for this is below. It takes three parameters, which are the string to pad, the number of characters to return, and whether to right or left justify. Extra space is blank filled.

The PadStr code:

Function PadStr(x$,y%,z$)
' parameters x = string to pad, y = number of spaces to fill, z = (L)eft or (R)ight fill

a% = len(x$)
b% = y% - a
Select Case z$
Case "L"
x$ = x$+SPACE(b%)
Case "R"
x$ = SPACE(b%)+x$
End Select

PadStr = x$

end Function 'PadStr

HTH,

Dave Griffin

 

[gasman009]

Thanks Dave. It is as I feared - this would require pretty low level coding. Wish I were a regular at coding, but I am not. It would probably take me quite a while to write this up especially with ~30 fields (each w/ different field sizes). As you'll see, I'm a fan of using GUI, sometimes.

I was thinking and talking to colleagues about ideas last night and just wanted share what I'm about to try today if anyone is interested:

1), there are only a few fields (as I discovered) that are adjacent to each other without any spaces (or delimiters) in between. I'm going to concatenate those fields together in Impromptu to show up like the example, "12545220020610000045833." For the columns with padding I am going to try using a Impromptu functions to do something similar to your PadStr code.
Column1 = White space
Column2 = Len(Field)
ResultColumn = White space - Len(Field) + Field
(On second thought, I think I might try your code. It's more efficient)

2)Similar to using ODBC and SQLExec/Retreive, I'm thinking about using Access to retreive the data and do the conversion.

You put a lot of effort into the answer - I really appreciate it. Thanks again.

 

[griffindm]

Gasman,

Another alternative for the non-coder, make a single output column in Impromtpu by stringing together the columns you need (with text conversion and string padding where necessary) and then save this to Text using the Save As Text option.

A simple Save As Text option on a 'normal' teport may work as well if you are happy with the default padding that Impromptu uses. Be careful because, as I found out when I used to use this occasionally, Cognos has changed the format of the output padding at least once before.

HTH,

Dave Griffin