This content has been marked as final. Show 15 replies
You can't do this. FDM will only accept numeric data values.
You can write an event or custom script to accomplish this though.
This should do what you want
Sub AftExportToDat(strLoc, strCat, strPer, strTCat, strTPer, strFile)
'Hyperion FDM EVENT Script:
'Created By: cbeyer
'Date Created: 2011-02-24-14:48:31
'Purpose: This script will process the DAT file and replace 0 with NoData
'On Error Resume Next
' Declare local objects and variables
' Create file system object used for file operations
Set objFileSys = CreateObject("Scripting.FileSystemObject")
'Parse File name out of full file path
intPos = InStrRev(strFile,"\")
strPath = Left(strFile, intPos)
strFileName = Mid(strFile, intPos+1, Len(strFile))
strTempFile = strPath & "TEMP_" & strFileName
Set objWrite = objFileSys.CreateTextFile (strTempFile, True, True)
Set objRead = objFileSys.OpenTextFile(strFile, 1, False, -1)
'Loop through actual DAT file and wrtite data to new file that already contains clear out data
Do While Not objRead.AtEndOfStream
strReadText = objRead.ReadLine
If strReadText <> "!Data" Then
' Write each line to new file
'Value Row, lets find the amount and see if its zero
strWriteText = ""
For z = 1 To 12
strWriteText = strWriteText & DW.Utilities.fParseString(strReadText, 13, z, ";") & ";"
' For field 13, test amount and write NoData if zero
strField13 = DW.Utilities.fParseString(strReadText, 13, 13, ";")
if val(strField13) = 0 then
strWriteText = strWriteText & "NoData"
strWriteText = strWriteText & strField13
' Write the modified line with value to the new "temp" file
' Close files since file I/O is complete
' Overwrite original DAT file with the TEMP file
objFileSys.CopyFile strTempFile, strFile
' Delete the TEMP file
' Cleanup - destroy file objects
Set objWrite = Nothing
Set objRead = Nothing
Set objFileSys = Nothing
Thank you very much for the reply. I tried to run this script but it gave me an error at line no 36- Set objRead= objFileSys.OpenTextFile(strFile,1,False,-1) saying - "Invalid Procedure call or argument" I changed the line to
Set objRead= objFileSys.OpenTextFile(strFile,1,False) but it had thrown the same error. Would you please have a look at it. Thanks in advance!
Sorry for this but before the error which is mentioned ablove, the script had given me an error at line no 44- If strReadText "!Data" Then. Error was - Expected 'Then'. I changed this line to If strReadText = "!Data" Then and ran this script. After tihs it gave me the error- invalid procedure call or argument which is mentioned above.
I'll take a look at it later today when things slow down a little.
I did that off the top of my head, but I'm pretty sure it should be good.
Only causes that come to mind on this error would be
1.) strFile is null or not a valid file name.
2.) The user that is executing the code does not have proper permissions to see/access the file.
I would ensure this code is running after the export?
A reason why sharing scripts via the forum is a dangerous activity. I would bet that this script was created as a custom script and not an event script.
I find nothing about this discussion remotely dangerous.
If the main goal of the forum is to share knowledge this would include script code (custom or not).
The only danger would be people taking things and implementing them into a production environment without fulling understanding them first.
With that said; however, learning is a process of trial and error. (mostly error :) )
By danger, I meant you are going to spend your valuable time figuring out why the script that you shared which presumably works for you does not work for someone else. If you have the free time to do that, all the power to ya :)
I can't share scripts as they are the property of the companies that have paid for me to write them but you may not have that restriction.
Edited by: TonyScalese on Feb 25, 2011 12:17 PM
I'll put as much time into the debugging as I put into the writing of the script in the first place. :)
As I wrote it for the purpose of that post, I don't have to worry about any restrictions on it; however, I think I want to do something similar for a budget FDM app in the future, so I'll probably re-use part of it there.
Presumably you will be using multiload for the budget? Just be aware that depending on the method you use (XL v txt), you may have different file formats created by FDM. Something to think about when you modify your script.
I've used the multi-load for the last two budget cycles now and we load via XLS. The issue I'm debating on cleaing up is zero amounts are sneaking in throw some of the XLS files. I'm debating on just cleaning it up during the export stage as cleaning up the DAT files seems like the least effort for me. Of course my opinion might change when I get serious about updating this! :-)
Are you loading to HFM or Essbase?
We are using HFM.... The HFM implementation here is a swiss army knife. :) :( :0 :)