Import excel data into sql table

Hi!

I have to develop an application for transfering data from an excel file into a sql table.The excel file is uploaded to a server.The database(and the table) is on another server.At first,I used openrowset for transferring data to the table.My sql command looked like this(in my asp page):

SQLstr = "SELECT * INTO dbo.shopping_TSR FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database="+Server.MapPath("upload/tmb2.xls")+";hdr=yes', 'SELECT * FROM [Sheet1$]')"

I kept getting this error:

[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.]

After reading a few articles,I think the cause of my error is that the excel file is uploaded into the folder where the asp script is located.I have 2 servers : one running the asp scripts and one containing the database.

Is my error generated by the fact that the excel file is on a different server than the sql server?How could I make this work?

Have you had a look at SSIS (Integration Services) if you're using SQL 2005 or DTS if you're using SQL2000. You can use the Excel Connection Manager. Can your Excel file be accessed by the SQL Server through a share?

|||I'm using SQL Server 2000.I want to automatize the process(so that the users don't have to install SQL server).After reading a few articles,I think I have a problem with the access rights on the SQL Server.Can u confirm me that?sql

Import database from access

Hi
I am develop a project with .net in windows application.my source of the
database set in access I want to working with WMSDE and I need to import the
database under the access to database in WMSDE first Is it passible?
If yes, how can I do this.
Thanks for who refer to my question
doesn't access have an upsizing wizard in it ?
"amos hchmon" <amoshchmon@.discussions.microsoft.com> wrote in message
news:91853F12-5194-4D31-891F-0C5EAFC16E94@.microsoft.com...
> Hi
> I am develop a project with .net in windows application.my source of the
> database set in access I want to working with WMSDE and I need to import
> the
> database under the access to database in WMSDE first Is it passible?
> If yes, how can I do this.
> Thanks for who refer to my question

Import database from access

Hi
I am develop a project with .net in windows application.my source of the
database set in access I want to working with WMSDE and I need to import the
database under the access to database in WMSDE first Is it passible?
If yes, how can I do this.
Thanks for who refer to my questiondoesn't access have an upsizing wizard in it ?
"amos hchmon" <amoshchmon@.discussions.microsoft.com> wrote in message
news:91853F12-5194-4D31-891F-0C5EAFC16E94@.microsoft.com...
> Hi
> I am develop a project with .net in windows application.my source of the
> database set in access I want to working with WMSDE and I need to import
> the
> database under the access to database in WMSDE first Is it passible?
> If yes, how can I do this.
> Thanks for who refer to my question|||you can import the access via SQL Servers import function in the EMC.

Import database from access

Hi
I am develop a project with .net in windows application.my source of the
database set in access I want to working with WMSDE and I need to import th
e
database under the access to database in WMSDE first Is it passible?
If yes, how can I do this.
Thanks for who refer to my questiondoesn't access have an upsizing wizard in it ?
"amos hchmon" <amoshchmon@.discussions.microsoft.com> wrote in message
news:91853F12-5194-4D31-891F-0C5EAFC16E94@.microsoft.com...
> Hi
> I am develop a project with .net in windows application.my source of the
> database set in access I want to working with WMSDE and I need to import
> the
> database under the access to database in WMSDE first Is it passible?
> If yes, how can I do this.
> Thanks for who refer to my question

import data from Text Files into SQL Server...?

Hi,
i wanna develop an web-database application with ASP.NET,C#, SQL server 2000.
i already have some data whichs been in text format(text file) and now, i want to import the same into my database.
the problem is, the text file has got many line breaks and also its not well formated to import it using DTS.
Can any one help me out in importing the same.
thanks in advance

Hello Kiran,
There is no way to transform the text file if it is not formatted and inconsistent.
Only thing you can do is to make it consistent at application end and then passes it to DTS package.
|||Try this link see if you can use BCP (bulk copy) with DTS. Hope this helps.
http://www.sqldts.com/default.aspx?237|||Hi,
Thanks for the response.
Even i think i need to format the text in the front end, before actually using the DTS.And thus formatted one can be easily imported.Well i gonna try that, but the probs is , even if i want to format it in the front end, the same cant be generalized to all, since each of them gonna have there own format.
any more ideas are always welcome