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

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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

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


"Telmo Sampaio" <(E-Mail Removed)> wrote in message news:<#(E-Mail Removed)>...
> 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
>