Importing data using "Bulk Insert"

[monikan]

I am building a aspx/c# application with SQL Server 2000 backend. Now i want to have the option for "Importing" the data into one of the tables in my database.

The source file for the import is a text file , CSV format. I want the users to click on the "Import" button placed on my webform and supply the souce file and the data should get imported into the SQL Server 2000 database table.

I am using a sqlCommand to invoke the query"

"BULK INSERT users FROM 'c:\user.csv' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')"; "

and getting an error !! Please help!!

[MAK]

Did you copy the csv file to C:\ of your SQL Server box?

Have to use UNC path to import files from front end.

or use DTS. I already answered your question reg. this

http://forums.databasejournal.com/sh...threadid=35818

[monikan]

Yes i did copy the file to c:\ of my SQL Server Box !!! It still isn't helping !!!

Well, regarding the last answer , i wanted to know about the DTS, now i want to know about the Bulk Insert...i do know that you replied to my DTS question.

[skhanal]

The user account you are using in your application to connect to sql server must be a member of bulk administrator role to do bulk insert.

[MAK]

What error you are getting?

[monikan]

Could not bulk insert. File 'c:/user.csv' does not exist.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Could not bulk insert. File 'c:/user.csv' does not exist.

Source Error:


Line 63: {
Line 64: sqlConnection1.Open();
Line 65: sqlCommand1.ExecuteNonQuery();
Line 66: sqlConnection1.Close();
Line 67: }


Source File: c:\inetpub\wwwroot\webapplication3\webform1.aspx.c s Line: 65




I am using sqlCommand with the "Bulk Insert" command on my webform !!

[monikan]

Originally posted by skhanal
The user account you are using in your application to connect to sql server must be a member of bulk administrator role to do bulk insert.

I have full admin rights !!!

[MAK]

http://www.experts-exchange.com/Prog..._20836442.html

[skhanal]

You have to put .CSV file in the server where your webform is running (wherever the SQL connection is initiated) not in SQL Server. I don't know much about webform so I don't know where it runs, if it runs in client then you need to put the file in client.

[monikan]

I have actually copied the file at both the places, client and server !! It still gives me the same error !! "Cannot find file" !! Going crazy !!!

[MAK]

How about UNC path? Did you try it?

BULK INSERT users FROM '\\servername\sharename\user.csv' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')

[monikan]

Yeah i tried , its working fine now, though i am having problems with one of the datetime columns....working on it ....
Thanks for all your help !!!


Regards
Monika