Import Multiple Files

Brian Element
Background

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.

Documentation

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:

Import 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:

Select Directory

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.

Select Files

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.

Select definition

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.

Language
IDEAScript

Jambob Fri, 03/28/2014 - 06:55

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

Hi
Ive now sorted the above , thank you for this amazing script

Brian Element Fri, 03/28/2014 - 08:22

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

Thank you very much Brian.
This is exactly what I was looking for.
Greetings from Spain.
 

Brian Element Fri, 04/04/2014 - 08:31

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

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

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

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

Hello Promila, which version of IDEA are you using?  Unfortunately the editor does not always give the right error spot.

Brian Element Tue, 04/29/2014 - 12:17

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

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

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

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

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

Here is explanation-
Set the path to the current working directory.

Syntax
Client.Working Directory
Type
String
Example
Sub Main
' Get the project name information.
MsgBox Client.Name
' Set the working directory
Client.WorkingDirectory = "C:\Program Files\IDEA\LangBrowserHelp\SampleFiles\"
MsgBox Client.WorkingDirectory
I tried executing this script and it is also giving the same error.
 

Brian Element Wed, 04/30/2014 - 11:43

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

Sure, Thanks for all your help.

shlomit Sun, 05/04/2014 - 02:57

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

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.

Jamshadali Fri, 05/09/2014 - 03:01

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

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

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

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

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

Jambob Thu, 05/22/2014 - 10:38

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

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

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

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

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

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

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

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

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.

jerome.whitehead Tue, 12/30/2014 - 08:51

You just saved me a ton of time!  Many thanks!

Brian Element Tue, 12/30/2014 - 08:59

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

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

Christian Thu, 02/26/2015 - 04:48

Hello Brian,
 
Of course I would test it, this would help me very much.
 
Best wishes,
Christian

Newtoscripts Tue, 05/05/2015 - 05:16

thanks for the script, it works very well for importing multiple PDF files.
 

DAVEG Mon, 05/11/2015 - 23:41

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

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

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

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

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

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