This script is now available as a SmartAnalyzer application, it is available through Passport with the Utilities SmartAnalyzer applications. Please consider purchasing this application as it helps to support this web site.
This script will allow you to select and import multiple files at the same time.
March 16, 2014 - Updated - added an option for delimited files to select the first row as the field names.
April 8, 2014 - Updated - there was a bug in the select files dialog that would not allow you to move a file from the right panel to the left.
April 29, 2014 - Updated - added some code so that the script should now work with V8 or V9 without having to edit the code.
May 1, 2014 - Updated - the script could give an error if you select "Select one" then selecting files to import, this fixes the problem.
August 11, 2014 - Added the option to have the original fieldname added to the file.
August 14, 2014 - Small update, there was a possible problem on some computers when adding the filename to the file, thanks to yongest for identifying the error and helping me track it down.
Febuary 20 and 23, 2015 - KrisW added routines to read the new version of the MS access format and found a problem when listing excel files and fixed this.
March 2, 2015 - Additional functionality was added so the script will now import excel files with multiple worksheets. So all worksheets will be imported.
Jan 22, 2017 - Fixed a problem when importing Excel worksheets that have special characters.
This script will allow you to import multiple files of the same type at the same time. The script handles the following formats: dBase, Microsoft Access, Microsoft Excel, Print Report and Adobe PDF, EBCDIC, Text Fixed Length, Text Delimited, XML.
For the Print Report, EBCDIC, Text Fixed Length, Text Delimited and XML you will first have to create a record definition for the files that you import. When importing these types you can only import files that relate to the record definition you have created. So what that means that allow the files have to follow the same parameters of the record definition, so all the text, xml and print reports are in the same format.
When you start the script you will see the following dialog:
From this dialog you first choose the format that you want to import, you can only choose one format at a time.
You then click on the Select Files button to first select the directory that your files are stored under. The files must be located under your project or working folder:
After you select the directory your files are stored in you will now have the option of selecting the files. If the folder you select does not contain any of the files, such as in the case of you wanting to import a dBase file and no *.dbf files were found you will be taken back to the main dialog.
From this screen all the files in the directory that are of the import format can be selected. You can select the >> or << to move all the files at once or click on a file and select the > or < to move them one at a time. Unfortunately IDEAScript does not allow the option for double clicking. Once you have selected the files click on OK and you will be returned to the main dialog.
You will see that the dialog indicates that you have selected one or more files. If your import is for Print Report, Text or XML you will now have to select the record definition by clicking on the Select Definition button.
If your import is for MS Access or Excel you will have an additional dialog asking if you want to scan all the records for Access and if the first line contains the field names for Excel.
This script works for V9 of IDEA. If you are using V8 please find the following in the script and comment out (') the V9 and uncomment the V8 portion.
'********************************************************************
'* The following 3 lines can be used for V8 of IDEA
'Set Opendlg = CreateObject("Ideaex.SaveOpenDialog")
'Opendlg.DisplayDialog False'For V8.5
'sDefinition = Opendlg.SelectedFile
'* End of section for idea verion 8.5
'***********************************************************************
'**********************************************************************
'* Start of dialogs for IDEA V9
iOptionButton = dlgMainMenu.OptionButtonGroup1
Set Opendlg = Client.CommonDialogs
Select Case iOptionButton
Case 4, 5, 6
sDefinition = Opendlg.FileOpen("", "", "RDF Files (*.RDF)|*.RDF")
Case 3
sDefinition = Opendlg.FileOpen("", "", "JPM Files (*.JPM)|*.JPM")
Case 7
sDefinition = Opendlg.FileOpen("", "", "XRDF Files (*.XRDF)|*.XRDF")
End Select
'*End of section for V9
'**********************************************************************
I have tested this script as best as I can but you use it at your own risk. If you find any bugs, problems or have any suggestions on improving the script please let me know.
Comments
Jambob
Fri, 03/28/2014 - 06:55
Permalink
Additional Help
Hi Brian,
I'm new to IDEA but it is now essential in a new job roll and this script will come in very handy.
However i am version 8.02.225 and i'm not sure how to get this to work, in simple terms i'm not sure what you mean about comment out?
thanks
Jambob
Fri, 03/28/2014 - 07:19
Permalink
Update
Hi
Ive now sorted the above , thank you for this amazing script
Brian Element
Fri, 03/28/2014 - 08:22
Permalink
Hi Jambob, and welcome to the
Hi Jambob, and welcome to the site. Glad you got it sorted out. Let me know if you think there are any other functions I could add to this script.
yongest
Fri, 04/04/2014 - 07:47
Permalink
Thank you
Thank you very much Brian.
This is exactly what I was looking for.
Greetings from Spain.
Brian Element
Fri, 04/04/2014 - 08:31
Permalink
Glad you enjoyed the script.
Glad you enjoyed the script. Let me know if you have any suggestions or other options that might be added to it.
ecarlile
Mon, 04/07/2014 - 15:27
Permalink
Errors
Hi Brian
I have about 500 XML files i'm trying to import using this script. The problem is the same import mask can't be used on all of the files.
Is there a way to setup the script to simply skip a file and continue onto the next one if it attempts to appy the mask to a file, and it does not "fit"
Thanks, Edward
Brian Element
Mon, 04/07/2014 - 16:27
Permalink
Edward, I would love to say
Edward, I would love to say it is possible but it isn't or at least not with a fair amount of coding in the background. The only thing I can say is try and separate the files yourself before using the script.
spromila
Tue, 04/29/2014 - 08:36
Permalink
Error
Hi Brian,
I am trying to execute the script and it is giving me error -" Library not registered". I checked the line and it has Client.WorkingDirectory. Could you please help.
Regards,Promila
Brian Element
Tue, 04/29/2014 - 08:48
Permalink
Hello Promila, which version
Hello Promila, which version of IDEA are you using? Unfortunately the editor does not always give the right error spot.
spromila
Tue, 04/29/2014 - 09:01
Permalink
Version 8
Hi,
I am using IDEA version8
Brian Element
Tue, 04/29/2014 - 12:17
Permalink
Ver 1.2
I just updated V1.2 of the script. Can you let me know if it works for you now? If you still have a problem can you let me know when in the script did it happen, i.e. was it when you ran the script, accessed the record definition templates. etc.
Thanks
spromila
Tue, 04/29/2014 - 13:12
Permalink
Same Issue
Hi Brian,
I am facing the same issue, "Error on Line 62 -Library not registered" and line states- working_directory = Client.WorkingDirectory(). As I am very new to IDEA, do we need to register the library manually. I am executing the .iss files by clicking the F5. Do I need to make any changes in idea environment. Is there any way to check the available library as I might have limited IDEA installation.
Thanks
Brian Element
Tue, 04/29/2014 - 13:22
Permalink
No, you shouldn't have any
No, you shouldn't have any problems with that line, and you shouldn't have to register anything, the script should run. Can you try creating a new IDEAScript with the following lines:
Sub Main
MsgBox client.WorkingDirectory()
End Sub
It shouldn't matter but are you using the full verson of IDEA or the demo or educational versions?
spromila
Wed, 04/30/2014 - 01:13
Permalink
Single Main Program
Hi,
I executed the single line program and it is giving the same error. I am using the "Client Vesrion - 8.0.3"
Thanks
Brian Element
Wed, 04/30/2014 - 07:17
Permalink
Unfortunately I only started
Unfortunately I only started scripting with version 8.0.4 and I still have that version on my office computer and it is not giving me an error. So I am not sure if the problem is because you are using an older version or another reason. Can you go into the Scripting Editor and select tools - language browser (hopefully you have that in your version). From there select IDEAScript and Client and look for WorkingDirectory. I am trying to see if your version has a different explanation or if the function doesn't exist.
spromila
Wed, 04/30/2014 - 11:39
Permalink
Checked
Here is explanation-
Set the path to the current working directory.
Brian Element
Wed, 04/30/2014 - 11:43
Permalink
Well I am not sure how to
Well I am not sure how to help. My only suggestion now is see if you can upgrade your version of IDEA or talk to your distributor about this error as this looks like some type of installation problem with IDEA. Maybe you can try reinstalling also as this is one of the core functions of IDEAScript and something might not have installed properly.
spromila
Wed, 04/30/2014 - 12:29
Permalink
Thanks
Sure, Thanks for all your help.
shlomit
Sun, 05/04/2014 - 02:57
Permalink
little problem with delimited files
Hi,
I had a little problem with importing delimited files (so as EBCDIC, ASCII and PDF). It gives me error on line:
importname = Right(ffile, firstbackspacenum - 1)
My guess it's because of "ext" parameter ("*") on line: (FindAllFiles(sDirectoryLocation, "*", files()) = true) ThenFor some reason it can't translate * to all file types (if I replace * with TXT or CSV for example - it's run). So, I just hided line "importname = Right(ffile, firstbackspacenum - 1)" - I don't founed any use you make with "importname" in any case (I hope I don't missing something that may cause me problem...)
Anyway, now it works. So thank you very much for the script, it's really very helpful!
Brian Element
Sun, 05/04/2014 - 05:34
Permalink
Hi Shlomit, I am glad you
Hi Shlomit, I am glad you found a solution. Usually the * is used to find all information, so I am not sure why it does not work for you. Can you let me know what version of IDEA you are using and if it is the ASCII or the Unicode. I am finding there are some differences in how things are done depending on the versions of IDEA.
shlomit
Sun, 05/04/2014 - 08:56
Permalink
I'm using IDEA 8.0.3.179 with
I'm using IDEA 8.0.3.179 with ASCII settings.
I'll let you know if I'll find more information about the problem.
Jamshadali
Fri, 05/09/2014 - 03:01
Permalink
Import Multiple files
Hi Brian,
I Just copied the attached script in the Idea script editor between Sub main and End sub. Iam getting the error below. Could you pls help
Syntax Error. Erroron line 56.
Pls note. Line 56 is "Option Explicit"
Brian Element
Fri, 05/09/2014 - 07:22
Permalink
Hello Jamshadali,
Hello Jamshadali,
Instead of doing a copy paste could you instead download the file and try it. If you do a copy paste the dialogs may give you errors which seems to be the case as line 56 is the first line you see in the editor but the error might be with the dialogs. Unfortunately the IDEAScripting editor isn't the best for finding out exactly where an error is. So just download the file and try and run it and let me know if you are getting the same error.
Thanks
Brian
Jamshadali
Tue, 05/13/2014 - 08:13
Permalink
Hi Brian
Thanx for guiding me.
I downloaded it. Since that file is a text file, Im unable to open in IDEA. Also. after downloading I've changed the extension of the file as ".iss" and tried too. But unable to do that.
Pls help
Thanks, Jamshad
Brian Element
Tue, 05/13/2014 - 09:20
Permalink
That is strange, it shouldn't
That is strange, it shouldn't be downlading as a text file. If you right click and select save as over the IS_Import_Files_Script V1.2a.iss it is not allowing you to save as an iss file but changing it to a text file? That is strange. If you still have problems PM me with your email address and I will email it to you.
Jambob
Wed, 05/21/2014 - 11:06
Permalink
PDF
Hi again Brian,
I seem to be having trouble with PDF files? It doesnt seem to acknowledge them when i select my working folder?
it works fine for excel files but just wont work for PDF's.
any IDEAS?
thanks
Brian Element
Thu, 05/22/2014 - 07:35
Permalink
Hi Jambob, I will have a look
Hi Jambob, I will have a look at this tonight or this weekend and get back to you.
Jambob
Thu, 05/22/2014 - 10:38
Permalink
Not sure if this helps but i
Not sure if this helps but i'm on Adobe Acrobat Pro 9?
Some files i had when i was on Adobe Standard 7 will show in Working folder, but not Adobe 9 ones?
Could this be the reason?
Thanks
Brian Element
Thu, 05/22/2014 - 13:43
Permalink
What is the extension of the
What is the extension of the files? The script is looking for pdf files so if the extension is different that would be a problem. Any chance you can send me over a sample so I could update the script for the Pro 9 files?
Brian Element
Mon, 05/26/2014 - 07:37
Permalink
The problems seems to do when
The problems seems to do when doing a file search using *.*, certain systems don't seem to pick-up all the files. In IDEA, can you go to the print report import, when it shows the list of files in file type select "All Files" and does it use *.* for your system or something else?
Jambob
Wed, 06/25/2014 - 08:34
Permalink
*.*
Hi Brian sorry for delay.
Yes it does show all files as *.*
Still it will find some pdf just not anything new?
Brian Element
Wed, 06/25/2014 - 09:24
Permalink
Hi Jambob,
Hi Jambob,
When you talk about finding some pdfs not anything new are you talking about finding pdfs in IDEA or in the script? Also I don't quite understand, you can see some pdfs but not all of them? Also how is it new, are these pdfs that you have added recently, while the script was open, etc? I am just trying to understand the problem to see if I can come up with a solution.
Thanks
Brian
Jambob
Wed, 07/02/2014 - 09:29
Permalink
PDF's
Hi again Brian,
The problem is that when i receive PDF files for analysis. i set my working folder in IDEA, then when i try and import them with your script it will show the main folder. When that is selected no PDF's are available. Now when i do the same with an earlier case of mine. They show up after doing the exact same process?
The only thing i can think of is that i went from Adobe Acrobat Reader to Adobe Acrobat Pro 9.2?
Ive tried alsorts of things to get it to work but i just cant seem to get it to show any new PDF's i receive. I can import these PDF's fine via the normal Report Reader in IDEA so iknow it can handle them. Its just baffelling as to why your script can find some, but not new ones?
Thanks
Jambob
Wed, 07/02/2014 - 09:39
Permalink
Error message
i now seem to get an error message asking me to run the script direct.
the problem highlights this area of script
importname = Right(ffile, firstbackspacenum -1 )
Thanks
Brian Element
Wed, 07/02/2014 - 09:45
Permalink
It sounds like they went from
It sounds like they went from a 3 letter extension (pdf) to a 4 letter extension in the newest version, much like excel went from xls to xlsx and that would probably be causing the problem and giving you the above error. When I have a chance I will have a look at it.
city1
Tue, 10/21/2014 - 23:02
Permalink
This is brilliant.
This is brilliant. Sometimes we have to import over 100 files, and we have been doing this one file at a time, a very tedious and time-consuming process. This script could save us lots of time. Thank you for posting this script.
Brian Element
Wed, 10/22/2014 - 09:35
Permalink
Glad you enjoyed it, I think
Glad you enjoyed it, I think it is one of the more useful scripts on the site.
jerome.whitehead
Tue, 12/30/2014 - 08:51
Permalink
Works Great!
You just saved me a ton of time! Many thanks!
Brian Element
Tue, 12/30/2014 - 08:59
Permalink
Hello Jerome and welcome to
Hello Jerome and welcome to the site.
Glad I could help, let me know if there are other things that might help you out.
Brian
Christian
Wed, 02/25/2015 - 03:18
Permalink
Excel file with several worksheets
Hello Brian,
I detected your site yesterday - thank you for your support, this is a great help for me.
Unfortunately I have a problem with the import of excel files using this script. From time to time I have to import a large number of excel files; some of them consist of several worksheets. When I use the script only the first worksheet of each file will be imported, the others remain unconsidered.
Do you see any potential solution for this problem?
Greetings from Germany,
Christian
Brian Element
Wed, 02/25/2015 - 07:41
Permalink
Hell Christian and welcome to
Hell Christian and welcome to the site.
Actually that is a good add-on that I should be able to add to this script. Would you be willing to test it out for me when I get the chance to add it?
Thanks again.
Brian
Christian
Thu, 02/26/2015 - 04:48
Permalink
Hello Brian,
Hello Brian,
Of course I would test it, this would help me very much.
Best wishes,
Christian
Brian Element
Thu, 02/26/2015 - 07:02
Permalink
Excellent, I will send you a
Excellent, I will send you a message when I have a chance to do the update.
Thanks
Brian
Newtoscripts
Tue, 05/05/2015 - 05:16
Permalink
THANKS
thanks for the script, it works very well for importing multiple PDF files.
DAVEG
Mon, 05/11/2015 - 23:41
Permalink
Import Multiple Files NO DIALOG TO SELECT FILES
Hi Brian, using Idea version 9, trying to import Excel files, using the latest version of this macro, the Excel files are in a sub of working directory. I get to choose directory but don't get asked to choose files. It keeps saying please select files. This is an external project, not a managed one. Any ideas? I added a MsgBox to show sIDEAVer, and it seems to be properly detecting ver 9. In the section where you detect the idea vers and execute different code, for version 9 thes is no Case 2 ( which correponds to Excel )
THX
Brian Element
Tue, 05/12/2015 - 07:43
Permalink
Hi Dave,
Hi Dave,
So when you run the script you select the MS Excel format and then click on Select Files. So you see the following "Browse for Folder" dialog, you then select the folder, hit OK but nothing happens, you don't see this dialog?
What file extension do your excel files have? The above dialog won't open if there are no proper files in the directory (and I just realized I need to add a pop-up saying that so you are not guessing), in your case no excel files. Right now it is looking for xls, xlsx and xlsm files, what extension do your files have?
Thanks
Brian
DAVEG
Tue, 05/12/2015 - 10:49
Permalink
That's right. Choose folder.
That's right. Choose folder. Don't see the choose files dialog. There is a mix of xls and xlsx files in the directory.
Brian Element
Tue, 05/12/2015 - 12:47
Permalink
Hi David,
Hi David,
I am not sure what is going on as it works fine with me. Here is an example of it showing several different types of excel files.
Any chance you can take some screen prints or something so I can see what the files look like and then maybe I can figure out what is going on.
Thanks
Brian
KrisW
Mon, 03/18/2019 - 16:05
Permalink
Hi Brian,
Hi Brian,
We have a client that has run into the same problem. I did a GoToMeeting... External Project, files are in a folder located at the "root" of her Working Directory. 41 Excel files, but when she chooses the folder from the BROWSE FOR FOLDER dialog, everything locks up & the SELECT FILES dialog never loads. I am unable to replicate, sent her a copy of the Jan-2017 version, Client is running IDEA 10.0
Brian Element
Tue, 03/19/2019 - 07:29
Permalink
Hi Kris,
Hi Kris,
I had an email from your client last night, I have asked them for some more information so I can try and figure out what might the problem be. Hopefully if I can see the problem I can figure it out.
Thanks
Brian
Pages