473,320 Members | 1,857 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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
Nov 20 '05 #1
2 5755
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" <tr***@hindenes.com> wrote in message
news:c1**************************@posting.google.c om...
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

Nov 20 '05 #2
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" <ts******@gmail.com> wrote in message news:<#U*************@TK2MSFTNGP09.phx.gbl>...
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

Nov 20 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Frostillicus | last post by:
I'm trying to get an ASP to return a zip file to the remote browser from an Image (BLOB) field in SQL Server 2000 but Internet Explorer keeps saying: Cannot open C:\Documents and...
5
by: great_googley_moogley | last post by:
Greetings, I am in the process of installing a SQL database at a customer location. I have determined that there are 3 ways to do this, and I wanted to know which is the best of the 3. 1...
11
by: DrUg13 | last post by:
In java, this seems so easy. You need a new object Object test = new Object() gives me exactly what I want. could someone please help me understand the different ways to do the same thing in...
5
by: Andrew S. Giles | last post by:
I thought I would post here, as I am sure someone, somewhere has run into this problem, and might have a good solution for me. I am writing an applicaiton in C# that will accept data and then put...
0
by: dwight | last post by:
hi in a data entry scenario where there is a 2 mb connection between locations i was wondering which technology would be best for the purposes between an asp.net web forms application and a...
12
by: MrQuan | last post by:
G'day all, I have a requirement to communicate between two or more PCs over the Internet, however I have no idea how to go about this. I'm not talking about a chat programme as such, I want to...
2
yashg
by: yashg | last post by:
I am building a data backup application in C# using Sockets. It has a server component and a client component. The client is going to upload files to the server through TCP sockets. I've got all...
4
by: dgleeson3 | last post by:
Hello all I am creating a VB.Net distributed SQL server 2005 application. Each computer in the system has a database with a table of users and their telephone numbers. Each computer has a...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.