Importing multiple excel worksheets

11 posts / 0 new
Log in or register to post comments
Last post
Thu, 05/09/2013 - 11:31
#1
aveenm293
Offline
Joined: 10/23/2012 - 16:02
Importing multiple excel worksheets

Hi Brian
I am currently using IDEA 8.5. I have 1000 excel files (.xlsx) each named differently with different sheet names (1 sheet per workbook). I have all these excel files stored in one folder.
I am looking for a script that will allow me to import all at one go as opposed to importing them one by one.
Thank you in advance.
Regards
Aveen
 
 

Top
Thu, 05/09/2013 - 12:06
Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

Hi Aveen, there is a file at the IDEA web site http://ideasupport.caseware.com/ideascript/downloads/default.aspx called Import Multiple Files that might work for you.  Can you let me know if it works for you or else I can create something for you.

Top
Thu, 05/09/2013 - 12:46
(Reply to #2)
aveenm293
Offline
Joined: 10/23/2012 - 16:02

Hi Brian
I can't seem to access the site. Please can you create something for me.
Thank you
 
 
 

Top
Thu, 05/09/2013 - 13:11
Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

No problem, I will put something together for you tonight and post it here.  So check back tomorrow.

Top
Thu, 05/09/2013 - 17:10
Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

Hi Aveen, try out this script.  It will open with a dialog to select the directory that contains all your excel files.  It will then import all the xls files in the directory.  It only imports the first worksheet from each excel file.  Let me know if this is what you are looking for.

Files: 
Binary Data Import Excel Files.iss
Top
Fri, 05/10/2013 - 04:42
(Reply to #5)
aveenm293
Offline
Joined: 10/23/2012 - 16:02

Hi Brian
It's perfect and works great. If possible can you maybe download the one from CaseWare site and upload for me? Also is it possible that when importing a file and you haven't checked "First row is field names" that once imported you can make the first rows the field names without re-importing?
Thank you
Regards
Aveen
 
 

Top
Fri, 05/10/2013 - 07:17
Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

Hi Aveen, I have attached the ideascript developed by Caseware to import multiple files.  It is more comprehensive then the one I gave you as it can handle different file types.

For your second questions, the only way I can think of to do this is first go to the Field Manipulation and change the field names to what is contained in the first row.  Then perform an extraction using the following forumula: @Precno() > 1 - this will extract all the records except the first one.  It might be quicker to reimport the excel file but if you don't have access to the file any longer these steps will give you the same thing.

Files: 
Binary Data Import_Multiple_Files.iss
Top
Fri, 05/10/2013 - 08:39
(Reply to #7)
aveenm293
Offline
Joined: 10/23/2012 - 16:02

Hi Brain
Wow this stuff is amazing.
Thank you very much.

Top
Fri, 05/10/2013 - 09:20
Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

Hi Aveen,

Glad you are finding it useful.

Brian

Top
Thu, 08/29/2013 - 02:55
angelaclancy
Offline
Joined: 08/29/2013 - 02:44

Hi Brian - i have the same problem. Are you able to send me the script as well? or is it located in this site.
Thanks

Top
Thu, 08/29/2013 - 05:27
(Reply to #10)
Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

Hi Angela,

Look about 3 message up and there is an attachment of the Caseware/IDEA script to do the imports.  Let me know if this works for you and if not how I can help out.

Thanks

Brian

Top