ODBC Import with filename in a directory

12 posts / 0 new
Log in or register to post comments
Last post
Sat, 02/17/2018 - 11:25
#1
Dino
Offline
Joined: 02/08/2018 - 02:45
ODBC Import with filename in a directory

Hi Brain, 
How are you? Is it possible to help me a second time :)
Thanks again for your answer on my previews question. I have a new one that is driving me bonkers!
I have got Visual FoxPro files that need to be imported so that i can join a couple of tables. Visual FoxPro Databases are controlled by a DBC file that changes every time. Importing is only possible with the ODBC connection and that is working perfectly.  
I have scripted the steps to import the tables and i am now trying to find the filename in the folder and include the filename and directory in the ODBC syntax. I am now stuck at the last step for the last couple of days and it is driving me crazy.
I reused some script on this website. 
I included the script with the lookup in the ODBC syntax for the directory and the filename. 
I icluded also the same script with fixed name in the ODBC syntax for the directory and the filename. 
 
The script can locate the filename that is being searched in the private function but i cant include it in the ODBC syntax like the sAdminpad
There is only 1 .DBC file in every folder.   
Can you see where it is going wrong?
 
Sorry for my poor scripting skills (i'm new to this)
 
Thanks in advance. 
 
Dino.  

Files: 
Plain text icon Code with fixed names ODBC
Plain text icon Code with lookup names in ODBC
Top
Sat, 02/17/2018 - 22:16
Dino
Offline
Joined: 02/08/2018 - 02:45

Hi again, 
Problem solved, after a day of debugging, i rewritten the part of the code that was not working and it is working fine now. 
Thanks. 
Dino. 
 

Top
Sun, 02/18/2018 - 06:48
(Reply to #2)
Brian Element's picture
Brian Element
Online
Joined: 07/11/2012 - 19:57

That is good to hear, sorry I didn't have a chance to respond yesterday.  So what was your solution?

Thanks

Brian

Top
Sun, 02/18/2018 - 08:33
Dino
Offline
Joined: 02/08/2018 - 02:45

Hi Brain, 
No problem, i can understand if you have a personal life :) 
My solution for this moment was to include the part of the code in the Sub Main. That way i could get the variable i needed (it gets lost after the loop in a function).  I know it is not the most ellegant way of coding. 
Can you help me with that my remaning questions? 
Is there a simple command to delete all the databases in a project like the close all command "Client.CloseAll"? 
 
How can i call this function from Sub Main? "Call bestandnaam_maken" is getting me a syntax error.
 
How can i call the variable "Txtpart" outside of the Function and for example in a different function or in Sub Main?
 
Function bestandsnaam_maken(currentValue As integer) As StringDo Until Txtteken = "\"  Dim Txt As StringDim Txtpart As StringTxt = "tekst\text en iets ander.xlsx"CurrentValue = CurrentValue +1Txtpart =Right(Txt, CurrentValue)Txtteken=Left(Txtpart,1)Loop'MsgBox(Txtpart)End Function
Thanks in advance and have a good day. 
Dino. 

Top
Mon, 02/19/2018 - 07:22
Brian Element's picture
Brian Element
Online
Joined: 07/11/2012 - 19:57

Hi Dino, here is some code to delete all the IMD files in the project folder, you should use the Client.CloseAll before running this, if a file is open it will not get deleted but I haven't tested it out:

 


Option Explicit

Sub Main
	Call deleteAllIMDFiles()
End Sub

Function deleteAllIMDFiles()
	Dim objFSO As Object
	Dim objFolder As Folder
	Dim objFile As File
	Dim task As task
	
	Set objFSO = CreateObject("Scripting.FileSystemObject")
	
	'make sure the folder exists
	If objFSO.FolderExists(Client.WorkingDirectory) Then
		'get the folder information, namely all the files from the folder
		Set objFolder = objFSO.GetFolder(Client.WorkingDirectory)
		
		On Error Resume Next
		
		'the ProjectManagement task contains the code to delete files in the project folder
		Set task = Client.ProjectManagement
		
		'loop through all the files in the folder
		For Each objFile In objFolder.Files
			'make sure the files is an IDEA file
			If Right(UCase(objFile), 3) = "IMD" Then
				'delete the file
				task.DeleteDatabase objFile
			End If
		Next
	End If
	client.RefreshFileExplorer
	
	Set task = Nothing
	Set objFile = Nothing
	Set objFolder = Nothing
	Set objFSO = Nothing
End Function
Top
Mon, 02/19/2018 - 07:26
Brian Element's picture
Brian Element
Online
Joined: 07/11/2012 - 19:57

Hi Dino,

For the second part of your question I am not sure what you are trying to do.  Maybe give me an example of what you are looking for, such as what the variable holds at the beginning and what you want returned form the function.

Thanks

Brian

Top
Mon, 02/19/2018 - 15:01
Dino
Offline
Joined: 02/08/2018 - 02:45

Hi Brain,
I will try the code Thanks!
 
I think i found a solution for the second question by deleting the "currentValue As integer between the ()". It was not needed.  Problem solved i think!
 
For the last part, see the code i included in the file.
 
If i call the msgbox within the function Text() the output in the msgbox is as expected "\example". When i call the variable "txtdeel" in sub main as part of the msgbox the output is empty where i expect "\example".
 
Hope this makes it a bit clearer.
Dino. 

Files: 
Plain text icon Example code.txt
Top
Mon, 02/19/2018 - 15:07
(Reply to #7)
Brian Element's picture
Brian Element
Online
Joined: 07/11/2012 - 19:57

HI Dino, I think this is what you are looking for:

 


Sub Main
	Dim Txtpart As String
	Dim txt As String
	Txt = "C:\test\example"
	Txtpart = Text(Txt)
	MsgBox Txtpart
End Sub

Function Text(Txt As String) As String
	Dim txtdeel As String
	txtdeel = iSplit(Txt, "", "\", 1, 1)
	Text = txtdeel
End Function
Top
Mon, 02/19/2018 - 15:26
Dino
Offline
Joined: 02/08/2018 - 02:45

Hi Brian, 
The Txtpart = Text(Txt) is the part that i was looking for. And also the Isplit is more efficient instead of the Do until Loop, i still have a lot to learn ;)
Dino.

Top
Mon, 02/19/2018 - 19:39
(Reply to #9)
Brian Element's picture
Brian Element
Online
Joined: 07/11/2012 - 19:57

Hi Dino, don't worry, I am still learning.  I just clued into using the iSpit a few months ago and I have been doing this (and teaching it) for years.  So you can always learn new stuff.

Brian

Top
Tue, 02/20/2018 - 12:42
Dino
Offline
Joined: 02/08/2018 - 02:45

Hi Brian, 
 
The code to delete all databases in a project works like a charm. Thanks!
Dino.

Top

Pages