Importing Excel file into a MS-SQL 2005 table

I hope this is the right forum for my question.

I'm developing a website for a Prepaid Calling Cards distributor. Each of the cards they sale have a list of the countries the card is good for. I need to import this data into my countries_rates table. The file they are giving me is an excel file that contain 3 colums (fields)

1- Country-Name

2- Rate

3- Card_$_Price

these files contain aproximaly 400 rows so it will be a hasle to have to insert it manually every week.

In my web application I need to create a form where the user will select the card from a dropdownlist and then find the excel file to be imported for that card.

I would like to know how do I do that with Visual Studio 2005, SLQ 2005 and C#

please direct me to some links where I can learn how to do this or please send me some code snips I can see how is done.

Tia

Charles

In short - here's some old (.NET 1.1) code I used. Basic idea is to uploadfile and then open the excelfile by using a OleDbConnection.

Dim strConnAs String ="Provider=Microsoft.Jet.OleDb.4.0;" _ &"data source=" & sFileName &";" _ &"Extended Properties=Excel 8.0"Dim objConnAs New System.Data.OleDb.OleDbConnection(strConn)Dim oAdapterAs New System.Data.OleDb.OleDbDataAdapter("SELECT * from " &"[Sheet1$]", objConn)

Dim ExcelDataAs DataSet =New DataSetDim oCollAs New CollectionDim nErrAs IntegernErr = 0Dim sErrAs StringTry objConn.Open() oAdapter.Fill(ExcelData)

Now you have the data from Excel in the dataset ExcelData and can loop through it and

insert it/update your SQL db

|||

Thanks for the code snip. But I don't use VB I'm using C# do you have the C# version. Sorry to be such a pain

Tia

Charles

importing data to MSDE

Hello,
What is the best way to import data to a client's MSDE
database from our website? We want our client to log onto
our website so we can update a table on the client's PC. I
need a little direction on this, not sure which is the best
way to go.
Thank you,
Randers
hi Randers,
Randers wrote:
> Hello,
> What is the best way to import data to a client's MSDE
> database from our website? We want our client to log onto
> our website so we can update a table on the client's PC. I
> need a little direction on this, not sure which is the best
> way to go.
> Thank you,
> Randers
perhaps you could provide an ftp based solution to download new data from
your website and the MSDE remote client proceed with a scheduled job to bulk
insert it..
you can even set the job to notify via mail of the success/failure of it...
(just use an SMTP solution like http://www.sqldev.net/xp/xpsmtp.htm )
or mail them, but this become more manual as the final user has perhaps to
read the mail, save the attached file in some defined file system position
and let the scheduled task process the file..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.msde:20144
send them an sql file as they will not be able to login to msde remotely
"Randers" <rmonroe@.aulcorp.com> wrote in message
news:06cc01c52e3e$1e481e20$a501280a@.phx.gbl...
> Hello,
> What is the best way to import data to a client's MSDE
> database from our website? We want our client to log onto
> our website so we can update a table on the client's PC. I
> need a little direction on this, not sure which is the best
> way to go.
> Thank you,
> Randers