IDEA Script extract data off IDEA Server or other servers

aveenm293

Forums

Hi All
Is it possibel to write a script that will pull data automatically off a server. Also how does ODBC work, is this a connection to a server to retrieve data. And lastly to clarify, to use IDEA Server, I would need to connect from my desktop to the IDEA Server and work off there?
Thank you
Regards
Aveen
 
 

aveenm293 Tue, 12/03/2013 - 12:19

Hi Brian
I have tried the code above using IDEA version 8.5. I keep getting the attached errors. I have a few changes I have 2000 text files to import but have used the report reader to import and have created a jpm template file.
Please can you assist?
Thank you
Regards
Aveen
 

Brian Element Tue, 12/03/2013 - 14:36

Sorry Aveen, html sometimes likes to change & to & to protect the code.  I think I found the errors and corrected them in the script.

aveenm293 Tue, 12/03/2013 - 15:03

Hi Brian
Thank you very much. Will it work with a JPM as opposed to an RDF?
Regards
Aveen

Brian Element Tue, 12/03/2013 - 16:46

Aveen you will have to change the following line from:

Client.ImportDelimFile filename, dbName, FALSE, "", "D:\Data\test\Detailed Time Code-1.RDF", TRUE

to

Client.ImportPrintReport "D:\Data\test\Detailed Time Code-1.jpm", filename, dbname, FALSE

aveenm293 Wed, 12/04/2013 - 00:50

Hi Brian
I updated as follows:
Option ExplicitOption Base 1 'arrays start at 1Const FOLDER = "C:\Users\amahado\Desktop\Microsoft Data\Test Run"Const EXTENSION = "*.del"Const JPM =  "C:\Users\amahado\Desktop\Microsoft Data\Test Run\New Template.jpm"Dim workingFolder As StringDim sFiles() As String 'to hold the files
Sub Main workingFolder =Client.WorkingDirectory() Call importFiles() client.refreshFileExplorerEnd Sub
Function importFiles() Dim Files As String Dim filename As String Dim dbName As String Files = Dir(FOLDER & EXTENSION) Dim bFirstPassInd As Boolean  bFirstPassInd = true ReDim sFiles(1) ' While a call to Dir() continues to return file names. Do  ' Get the next file name and place it in FileList.  If Len(Files) > 1 Then   filename = Mid(Files, 1, InStr(1, Files, ".") - 1)      dbName = filename & ".IMD"   If bFirstPassInd Then    bFirstPassInd = False   Else    ReDim preserve sFiles(UBound(sFiles) + 1)   End If   sFiles(UBound(sFiles)) = FOLDER & dbName   filename = FOLDER & Files   Client.ImportPrintReport "C:\Users\amahado\Desktop\Microsoft Data\Test Run\New Template.jpm", filename, dbname, FALSE   'Client.ImportDelimFile filename, dbName, FALSE, "", "D:\Data\test\Detailed Time Code-1.RDF", TRUE   Client.OpenDatabase (dbName)  End If  Files = Dir Loop While Files <> ""
End Function
The script runs but no data appears in IDEA. Have I missed something?
Thank you
Regards
Aveen

Brian Element Wed, 12/04/2013 - 17:55

Aveen, this works, you were missing a \ in the FOLDER variable. Also makes sure the EXTENSION variable is the extension of the files you want to import, they should all be the same. Also I changed the import instruction to use the JPM variable for the template, make sure the name is correct and it is store in that location.

Option Explicit
Option Base 1 'arrays start at 1
Const FOLDER = "C:\Users\amahado\Desktop\Microsoft Data\Test Run\"
Const EXTENSION = "*.del"
Const JPM =  "C:\Users\amahado\Desktop\Microsoft Data\Test Run\New Template.jpm"
Dim workingFolder As String
Dim sFiles() As String 'to hold the files
Sub Main 
	workingFolder =Client.WorkingDirectory() 
	Call importFiles()
	client.refreshFileExplorer
End Sub
Function importFiles() 
	Dim Files As String 
	Dim filename As String 
	Dim dbName As String 
	Files = Dir(FOLDER & EXTENSION) 
	Dim bFirstPassInd As Boolean  
	bFirstPassInd = true 
	ReDim sFiles(1)
	 ' While a call to Dir() continues to return file names. 
	 Do  
	 	' Get the next file name and place it in FileList.  
	 	If Len(Files) >1 Then  
	 		
	 		filename = Mid(Files, 1, InStr(1, Files, ".") - 1)      
	 		dbName = filename & ".IMD"   
	 		If bFirstPassInd Then    
	 			bFirstPassInd = False   
	 		Else   
	 		 	ReDim preserve sFiles(UBound(sFiles) + 1)   
	 		End If   
	 		sFiles(UBound(sFiles)) = FOLDER & dbName   
	 		 filename = FOLDER & Files   
	 		Client.ImportPrintReport JPM, filename, dbname, FALSE   ' 
	 		Client.OpenDatabase (dbName)  
	 	End If  
	 	Files = Dir 
	 Loop While Files <> ""
End Function