VB.NET - Excel Import

 I am trying to import excel file to SQL Server 2000 database using vb.net code. I already wrote the code!
My question is: Does this excel file can be loaded from local computer or it should be always on the server (ex: application server where the application resides)... this is WINDOWS APPS.
Thank you.

I would recommend having the file on a server something like \\SERVERNAME\SHAREDFOLDER\EXCELFILE

This way you can gurantee the successful running of the code; in likelihood the local machine either logged off or shutdown...

Hope that answers.

If this is an excel file that a user needs to be modifying or creating you have two options:
1- Have it reside on the user's PC and have your program do a filecopy on startup to the local folder on the server where the program runs and process that copy (this will prevent connection loss or file locking or even slowdowns).

2- Have it reside on the server and create a shortcut on the user's desktop. If there is a network connection loss user may lose any changes, it may be slower (I don't know your network setup so I can't say) and user would need permissions to the server folder/file.

It all depends on your network and security setup.

Thank you guys! I belive the excel file should be on the server too! for instance user click on the upload button and load local excel file, then when click on the save button, I can save that excel file on the server and interally locate that excel file from server and start process of loading in to SQL server database table.

Does any one know, in windows application how can I store local excel file on the specified server location. As we do in Webapps like SERVER.MAPPATH.....

Thank you again.

The equivalent in a windows app would be:
System.Reflection.Assembly.GetExecutingAssembly.Location


You also have Application.StartupPath. It will return the folder the executable is in.

Thank you! When I spoke to my BA Team they said, the local user who is uploading the excel file does not have access to store the excel file on the server. they suggested to use : Dataset and instert into database.
ie, Load local excel file, read and store into dataset, then upload in to Remote SQL Server 2000 table... Is there any way to do this? Thank you!

If your program already works by processing the sheet why re-invent the wheel. Isn't there a shared folder on your network? Then locate the sheet there. The user can access it and the server and your program can access it. Can they create a shared folder and give appropriate permissions?
Another option if the sheet structure never changes could be to create a dts package.

And as far as dataset goes, how would it work? The user would enter the data on a sheet and you would copy it to a local dataset or the user would enter it into a local dataset??? And then what? Does the user have direct access to the sql ds? Wouldn't you still need permissions if you went the dataset route?

One way is to use the OLEDB dataprovider to load a datatable and then use the datatable to update the SQL Server database.  The ffollowing code assumes the spreadsheet has column headers that match the column names in the SQL Server table.

'get .xlsx file path

Dim ofd As New OpenFileDialog

With ofd

.InitialDirectory = "C:|"

.Filter = "Excel|.xlsx"

.ShowDialog()

End With

'build connection string

Dim oledbconstr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ofd.FileName & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

Dim oledbConn As New OleDbConnection(oledbconstr)

'You can use column names or "*" for all columns; Sheet name followed by "$"

Dim strSql As String = "SELECT [Column Name One], [Column Name Two] FROM [Sheet One$]"

Dim oledbcmd As New OleDbCommand(strSql, oledbConn)

Dim oledbda As New OleDbDataAdapter(oledbcmd)

'set AcceptChangesDuringFill to False so rows will have a row state of added

oledbda.AcceptChangesDuringFill = False

Dim dt As New DataTable

oledbda.Fill(dt)

Dim sqlConstr = "your SQL Server connection string"

Dim strSQLA As String = "Select * From yourTable"

Dim sqlconn As New SqlConnection(sqlConstr)

Dim cmd As New SqlCommand(strSQLA, sqlconn)

Dim sqlDa As New SqlDataAdapter(cmd)

Dim cb As New SqlCommandBuilder(sqlDa)

sqlDa.Update(dt)

If the speadsheet does not have column headings or the column names do not match the column names in the table, you will need to load the schema to the destination table, and loop through the datatable and add rows to the destination datatable.

See this link for more info on the OleDb connection string extended properties:
http://msdn.microsoft.com/en-us/library/Aa140022