Upload Excel Spreadsheet into SQL Server

I need to have a webpage that allows a user to browse and upload a Excel spreadsheet and have the processing asp page upload the rows into a SQL database.

Does anyone have any code that's doing this I could re-use?

Thank you,
-Greg
greddinAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

peh803Commented:
Okay, so you want to actually take the contents from an Excel spreadsheet (all columns and rows) and insert the data into a SQL Server table of the same structure?  Is this correct?  Or, do you just physically want to store excel spreadsheets (the actual *.xls files) on your web server?

Regards,
peh803
0
greddinAuthor Commented:
I need to upload the data into the the database tables. I don't so much care about the xls file itself.

Thanks,
-Greg
0
peh803Commented:
OKay -- so then, will the data structures of the XLS file always be the same for all users?  Or, do you need to create tables on the fly for whatever structure the XLS file is in?  These are obviously two very different tasks.

Thanks,
peh803
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

greddinAuthor Commented:
No, my sql database table has fields like:

FirstName
LastName
Address1
Address2
City
State
Zip

..and in the spreadsheet, I want to map names to this stucture.

-Greg
0
peh803Commented:
Okay, so the excel spreadsheet will have a constant structure that will not change; is this correct?  And your SQL server table will always remain the same as well?

Regards,
peh803
0
greddinAuthor Commented:
Yes, it will always be a constant structure. I would think it almost has to be.

Thanks.
0
peh803Commented:
Yes, well, if it wasn't, let's just say it would be much more difficult to code!

Here's how I would approach this problem:

1.) have the user upload the xls file to the server (whether you want to save it or not, you need the file on the server to access the data inside it).  How, you ask, should you have your users upload the files?  FreeASPUpload (http://www.freeaspupload.net/) is a good tool for this, and there are a bunch of other ways as well.  Just google for it, and you'll find a bunch of good alternatives.
2.) using the file name of the file the user just uploaded, create a connection to the excel file using ADO.  Something like this:
  http://www.experts-exchange.com/Web/Web_Languages/ASP/Q_20108086.html
3.) now that you're connected to the excel spreadsheet, loop through the records in the excel sheet and insert them into the appropriate fields in SQL server. (if you need help doing this, let me know)

HTH,
peh803
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
peh803Commented:
Also, you'll want some error-checking code in there to make sure that the file structure that was uploaded (the excel spreadsheet) matches what you're expecting to get...

HTH,
peh803
0
mikoshaCommented:
Thats all great, but if you have MS SQL server as your database , why not to build DTS package on your sql server and run it with proper parameters from the stored procedure ,that will be called from asp code ?
 
0
mikoshaCommented:
sorry, all this what i suggested could work after uploadin an excel file .
0
peh803Commented:
@mikosha -- another very good approach.  Probably easier to maintain than straight ASP + ADO.

Thanks for the good info.

peh803
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.