Cognos Impromptu 7.1 - add header info and footer info to .csv export

[shannonbill40]

Thank you for your help so far in informing me of how to create a macro to do an export.

Now is another question:

The company I am doing this export for requires 1 row of "header" information at the top of the .csv export file. It will only contain 3 things. (Record Type which will always be 1) (File Type which will always be 4) and (File Creation Date and Time in YYYYMMDDHHMISS format)

After this 1 row of header info, the main report data will be displayed. I have gotten that all setup and working properly.

Finally, on the end of the .csv file, they require a "footer" or "trailer" rox that contains: (Record Type which will always be 3) (Number of Records which will be the sum of the number of records pulled in the main data of the report)

I see that I can add headers and footers to the reports, but this just seems to be graphical stuff that doesn't export in the .csv pull.

Thanks again for the help.

 

[drlex]

Shannon,
As you've found, the .csv export is just a 'dump' of the executed query and omits any layout information.

Two ways of tackling the problem that occur to me are:
1.A two-stage approach - export the report as excel to garner the headers and footers and then convert the output to the required csv format.
One might do this by using the Excel output as a source for a second impromptu report to be saved as .csv with the relevant pipe separators in place of commas.

2.Stick with the original .csv export, but add the header and footer information to each end of the file. You'd need 2 more versions of the report, one to provide the header information and one for the footer.

lex

soi la, soi carré

 

[shannonbill40]

Lex, I follow your logic, but am not understanding how all of the automation will stay in tact.
Opt 1) In your first suggestion, I could export from the Impromptu macro to Excel.- this is automated. But once I have the Excel file, how would I automate the process of adding the formulated header and footer and then saving it as the properly formatted csv file? You state to use the excel file as the source for a second impromptu report. I have not done this before. Would you mind providing a more laymen explanation? Thank you!!!

 

[drlex]

shannon
For Opt 1), I envisage setting the excel output of the report as an OBDC data source for a simple 'Select *' style second report from which you can set a .csv export.
It's like doing a hotfile export and running a second report against the hotfile.
So, to set it up, you'd need to save an excel output, set up an ODBC data source against this file and create a one-table catalog. Your second report is then a simple extract of all in the single table.

If this sounds like too much of a schlep, then it would be simpler to go with Opt 2). I can't recall the exact DOS command to conjoin files, but it's something like "copy /b file1.csv+file2.csv+file3.csv outputfile.csv" that you can put in a 'Shell' command in the macro.

lex

soi la, soi carré