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
KrisW
Tue, 03/19/2019 - 10:22
Permalink
File browser not displaying
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.
ZBrown
Wed, 09/02/2015 - 14:29
Permalink
Excel Imports have # of records but no data
Excel .xslx imports have the right # of records but no data (all fields are blank) after the import. How can I fix this?
Brian Element
Wed, 09/02/2015 - 14:54
Permalink
Hello and welcome to the site
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
sspsuhaib
Mon, 09/07/2015 - 13:13
Permalink
Try importing in other format
Hello,
You can try coping the data into a new excel file and then try importing it into IDEA. If the problem still exists, try importing the data in text format. Should solve the problem.
Thanks,
Suhaib
fmvy5513
Sat, 01/16/2016 - 02:37
Permalink
Thanks for the scripts
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
Permalink
Hello and I am glad that the
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
Permalink
select record definition
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
Mon, 06/06/2016 - 19:10
Permalink
Hi Giorgia,
Hi Giorgia,
What type of a text file is it, is it a delimited or a flat file?
Brian
giorgia.basso
Tue, 06/07/2016 - 08:33
Permalink
it's a delimited .csv file
it's a delimited .csv file
Brian Element
Tue, 06/07/2016 - 08:53
Permalink
Thanks for the info, also I
Thanks for the info, also I forgot to ask, which version of IDEA are you running?
Brian Element
Tue, 06/07/2016 - 09:17
Permalink
Hi Giorgia,
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
giorgia.basso
Tue, 06/07/2016 - 12:21
Permalink
I always imported excel files
I always imported excel files and had no issues. I am using IDEA version 10
FIDEA
Tue, 08/09/2016 - 09:06
Permalink
Each time different outcome
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
Permalink
That is strange. I don't
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
FIDEA
Wed, 08/10/2016 - 02:58
Permalink
I use IDEA 9 and the 12
I use IDEA 9 and the 12 files are in Excel format.
Brian Element
Wed, 08/10/2016 - 07:25
Permalink
Hi Joanna, thanks for the
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
Permalink
Export Multiple Files
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
Permalink
Error with Import multiple Excel Files
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.
Brian Element
Wed, 01/11/2017 - 07:21
Permalink
That is an interesting one.
That is an interesting one. I will have to test it out. If you could let me know the names of the sheets that were giving you problems and what the error was that would be great.
Thanks
Brian
fmvy5513
Wed, 01/11/2017 - 21:44
Permalink
The Names of the sheets
It works with single excel file, but don't work with multiple excel files.
The Names of the sheets:
Brian Element
Sun, 01/15/2017 - 05:10
Permalink
Thanks for the info. When I
Thanks for the info. When I have a chance I will try and replicate the problem and see if I can find a way to fix it.
Brian Element
Sun, 01/22/2017 - 18:25
Permalink
Hi Joe, I think I have fixed
Hi Joe, I think I have fixed the problem. Download the newest version and give it a try.
stephen
Thu, 05/04/2017 - 12:28
Permalink
script to import mutiples excel files on version 10
Please help with script to import multiple excel files to version 10 caseware idea.
Brian Element
Fri, 05/05/2017 - 07:40
Permalink
Hi stephen,
Hi stephen,
Version 9 of the script should work in version 10 with no problems. If you find a problem let me know.
Thanks
Brian
cyndie
Tue, 12/12/2017 - 06:42
Permalink
Hello
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
Permalink
Hello cyndie and welcome to
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
Permalink
The following message appears
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?
cyndie
Wed, 12/20/2017 - 09:32
Permalink
Hello
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
Permalink
Hi Cyndie,
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
Permalink
Good morning,
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
Permalink
Error 9 - Unable to import file due invalid database name
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
Permalink
Hi Rahul,
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
Permalink
Not enough storage - error message
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
Permalink
Import Script for V10.4
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
Permalink
Delimited Text file - import
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
Permalink
Excel Import failure - Sheet Names
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
Permalink
Idem database
Hello,
Please dos this script could put the files in the same database ?
Brian Element
Fri, 09/13/2019 - 07:40
Permalink
HI Idem,
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
Permalink
Multi PDF Import
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
Permalink
Definition error when importing txt delimited files
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
Brian Element
Fri, 01/03/2020 - 06:55
Permalink
Are you using the ASCII or
Are you using the ASCII or Unicode version of IDEA? That might be a problem.
haimico2
Mon, 01/06/2020 - 15:49
Permalink
Are you using the ASCII or
Hi Brian,
Thanks for the response. I am using the ASCII one.
Victor
sbheber2000
Wed, 10/27/2021 - 02:06
Permalink
Select Folder
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
Permalink
Version 11
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
Permalink
If you are on V11 of IDEA
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.
Pages