Fog Creek Software
Discussion Board

best way to move excel data to sql server

hi, i need to write up a quick web page where people can upload an excel file to the server, and then have something on the server-side pull the data from the excel file into a sql server. this is a very temporary solution...we are architecting a better way to move data around, but in the mean time need a quick fix.

the webserver is IIS 5.1 , with .NET 1.1. I can do this in either VB or C#. I've been searching for examples on the web and it appears that i can do two things:

1. set up some sort of oledb connection to each excel file as it comes in, and use that to pull out the data
2. use excel objects to pull out the data

i'm pretty new to .net programming, so wanted to know if anyone had tips on which of those methods will work the best, or... if there is a better way to do this.

note: this is a very small data set... maybe 5 people will each upload a spreadsheet once or twice a month until the end of the year when we roll out the "real" system.

Wednesday, July 30, 2003

You could also have a regularly scheduled DTS job look for new spreadsheets and import the data.

Wednesday, July 30, 2003

You could use Microsoft Office's object model, an Excel reader component, OLE-DB, use an intermediate format (and then BCP), or DTS.

Li-fan Chen
Wednesday, July 30, 2003

DTS sounds like the way to go here. You could also create a .adp in Access, if your users have that.
Is the data being originated in Excel, or is Excel yet another intermediary?

Israel Orange
Wednesday, July 30, 2003

DTS looks like what I need. thanks!

excel is an intermediary, the data is coming from some horrendous palm pilot application and somehow massaged into a spreadsheet. i'm not really being paid enough to deal with some clever way of getting the data from the desktop to the server, it was hard enough to get the people on that end to get me the data as excel spreadsheets.

Wednesday, July 30, 2003

*  Recent Topics

*  Fog Creek Home