Best method for sending data from Excel to remote SQL Server Database?

[Trond Hindenes]

Hello all,
I am working on a application for analyzing data from a SQL Server
Database using vb.net. THe application will mostly be web-based,
although we migt use some Windows Forms for some of the components.

Anyway, the data to be analyzed comes in Excel format, and needs to be
converted into SQL Server in some way or another, And I'm not sure
which is the best way. I find lots of info on how to pull data from
SQL Server and present it in a Excel spreadsheet, but not too much on
doing it the other way around.

The SQL Server database will not be on the same network as the Excel
Application, so security is an issue, e.g. I cant import it via DTS
directly from the SQL Server. As far as I can see, I have the
following options:

-Create a web services that Excel submits to, and make the web service
update the Database
-Use .Net remoting to perform the updating
-Use smart clients to create a small windwos form that is downloaded
to the client and reference the excel sheet as a datasource from
there, upload via xml or web service
-Other

Im very interested in any views on what would be better (or worse),
and of course if you guys have any other options that I haven't
specified here, please let me know.

Best regards,
Trond Hindenes
Norway

 

[Telmo Sampaio]

Trond,

Have you considered using DTS on the SQL Server to pull the data from the
Excel Spreadsheet? You can create a simple DTS package that runs every now
and then (or continously) checks to see if there is a new file in a folder
and imports it to SQL. Quicker than writing an app or web service... just my
0.02

Telmo Sampaio

 

[Trond Hindenes]

Hi Telmo,

The problem is that the SQL Server and the Excel sheet will not be on
the same network. I think DTS needs file access to the Excel file at
the time of the import, which will not be possible in this case. One
option is of course to create som kind of file upload mechanism to
move the Excel file to the webserver where the SQL Server sits, but
I'm afraid that will give me an environment that is a bit to
uncontrolled...

best regards,
Trond Hindenes