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
 
 

Brian Element Fri, 10/04/2013 - 09:20

Hello Aveen. 

For server do you mean an IDEAServer or just a server you have access to?  I am not sure for the IDEAServer as unfortunately I have never worked in that environment and I don't have access to it to test it out. 

If you are talking about getting documents like you would on your c: drive but store on your server a script could probably do this, it is just a matter of pointing at the correct file.

ODBC stands for Open Database Connectivity and they are interfaces that allow you to connect directly with different types of databases.  Usually on your computer you would have ODBC drivers for Excel and Access but you can download ODBC drivers for most databases.  The ODBC has to be properly configured to access the database such as giving the drivers the location, passwords, etc on how to access the database.  Once it is properly installed it will allow you, through the IDEA import control, to connect directly with the database and select the tables / fields you wish to import into IDEA and the data will be imported directly as an IDEA file.  They are great to have.  If you have excel or access on your machine you might want to try them out.  Also there is lots of information on using ODBC dirvers on the net.

Yes, for IDEAServer you would use your IDEA application to connect.  In version 9 there is a tab to see the Server files versus the desktop files, I can't remember how it is separated in V8.  Once IDEA is connected to the server working with the server files is seemless and you are not using your computer resources to do data analysis.

Let me know if this answers your questions.

aveenm293 Fri, 10/11/2013 - 05:04

Hi Brian
Your response definitely answers my questions. I am talking about getting documents off the c: drive / server  and having them import directly into IDEA without clicking import each time.
Regards
Aveen
 
 

Brian Element Fri, 10/11/2013 - 05:40

Sure that is possible.   You can have many options when doing this such as do you hardcode the location so the script always looks at the same place or do you tell the script where to look.  Also are you importing one or many files and are they of the same type, do they need definitions, etc.  So the answer is yes but I would need more info. 

aveenm293 Mon, 10/14/2013 - 05:26

Hi Brian
I will be importing multiple files. I will create a once off deifinition set and each month pull in these files. The file type will always be the same. My intention is to create a central spot on a server, where data can be dumped and each month I will want to run a script to pull the data off the server and then run some tests.
Regards
Aveen
 

Brian Element Mon, 10/14/2013 - 05:35

Well that sounds easy enough.  You would create the definition once and then put it in a loop to read all the files in the directory.  If your analysis is the same you can also put that as part of the script.   Do you need any help setting up the script?  You can see an example of a similiar script here.

aveenm293 Tue, 10/15/2013 - 06:00

Hi Brian
I need some assistance with writing/ setting up the script to point to the location - maybe a folder on a server or an IP Address. Also to loop the RDF. I had an IDEA (excuse the pun) of have a little menu / interface where you could click a button and then IDEA would automatically pull the files and run some calculations and output the results. I think the setup is more important though.
Regards
Aveen
 

Brian Element Mon, 10/21/2013 - 18:57

Aveen here is some code.  You have 3 constants at the beginning to hold the location of the files, the extension of the files and the name and location of the RDF file.  The files that are imported are stored in the sFiles() array so you can use this to do any other analysis on them.  Let me know if this makes sense or if you have any questions.

Option Explicit
Option Base 1 'arrays start at 1
Const FOLDER = "D:\Data\test\"
Const EXTENSION = "*.del"
Const RDF =  "D:\Data\test\Detailed Time Code-1.RDF"
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.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

 

aveenm293 Tue, 10/22/2013 - 05:52

Hi Brian
Thank you very much. I am going to try this out. After which line can I put in my calculations?
Regards
Aveen
 

Brian Element Tue, 10/22/2013 - 07:14

Aveen, you would create a new function and do a loop such as:

For i = 1 to ubound(sFiles)

     ... Your code

Next i

Each sFiles(i) would be the name of a file you brought in.

Also I just noticed in the above code it changed some of it, it changed the < to &lt; and > to &gt; and & to &amp;