Using win32ComClient along with ImportDelimFile in Python to import csv
Forums
My problem is related to this one:
http://www.ideascripting.com/forum/python-import-txt-file
However, slightly different, as I can say for sure that my rdf import definition file is correct.
The task is actually quite simple: I have a csv file, a rdf file and I want to import the csv file into idea. When I do it manually, it works. Now, I need to do it with python code. My code so far is:
import win32com.client as win32ComClient
idea = win32ComClient.Dispatch(dispatch="Idea.IdeaClient")
dbName = "test.IMD"
idea.ImportDelimFile("source_file.csv", dbName, False, "", "corresponding_rdf_file.RDF", True)
idea.OpenDatabase(dbName)
task = None
db = None
idea = None
I ran this code directly from within IDEA. I get an error popup without a message. When I run it in python, I get the following error message:
com_error: (-2147352567, 'Ausnahmefehler aufgetreten.', (0, 'Idea.IdeaClient.1', 'Ungültiger Makro-Dateiname.', None, 0, -2147212329), None)
Which translates to something like "exception occured" and "invalid makro-filename".
Somehow the code line with idea.ImportDelimFile throws an error.
I also tried to use absolute pathes:
import win32com.client as win32ComClient
idea = win32ComClient.Dispatch(dispatch="Idea.IdeaClient")
dbName = "test.IMD"
idea.ImportDelimFile("C:\myfolder\source_file.csv", dbName, False, "", "C:\myfolder\corresponding_rdf_file.RDF", True)
idea.OpenDatabase(dbName)
task = None
db = None
idea = None
but it still doesn't work. I don't understand why, when I use the corresponding idea script code and run this in IDEA:
dbName = "test.IMD"
Client.ImportDelimFile "C:\myfolder\source_file.csv", dbName, FALSE, "", "C:\myfolder\corresponding_rdf_file.RDF", TRUE
Client.OpenDatabase (dbName)
it works. (Removing the brackets () in the idea.ImportDelimFile statement in the Python code didn't solve the issue)
So where is the mistake?
Hi Brian,
Hi Brian,
thanks a lot for your very fast and precise answer!
1.) My idea files are all with IMD, so I guess I have a ascii and it is correct to use IMD and not IDM.
2.) It now works, one of my mistakes was that I just entered the file name without the path, however I copied the csv and the rdf file to the location where the python script is located, not into the IDEA folder. If I copy them to the IDEA folder and then just use "file.csv" and "myrdf.rdf" it does work. However, I could not find a way to specify an absolute path. When I use a relative path like you, for example sourcefiles\ and importdefinitions\ it does work. But it does not work, if I really want to specify the complete path. And that is another problem/mistake, but I don't know where my mistake here is. So could you give me an example with an absolute path? When I try as I wrote "C:\myfolder\myfile.csv" and same for the rdf it does not work. I tried it with / instead and with double // but then I get the error message with the invalid macro name. So the reason is the wrongly specified path.
3) When I run the script (also without the idea.OpenDatabase command) it finishes, however an IDEA process is running in the background (I did specify idea = None). See also point 5) below the code example at the end I gave. Then I cannot start IDEA again, as it says IDEA is already started. I have to kill the process in the task manager. Do you know a solution for this? A command to kill/end/close the idea connection / close the IDEA application? I would also like to use this command if possible before I start the code, to close any other IDEA processes, in case there might be an IDEA session already running.
4.) In case the file which I want to create, the IMD file already exists, the script runs into problems. Sometimes I am asked if I want to replace (when I run it from IDEA), however sometimes especially when run from Python it does not work. So it is stuck. That means the python code is running forever and does not end. Because I think in the background IDEA waits for the confirmation to overwrite. However, I cannot see the popup message. The problem is likely the replacement of the already existing file. Do you know a command to tell IDEA to overwrite and not ask? This is a minor problem, as I am able to check with python code if the file exists and delete it before. So just in case you know a (IDEA) solution (in this command to just tell IDEA to overwrite and not care). If not I will do it with python code.
5.) Do you know a way in python to detect the current IDEA working directory? So in my python script if I run it standalone, can I detect the current project folder? The one which is opened when I start IDEA. Because for the python solution in point 5 I need to check if the Test.IMD file already exists in the IDEA working directory and if yes delete it. Of course I know my current personal IDEA project folder, but in case someone else runs it the path would change. So I would like to code it dynamically and not give a hard absolute path. My problem here is also connected to point 3, because if I try to delete a file with python, I get the error message that another process uses the file. This is an IDEA background process from my code run before. So there I can see (and also in the task manager) that an IDEA process is running. This is still using/opening the file. And I need to kill it. I can do it with the task manager and then it works, however I would like to do it with python code. The idea = None is not always killing the process. If I just run
import win32com.client as win32ComClient
idea = win32ComClient.Dispatch(dispatch="Idea.IdeaClient")
idea = None
Sometimes it works, but sometimes I can see in the task manager that IDEA is first started and uses some resources then the python script ends and the resource usage is going down to zero. But the process is still there and running.
6.) Suppose I would like to import two files, one into test.imd and the other into test2.imd. Is the following code correct (in both cases using the same rdf)?
import win32com.client as win32ComClient
idea = win32ComClient.Dispatch(dispatch="Idea.IdeaClient")
dbName = "Test.IMD"
idea.ImportDelimFile("fileone.csv", dbName, False, "", "myrdf.rdf", True)
idea.OpenDatabase(dbName)
dbName2 = "Test2.IMD"
idea.ImportDelimFile("filetwo.csv", dbName2, False, "", "myrdf.rdf", True)
idea.OpenDatabase(dbName2)
idea = None
So can I just attach another idea.ImportDelimFile after the first? Or should I consided something else, like closing the connection before and opening a new one? I think I can do it like this?
You already helped me a lot, big thanks again!
Hi Bert, lots of points.
Hi Bert, lots of points. Lets take them one at a time. I was able to get the script to work with an absolute path by putting the r before the path name. The \ is an character escape in python, the r in front of a character strings tells python to ignore that it is an escapte character and just use it as a normal charater.
import win32com.client as win32ComClient
idea = win32ComClient.Dispatch(dispatch="Idea.IdeaClient")
dbName = "General Ledger-GL1.IMD"
idea.ImportDelimFile(r"C:\Users\user\Documents\My IDEA Documents\IDEA Projects\WP IDEA Integration - Summary Sheet\Source Files.ILB\General Ledger-GL.CSV", dbName,
False, "", r"C:\Users\user\Documents\My IDEA Documents\IDEA Projects\WP IDEA Integration - Summary Sheet\Import Definitions.ILB\General Ledger-GL.RDF", True)
idea.OpenDatabase(dbName)
idea = None
3 - This was probably because
3 - This was probably because your script wasn't finishing properly. What you should do is wrap your code around a try and finally so that the idea = None always gets run. Here is an example of your script rewritten using a try: statement.
import win32com.client as win32ComClient
if __name__ == "__main__":
try:
idea = win32ComClient.Dispatch(dispatch="Idea.IdeaClient")
dbName = "General Ledger-GL1.IMD"
idea.ImportDelimFile(r"C:\Users\user\Documents\My IDEA Documents\IDEA Projects\WP IDEA Integration - Summary Sheet\Source Files.ILB\General Ledger-GL.CSV", dbName,
False, "", r"C:\Users\user\Documents\My IDEA Documents\IDEA Projects\WP IDEA Integration - Summary Sheet\Import Definitions.ILB\General Ledger-GL.RDF", True)
idea.OpenDatabase(dbName)
finally:
idea = None
4 - You are probably correct.
4 - You are probably correct. In visual basic there is an ignore warning function that you can use but there isn't an equavalient in python. In this instance you need to write some code to check if the file already exists and if it does delete it before running the test.
Also you can use the Client.UniqueFileName("Sample") function that will return a unique filename. This will just mean that you could have multiple versions of the same import file.
5 - The Client.WorkingDirectory() function will return the current project folder that is open. So you can use this to create your paths.
import win32com.client as win32ComClient
if __name__ == "__main__":
try:
idea = win32ComClient.Dispatch(dispatch="Idea.IdeaClient")
dbName = "General Ledger-GL1.IMD"
idea.ImportDelimFile(idea.WorkingDirectory + r"Source Files.ILB\General Ledger-GL.CSV", dbName,
False, "", idea.WorkingDirectory + r"Import Definitions.ILB\General Ledger-GL.RDF", True)
idea.OpenDatabase(dbName)
finally:
idea = None
Hi Bert,
Hi Bert,
I just tried it with this code and it works but I think I might now what your problem is.
What version of IDEA are you using, are you using the ASCII or Unicode version. In the ASCII the IDEA file extension is IMD but in Unicode I think it is IDM. So if you have the unicode installed it won't find the IMD file as it is looking for an IDM file, try switching the extension and see if that fixes the problem. This would fit with getting the invalid macro name.