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

KrisW Tue, 03/19/2019 - 10:22

Thanks! It was interesting to see that the "error" has poped up before, especially when I could not replicate. I did have her browse w/ Windows Explorer to verify files in folder she selected. I also verified that she was using the copy that looked for XLS, XLSX & XLXM files. Wondered if it might be the Windows issue with multiple monitors, but no matter how much I moved the DIALOG boxes around on my screen, they always went back to the IDEA window next time I ran the script.

Brian Element Wed, 09/02/2015 - 14:54

Hello and welcome to the site.

That is strange, which version of IDEA are you using?  Also are you able to bring in one or more of those files directly into IDEA without problems?  Is there anything different about the excel file?  Any chance you can share one or more of them so I can have a look?

Thanks

Brian

fmvy5513 Sat, 01/16/2016 - 02:37

Hi Brian Element,
Thanks for the scripts,
it really help me a lot and save a lot of time for me,
This script show me the power of automation, so I decide to learn code.
I'm a new beginner to learn Idea script, and I am wondering if you or somebody can give me some guide or manual ?
I appreciated your time and effort.

Brian Element Mon, 01/18/2016 - 07:41

Hello and I am glad that the script helped you out.  IDEAScript is related to Visual Basic for Applications which is used by Excel, Word, etc, so there are lots of places on the web where you can learn the basics.  The specifics of using IDEAScripting you can find in the language browser through the IDEAScript editor that lists all the different IDEAScript commands and samples of code.  There is also a book (link from the main page) called Mastering IDEAScript that could help you out.  I also have a series of videos on the site that you can find under the Videos tab that walks you through the creation of a basic script.  Other things you can do is look through scripts that have already been created and ask questions.  IDEAScript is fairly easy as IDEA will write much of it for you through the History, Project Overview or Recorder so there are lots of options to help you out.  CaseWare also has a 3 day course on IDEAScripting that might be available to you.

So good luck and don't be shy to ask questions.

Brian

giorgia.basso Mon, 06/06/2016 - 18:16

Hi Brian,
I was trying to use your script to import more than 300 text file into IDEA, but I am not able to. I think your select definition button is not working properly or maybe I am the one who is not able to use it. Can you please give me a small help?
 
Thanks,
Giorgia

Brian Element Tue, 06/07/2016 - 09:17

Hi Giorgia,

Where you able to import any files or did you have problems before importing anything.   If you were able to import some of the files did it stop part way through?

Thanks

Brian

FIDEA Tue, 08/09/2016 - 09:06

Hi Brian,
thank you for the script. I works nearly perfectly. There is one thing that bothers me. I can't understand why sometimes it works and sometimes not. 
Example: I have 12 files (for each month: etaty_agregacje_01.2015 , etaty_agregacja_02.2015 , etc.) After using your scripts all files are imported but some of them have the file name, some have the file name + the name of the next file (additional column) and some don't have any column with the file name. I'm not sure how does it work becouse it seems to be random . Do you have idea what can be wrong? Maybe the files' names? 

Brian Element Tue, 08/09/2016 - 09:39

That is strange.  I don't have an answer for you right now.  I looked through the code and I didn't see any obvious problems.  What format were you using to bring in the 12 files and I can do some tests on that format and see if I can see what might be going wrong.

Thanks

Brian

Brian Element Wed, 08/10/2016 - 07:25

Hi Joanna, thanks for the info.  I think I see where the problem might be.  I will have to experiment some.  Right now what the script does is after you import the file it goes to the directory that the file was stored and obtains the latest file.  If the files are coming in too quickly it might be getting confused and causing your problem.  I will have to play with that. Thanks for pointing it out.

Brian

Margaret Thu, 10/06/2016 - 14:31

Hello Brian!
I used the above script and it worked perfectly!  I had to import 500 delimited files.  Now I need to export them as fixed width text files and re-import as print report files.  Do you have a script that can export multiple files at one time?
Thanks,
Margaret
 

fmvy5513 Tue, 01/10/2017 - 01:16

Hi Brian,
thanks for the script. I used many times, and it works almost perfectly. But today I find a little problem with import multiple Excel Files (multiple sheets and some of them with a special symbol "()" )  and it returns an error message. 
 

cyndie Tue, 12/12/2017 - 06:42

Hi Brian,
 
Thank you for providing the script. I am having problem when I run the script, it says that dlgMainMenu is a variable that is not defined. How can I define this variable? Also, I am using version 10.
Thank you.

Brian Element Wed, 12/13/2017 - 08:27

Hello cyndie and welcome to the site.

That is a strange one.  The dlgMainMenu is actually the main dialog.  When you get this error does it give a line number?  Also does it give you the error when you run the script or when you are using it and if you are using it at what point are you getting the error?

The other thing is how did you download it, did you do a right click and save as or download it in another way, just wondering if the dialog part of the script didn't get downloaded.

Thanks

Brian

cyndie Wed, 12/20/2017 - 09:32

The following message appears
 It says "error in line 1 - Variable not defined: dlgMainMenu"
What do you mean by there is a problem in the installation of the program? 

Brian Element Wed, 12/20/2017 - 12:19

Hi Cyndie,

Any chance you can email me the iss file that you are getting the error from as I wouldn't expect to see this error.  You can email it to brian.element@ideascripting.com

Thanks

Brian

Equipmentman1662 Tue, 11/27/2018 - 11:38

Good morning, 
I am a new user to IDEA, and am finding this website to be extremely helpful! If I wanted to take this idea a step further.  How would I set a loop to join all databases in IDEA? Sorry if this is not challenging, I am still learning.

rahulsaxena3 Thu, 12/13/2018 - 05:27

Hi brian,
Firstly thank you for replying to all queries so promptly, appreciated, as this is the only authentic and viable source of information on IDEA on the internet.
Where i am facing an issue is, when import multiple files using your script, this (Error 9) keeps on coming, randomly, if import 10 files it will come after the 6th file and the import will stop.
If i am importing 50 files, this error will come after 30 files. This error always comes while importing excel files. it did not use to come earlier, but now it does, was wondering if its got to with the version, as i am using Client Version 10.2.0.52 
Please let me know in case you can help modifying the script so this can be avoided.
When i import the rest of the files manually in idea, no error comes, which leads me to believe it might be an issue with script.
Thanks in advance.

Brian Element Thu, 12/13/2018 - 07:01

Hi Rahul,

Thanks for the comment.  You are the first one that has pointed this out.  The Excel files you are importing, which version of Excel are they from?  Also do they have one worksheet or multiple worksheets?  I will check this out and see if I can replicate the problem.

Brian

zaxman Fri, 05/03/2019 - 11:47

When I attempt to run the script and only select 1 .txt file to import, I receive the following error message:  Not enough storage is available to complete this section.
 

Duenas-Heinrichs Tue, 07/02/2019 - 10:05

Hi Brian,
I am using v10.4 and downloaded the app from SmartAnalyzer and it worked fine.  May I please request for the iss file of that app?
Greetings from Canada,
Rolet

aliasgar.sk Thu, 08/08/2019 - 04:06

I'm trying to import multiple delimited text files but unable to proceed after selecting all the files.
Am stuck at the "Select Definition" stage, What exactly is a record definition ? and how do I create one for the files I want to import?
Can you please guide me?

raoul.kelly Wed, 08/14/2019 - 13:49

Hi Brian 
 
First off - awesome script for multiple file imports !!
 
I have a small but frustrating bug to report. I experienced when using "c6373_IS_Multi_File _Import_1.0en_10.0_10282C.dpack" , and then used "IS_Import_Files_Script_20170122.iss" to identify the cause.
 
Symptons dpack : Importing .xlsx results in a failure to import . The status indicator with in Smart Analyser indicates that the "database doesnt exist"
 
Symptons .iss  :  Importing .xlsx results in a failure to import . Error message "Error Number 9 Error String The sheet XXX does not exist in the Excel file XXX " 
 
Root cause  :  The Sheet name ( not the file name) had a "-" in the name 
 
Bug fix request  : 1) Allow special characters to be used in the sheet name OR 2) Error message to be produced in the dpack that indicates the source of the problem 
 
Many thanks
 
Raoul
 
 
 
 
 
 

sansii Thu, 08/29/2019 - 12:51

Hello,
Please dos this script could put the files in the same database ?

Brian Element Fri, 09/13/2019 - 07:40

HI Idem,

Sorry I missed this one.  No, each database is in a separate file.  You would then have to append them together.

Brian

BillThompson Fri, 09/13/2019 - 15:39

Hi Brian - Fantastic work, much appreciated!
I'm getting an error when applying a Definition to PDFs: "Error on line 1076 - Invalid procedure call or argument"
(To replicate: Select "Print Report and Adobe PDF", Select Files Button, Choose folder, select all files, Select Definition Button, Choose jpm file, then I receive the error)
If you have any guidance or suggestions, please let me know. Thank you in advance!

haimico2 Thu, 01/02/2020 - 17:19

Hi Brian,
Great work here! honestly it does help a lot and reduces time incredibly! I have been having issues with importing txt delimited files. Whenever I try to import and I select the definition dile (.rdf), it gives me the error stating the following:
Error on line 203 - The file encoding in the record definition file does not match the API being used. Re-generate the record definition file or ensure you are calling the proper API.
I obtained the record definition file from importing on file manually. Am I maybe using the incorrect definition file? I am using IDEA V10.3.0.1139.
Thanks very much for all the help!Kind Regards
Victor

sbheber2000 Wed, 10/27/2021 - 02:06

Hi Brian,
I am trying to use the script in IDEA Caseware 10.0 however the Select Files button does not select the chosen folder. There is no error message, it just doesn't select the folder. Consequently, the alert appears: "Please select the files to be imported". Would you help me.

Simon Humphreys Thu, 11/18/2021 - 08:57

Hi Brian, 
Does this work for Version 11? I am getting an error on line 887 Dim Preserve files... 
If I can get this to work then importing the 250 files I have will be a breeze!
Many thanks, 
Simon 

Brian Element Sat, 11/20/2021 - 08:58

If you are on V11 of IDEA then I recommend you get the Utilities app from Passport as there is an updated Multi-file import that is part of it.