Link to home
Start Free TrialLog in
Avatar of Tech Novice
Tech Novice

asked on

Efficient way to read excel file having huge records online

Hi,

I have an excel file which would contains around 20-30 millions records every time user uploads the same,frequency of upload would be 4-5 times on daily basis (by different users).
Application which is built on java, spring 3 and SQL server as back end.

Just need to know how to process/read the file ( reading the excel file cell by cell and storing in database) in an efficient manner which would not allow user to wait much.
Avatar of Rainer Jeschor
Rainer Jeschor
Flag of Germany image

Hi,
I would suggest to use SSIS package to read the file. If speed matters, you can do it in a two step process - importing the RAW data from Excel into a staging table and then running another package or task to „move“ the data from staging to target table.
Just my 2 ct.
HTH
Rainer
What do you need to do with the data?
Uploading how? You could process the file within the uploading mechanism, excel has several options, csv, excel, xlsx, XML format....

Valuation of data, ?
Avatar of Tech Novice
Tech Novice

ASKER

yes we need to evaluate the data based on some business logic
in what actual format is the data?
While it is an "Excel" file are these plain text csv, or actual excel (ms formatted)?
The processing could occur after the file is uploaded....


must depends on your setup and the resources available to you
ssis bcp can be used if you just load data from this file into the database. or ou could load it as a temporary table, and then run your query and do what you need.
can u pls share some tutorial/link for ssis bcp?
Microsoft has different examples/writeups on their site describing the use of bcp, and ssis.
What is the relationship web server and SQL ?
Same server, two separate servers?

If you want, you can handle the process, upload the file, then begin processing it, notifying user of errors ir successful conclusion.


I.e. Submit file for processing,  the user can return and view whether their file was process or get notified.....

I prefer to have control in the form of detecting if there is an error, ....
ASKER CERTIFIED SOLUTION
Avatar of arnold
arnold
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial