APEX-PLUGIN.COM

..the apex plugin directory

an initiative of

 

Top Rated Plugins

Add your APEX4.0 Plugin info

Describe your Plugin’s features. You can upload the Plugin’s code and documentation, or, alternatively, submit the URLs to your website or blog.

You are here:
Follow us on Twitter
Process Type Plugin EXCEL2COLLECTIONS

EXCEL2COLLECTIONS

10
Categories: Process Type Plugin
Author: Anton Scheffer

Upload an Excel file into an Apex Collection

This is a Process Type Plugin to load an Excel file into an Apex Collection.
Excel can be in format XLSX, XLS, XML 2003 or CSV. Because the data is loaded into the "varchar2" columns of an Apex Collection only the first 50 columns of an Excel sheet are loaded.

New version 0.804
  XLSX: Fix bug for formated strings
  CSV: Support for HT separator
New version 0.802
  Support for XLSX numbers stored in scientific notation
  Fix bug for XLSX empty strings


Keywords
Excel

Oracle APEX Plugin
Company: AMIS
Date added: 24.2.2013
Views: 4674
Votes: 5
Reviews: 15
Min. APEX Version:
4.0
Share on Facebook
Digg! Share on Digg
Save This Page
Link to:
Download
download apex plugin
If something is not correct please report it here: Report Listing
Do you have a question about this Plugin? Want to write a Review or Comment?
Login first.
Reviews / Questions / Comments are e-mailed to the author of the Plug-in.
0 
@Josip
by Anton Scheffer
on November 29, 2013
I've changed the format in the version at the demo page to dd-mm-yyyy hh24:mi:ss
0 
by Josip
on November 29, 2013
Now , plugin read strings from xlsx but I have problems with dates. If there is date field in xlsx I get only truncated date (01-01-2013) but in xlsx file I have 01-01-2013 16:00. It seems that somewhere in plugin code date value is truncated to dd-mm-yyyy without time.
Thank You for your efforts on my behalf!
Josip
0 
@Josip
by Anton Scheffer
on November 22, 2013
Oracle might not support it's 10.2 databases anymore, I do!. I've just make an new version of this plugin which might support XLSX-strings on a 10.2 database. I'm not sure about dates. Give it a try and let me know. You can find a download link at the demo page.
0 
by Josip
on November 21, 2013
I have 10.2.0.4.0 database :( Ok, i'll find another solution then:)
Thanks,

Josip
0 
@Josip
by Anton Scheffer
on November 21, 2013
Last time I checked (one minute ago) it worked for me. But I'not working on an unsupported 10.2 database :). I know that xlsx has troubles with strings on some 10.2 databases, I never heard of problems with dates.
10 
Problem when importing xlsx
by Josip
on November 21, 2013
Dear Anton,
I downloaded your nice plugin to my apex workspace. When importing xls files everything is ok but when importing xlsx files, plugin ony imports number cells in apex_collection. Cells that have date and strings values doesn't imports into apex collection.
Best regards,

Josip
10 
Problem when importing xlsx
by Josip
on November 21, 2013
Dear Anton,
I downloaded your nice plugin to my apex workspace. When importing xls files everything is ok but when importing xlsx files, plugin ony imports number cells in apex_collection. Cells that have date and strings values doesn't imports into apex collection.
Best regards,

Josip
0 
by Michael Kutz
on November 18, 2013
I see... I understand the coding problem.

I really just need to map sheet name to collection name.

How about storing the mapping for collection name <-> sheet name inside another collection?
That collection could have the suffix of '$MAP'
C001 == Collection Name
C002 == Sheet Name

I have a process right after Excel2Collection that does a lot of:
INSERT INTO {specific table}
SELECT ...
FROM APEX_COLLECTIONS
WHERE COLLECTION_NAME = {something representing a specific sheet name}

One INSERT is for the master table (one row only)
and one INSERT for each of the child tables.
note : some data will span multiple sheets that can be identified with LIKE or REGEXP_LIKE

Sheet Names are preassigned and do not change from file to file.
(ie I know what sheet names are and how to process them ahead of time)

If you need more information, feel free to contact me via e-mail (i have the 'show email' checked)

thanks,

MK
0 
@Michael Kutz
by Anton Scheffer
on November 16, 2013
Are you sure that's it just a small tweak? Adding the sheetname to clob001 would make the plugin a lot slower, as I have to change from add_members to add_member. Using the sheetname as collection name would make further processing very difficult, as you don't know the name of your collection beforehand.
And you know that this plugin has a parameter to restrict it to loading only a Sheet with a specific name? And that on Apex 4.2 you change the value for that parameter on runtime?
10 
Needs a small tweak
by Michael Kutz
on November 14, 2013
Anton,
Your plugin works very well with all of our Excel files.

However, I need to identify the sheets by name for further processing.

The value could be the collection name, or it could be stored in the CLOB001 column.

Again, many thanks for your contribution to the community.

MK
Powered by Sigsiu.NET RSS Feeds

Disclaimer: The APEX Plugins on this site are not supported by Oracle Support Services.

If you have a question about a Plugin or need support: Login and select the plugin detail page. At the bottom you can enter your Question / Review / Remark.
Your message will be sent to the author of the plugin.