Importing multiple excel worksheets

aveenm293

Forums

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
 
 

Brian Element Thu, 05/09/2013 - 13:11

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

Brian Element Thu, 05/09/2013 - 17:10

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
27.gif (9.55 KB)

aveenm293 Fri, 05/10/2013 - 04:42

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
 
 

Brian Element Fri, 05/10/2013 - 07:17

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.

Brian Element Fri, 05/10/2013 - 09:20

Hi Aveen,

Glad you are finding it useful.

Brian

angelaclancy Thu, 08/29/2013 - 02:55

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

Brian Element Thu, 08/29/2013 - 05:27

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