Text File Import Into Framework

[qberta01]

Hello,

I am using Cognos BI 8.4. I created a datasource and pull text files into the Cognos Framework. Terrible I know, but there is a reason for it. The problem is the text files don't have a header so it pulls in the first value as the field name, making it difficult for me to build queries. For example,

File name = File X

data in file = ABC 123

So should the new file's data change I have a problem. i.e.

File name = File X

data in file = DEF 123

Adding a header to the file is too difficult for the developer creating the file. So I was wondering if there were a way to get around that "dynamic" header problem.

thx in advance,

Q.

 

[blom0344]

A good solution would be to upload the text files to a database and use this as the datasource. Any other solution - including the one you are searching for - is a much more technical and exhaustive path.

Depending on the database you can use, this is moderately to very easy to implement (very easy in case of SQL server)

If you need more details, then let me know..

Ties Blom

 

[qberta01]

The thing is there are several raw files imported into tables in the SQL database. The data then gets manipulated via very long and complex ssis packages. The end result is text files that get sent to customers. Sometimes there are isssues with the data, ie. unexpected carriage returns or odd characters that need to be elimintaed. Some of these characters are not seen in SQL, but creates issues for the customers (not sure how they manipulate the data). Although much care is taken to eliminate these issues they sometimes slip through. Being able to grab the data and parse the files in the framework so the data can then be reviewed in Cognos reports to look for such errors is the idea. I beleive in SQL when importing text or excel files there is an option to include headers. I would like to be able to create a static header for each file. Currently, the framework creates a header based off the first value in the file, which is always changing.

You say this can be done, but is long and exhaustive? If it's that bad I guess the developers will have to update the packages to export a new set of files with headers just for me.

thx,

Q

 

[toddl]

Hey Q,

I'm gonna second Blom's response. Just upload it into SQL and run your reports from there. That's most likely going to be easier then having your developers update their package. I've been in the same issue before, and sometimes it's just easier to upload it into SQL then have the developers change it to your specs. I prefer not to do any cognos reporting off of a text file anyways.

Is there a reason why you can't upload them into SQL?

Todd

 

[qberta01]

Hi Todd,

I actually suggested that the data be put into SQL tables and then I could pull the tables into the framework...much easier, right. However, apparently in SQL the odd carriage returns (the things that look like squares in text files) get "corrected" in SQL and will therefore be missed. I guess I need to see the file exactly as it is in text format.

thx,

Q.

 

[toddl]

Hey Q,

I see your point now...I've used a program called Monarch to parse "text reports" and it can usually skip over the odd carriage returns because you define the line. Then I output it in a text file to import into SQL. I'd see what your developer says, I've also heard that SQL has a better text parsing ability now, but haven't looked at it at all.

Good luck.

 

[blom0344]

Q,

Did you consider a hex editor (like XVI32.exe) for scanning the text files?
In Hex-code CR is 0D (zero dee) and LF is 0A (zero a)
CRLF will show up as 0D 0A in the hex presentation.
Obviously other 'odd' characters have their own hex code, but perhaps this is a viable alternative compared to scanning with Cognos?




Ties Blom