No question at this time
DBA Top 10
1 A. Kavsek 11100
2 M. Cadot 7600
3 B. Vroman 5100
4 P. Wisse 5000
5 T. Boles 2000
5 . Lauri 2000
7 J. PĂ©ran 1100
8 J. Schnackenberg 700
9 R. Pattyn 600
10 T. P 500
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48435
Total active users1504
Act. users last 24h5
Act. users last hour0
Registered user hits last week139
Registered user hits last month778
Go up

Best way to upload Excel
Next thread: DataGuard broker configuration with service_name
Prev thread: Question about licensing

Message Score Author Date
I have database sitting on some server and applica...... Nishant Kariya Oct 08, 2019, 11:28
Use the standard http/ftp way to upload the file...... Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts Michel Cadot Oct 08, 2019, 12:28
Hi, imho, you should write your way out the pro...... Score: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 Pts Ales Kavsek Oct 08, 2019, 12:45
Yeah Michel. I will have to convert that xlsx to c...... Nishant Kariya Oct 08, 2019, 16:32
I agree and now I am thinking this should be the b...... Nishant Kariya Oct 08, 2019, 16:37

Follow up by mail Click here


Subject: Best way to upload Excel
Author: Nishant Kariya, India
Date: Oct 08, 2019, 11:28, 7 days ago
Message: I have database sitting on some server and application(that is designed on K2) sitting on different server. And users will be accessing application all over the regions from their system via URL.

Requirement is to provide functionality to upload excel from frontend screen of K2 application. Data of which will be stored in database table.

Now possible methods are using SQL LOADER, External tables and UTL_FILE

SQL Loader - If I go with this option I will need a oracle client on application server on which K2 is hosted.(Correct me if I am wrong)
Process would be to take the file from users machine and place it on application server(this will be done by application) And then invoke SQL Loader via some script to load data

External Tables - If I go with this option I would need a directory. Now the problem is how to create a oracle directory that points to path on application server. If I can do this easily then this would be the great option.

UTL_FILE - If I use this package to play then also I would need a directory at remote location.


Please someone suggest the best possible way out.
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Best way to upload Excel
Author: Michel Cadot, France
Date: Oct 08, 2019, 12:28, 7 days ago
Score:   Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts
Message:
Use the standard http/ftp way to upload the file from the client machine to the application server. This has nothing to do with Oracle.
Then use one of the standard Oracle ways to insert the data into a table.
Note that external table and UTL_FILE require the file to be on the database server, so SQL*Loader (on the application server) seems to be the way for you (assuming what you call "excel file" is "csv file").

Regards
Michel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Best way to upload Excel
Author: Ales Kavsek, Slovenia
Date: Oct 08, 2019, 12:45, 7 days ago
Score:   Score: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 PtsScore: 600 Pts
Message: Hi,

imho, you should write your way out the problem to upload data from excel to Oracle with *your* application code (whatever you use, python, Java, etc.).

Regarding your three mentioned options, just a comment from me:

SQL Loader
- you'll need Oracle instant client (version >=12.2) at app server
- you'll need "Excel" in csv format, if you have xlsx, you'll have to convert to csv first,
- you'll have to write error checking script that'll properly handle errors (this can get very tricky if Excel files are human generated stuff)

External Tables
- this can be more elegant than SQL Loader, if you can:

** setup some sort of access path (scp?) to directory on RDBMS server, so that app will upload file to DB server

** use some tool that can read xlsx files directly, such as one that you can find here:
https://odieweblog.wordpress.com/2016/06/21/reading-an-excel-file-xlsx-as-an-external-table/

A variation of solution if you can not get scp access to the DB server, that you write some PL/SQL code using UTL_HTTP and retrieve xlsx file(s) via url from your APP server.
I would not even think about messing with external table directory residing on APP server...it's just bad design:)

UTL_FILE
Just don't...nothing more to say. :)

Personally, I would not pick neither one of the options (SQL Loader, External Table, UTL_FILE), you should deal with excel files within the application code that runs on APP server (python, java, ...).

Regards,
Ales



Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Best way to upload Excel
Author: Nishant Kariya, India
Date: Oct 08, 2019, 16:32, 7 days ago
Message: Yeah Michel. I will have to convert that xlsx to csv. And sql loader is the only option if we have to use some DB feature to deal with this.

Thanks !!
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Best way to upload Excel
Author: Nishant Kariya, India
Date: Oct 08, 2019, 16:37, 7 days ago
Message: I agree and now I am thinking this should be the best option. Currently application is doing that but somewhat in different fashion which is not performance wise effective.

Currently application is reading excel and sending each row as input to a procedure that inserts data. This is not good for me. If I have 1000 rows then proc will be called same number of times.

I can ask application developers to process file in a way that all entries are inserted at once.

Cool, thanks !
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here