Import data retrieved by Cognos 8 into Access database

[annie52]

I have an Access 2007 database application that contains an import specification to fill a table with data from an Excel spreadsheet (xls) that was generated by Cognos. Things have been well for two years.

We just upgraded Cognos to version 8 and although the filenames of the reports show an xls extension, I'm told that they are actually csv files. Instead of overwriting data in the table, the version 8 files cause the table to be deleted and the saved import spec no longer functions. The import must now be created manually each time we save a new copy of a Cognos report.

I tried creating a new import spec for a csv file but, because of cranky quotation marks in the data, this did not work well.

Any thoughts?

 

[blom0344]

Using a BI-tool to simulate an ETL process is a pretty cumbersome way to migrate data. I would translate the Cognos report to one or more database views, then link these to MS Access and use some sql to write the view data to MS Access table.
As a starter you take the native SQL that Cognos suggests when running the report and use that as a starting point.

If you really dare then you could also consider writing a pass-through query from MS Access directly to the database

(Actually used both techniques years ago from Access2000 against a DB2 database)

Ties Blom

 

[annie52]

Thanks for your response but, unfortunately, you're talking way over my head. I know nothing about Cognos. The report was developed by someone else and I simply run it.

Also, I don't have direct access to the main database. We run the Cognos report once or twice daily to retrieve fresh data, import that into Access, and run our own custom reports.

I was hoping that my problem was a known compatibility issue between Cognos 8 and Access 2007. Guess I'll just hope our computer folks here can figure this out sooner or later.

 

[blom0344]

There is no compatibility issue cause Cognos and MS Access have nothing (and certainly no connectivity) in common. (never had actually) There is a pretty big chance that Cognos8 generates a newer excel type. The latest Excel versions store data diffently (xml format) than previous versions. your best bet is to try to generate the Excel output of the earliest version possible to get your problem solved..

Ties Blom

 

[annie52]

Thanks for the feedback. It turns out that Cognos 8 lets me use the Run With Options button to save the file in .xlsx format. This imports quite nicely and I'm back on track again. Thanks for walking through this with me.