[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 432
  • Last Modified:

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
0
greddin
Asked:
greddin
  • 6
  • 3
  • 2
1 Solution
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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
 
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 6
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now