import from excel to SQL Server - can it be more efficient ?

Hello All,

Iam new to the world of ASP .Net. Right now iam building an application that will IMPORT about 5,000 records from an Excel spreadsheet to a table in MS SQL Server. Right now the code works correctly, but i feel it is not efficient and takes a little bit of more time in doing the import. Could you guys throw some light on how i can make the code run more faster ? Someone suggested me that i can use DataAdapter and update the table in the database thru an update method available with it. I dont know how to do it? Could anyone share with me a snippet of code that does this ?

Here is my code:

Private Sub ProcessRecords()
Dim ds2 As New DataSet
' readExcelSheet is a user-defined function that reads a spreadsheet and returns a DataSet object
ds2 = readExcelSheet("C:\Inetpub\wwwroot\Project1\Book2.xls", "SELECT * FROM [Sheet1$]")
Dim myConnection As SqlConnection = Connection() ' user-defined function that returns a SQLConnection object
myConnection.Open()
Dim strSQL As String = "insert_member" ' stored procedure that inserts records
Dim myCommand As New SqlCommand(strSQL, myConnection)
myCommand.CommandType = CommandType.StoredProcedure
myCommand.Parameters.Add("@.salutation", SqlDbType.NVarChar)
myCommand.Parameters.Add("@.firstname", SqlDbType.NVarChar)
myCommand.Parameters.Add("@.lastname", SqlDbType.NVarChar)
myCommand.Parameters.Add("@.company", SqlDbType.NVarChar)

Dim i, j As Integer
Response.Write(Date.Now() & "<br>")
For i = 0 To ds2.Tables("Members").Rows.Count() - 1
myCommand.Parameters("@.salutation").Value = ds2.Tables("Members").Rows(i).Item("sal")
myCommand.Parameters("@.firstname").Value = ds2.Tables("Members").Rows(i).Item("firstname")
myCommand.Parameters("@.lastname").Value = ds2.Tables("Members").Rows(i).Item("lastname")
myCommand.Parameters("@.company").Value = ds2.Tables("Members").Rows(i).Item("company")
j = myCommand.ExecuteNonQuery()
If (j > 0) Then
Response.Write("Record Inserted - " & i + 1 & "<br>")
End If
Next
Response.Write(Date.Now() & "<br>")
myConnection.Close()
End Sub

Please reply soon.
Thank You.can you show us the readExcelObject class?

i got this unspecified exception when trying to connect to an excel file:

System.Data.OleDb.OleDbException: Unspecified error at System.Data.OleDb.OleDbConnectionInternal.InitializeProvider() at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnection conn, OleDbConnectionString constr) at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object providerInfo, DbConnectionBase owningObject) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnectionBase owningConnection, DbConnectionPoolGroup poolGroup) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnectionBase owningConnection) at System.Data.ProviderBase.DbConnectionClosed.Open(DbConnectionBase outerConnection) at System.Data.ProviderBase.DbConnectionBase.Open() at ASP.excel1_aspx.LoadExcelIntoDS() in c:\excel_dev\excel1.aspx:line 25

<%@. Page language="c#" %>
<%@. Import Namespace="System.Runtime"%>
<%@. Import Namespace="System"%>
<%@. Import Namespace="System.Data"%>
<%@. Import Namespace="System.Data.OleDb"%
<script runat=server>
void Page_Load(Object src, EventArgs e)
{
LoadExcelIntoDS();
}
void LoadExcelIntoDS()
{
OleDbConnection MyConnection = new OleDbConnection();
try
{
String conn = "Provider=Microsoft.Jet.OLEDB.4.0;";
conn += @."Data Source=C:\excel_dev\demo_banc.xls;";
conn += "Extended Properties=Excel 8.0;";
MyConnection = new OleDbConnection(conn);
MyConnection.Open();
}
catch(ArgumentException ae)
{
Response.Write(ae.ToString() + "<BR>");
}
catch(OleDbException e)
{
String err_msg = "";
for (int i = 0; i < e.Errors.Count; i++)
err_msg += "------<br>Index #" + i + "<br>Message: " + e.Errors[i].Message + "<br>NativeError: " + e.Errors[i].NativeError + "<br>Source: " + e.Errors[i].Source + "<br>SQLState: " + e.Errors[i].SQLState + "<br>";
Response.Write(err_msg + "<br>");
Response.Write(e.ToString() + "<BR>");
}
catch(Exception e)
{
Response.Write(e.ToString() + "<br>");
}
finally
{
MyConnection.Close();
}
}|||solved.

pls refer to:

http://www.asp.net/Forums/ShowPost.aspx?tabindex=1&PostID=314899

import CSV data to SQL Server database

Hello everyone,

I need help with a situation that iam facing right now.

Here is the problem:

I have a tab limited or comma delimited CSV file. I want to read the contents of that CSV file and import it in one of the tables in the database.

id firstname lastname
-- ---- ----
"1" "John" "Smith"
"2" "Louis" "Garcia"

I assume that the columns in the CSV files and the table in the database match the datatype.

What would be a good approach to do it ? If anyone have a code that does the job, please post it.

Thank You.Do you have Enterprise Manager? If so goto the "import data" menu.|||Or Use a Bulk Insert statement

BULK INSERT dbo.TableName FROM '\\yourMachine\filename.csv'
WITH (DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
FIRSTROW = 2 )|||Hi pkr,

I want to achieve this programatically using ASP.Net and VB.Net .....

Can you tell me any good resources on it please ?

Thank You.|||Hi John,

Do you have a code snippet in ASP.Net/VB.Net that shows how to use the bulk statement ??

Please help.

Thank You.|||Its just like any other SQL statement|||The Bulk insert statement is best kept to the confines of a Stored procedure and therefore you would make a call to the stored proc from your Data Access Layer within your asp.net application.

If your going to use a DTS package you can call this directly from the DAL and manage each step programmatically; if this is the path you want to take I have posted some c# code in the following post:

http://www.asp.net/Forums/ShowPost.aspx?tabindex=1&PostID=697176

But i don't recommend it.

All the best, John|||thanks a lot John. it really helped me .