[RESOLVED] [2005] Import situation

[j0shua]

Hi, i have an excel file, where there is a specific template which contains the data of products of a certain company.

I am new and using visual basic 2005, as well as microsoft sql server 2000. What i wanted to do is i would like to import the excel items into the database through visual basic 2005. i would like to know the process and how to do the importing of excel items into the system and then to the database. Thank you.

i would appreciate any help please.

[jmcilhinney]

First up, you should be aware that SQL Server will import data directly from Excel files.

If you really do want to a VB app then it's simple ADO.NET. Follow the Data Access link in my signature for some code examples. In your case you'll have a DataTable for each worksheet in the Excel file. You'll use an OleDbDataAdapter to populate the DataTable. You'll then use a DataAdapter to save the contents of the DataTable to SQL Server. You might want to use a SqlDataAdapter or you could use the same OleDbDataAdapter you used to get the data. Either way, the SelectCommand of the first adapter and the InsertCommand of the second adapter will use different connections for the different databases.

Another point to note is that the first adapter must have its AcceptChangesDuringFill property set to False, or else the data will not insert.

[jmcilhinney]

E.g.

With one DataAdapter:

Dim excelConnection As New OleDbConnection("connection string here")
Dim adapter As New OleDbDataAdapter("SELECT statement here", excelConnection)
 
adapter.AcceptChangesDuringFill = False
 
Dim table As New DataTable
 
'Retrieve the data.
adapter.Fill(table)
 
Dim sqlConnection As New OleDbConnection("connection string here")
Dim insert As New OleDbCommand("INSERT statement here", sqlConnection)
 
'Add parameters to insert here.
 
adapter.InsertCommand = insert
 
'Save the data.
adapter.Update(table)

With two:

Dim excelConnection As New OleDbConnection("connection string here")
Dim excelAdapter As New OleDbDataAdapter("SELECT statement here", excelConnection)
 
excelAdapter.AcceptChangesDuringFill = False
 
Dim table As New DataTable
 
'Retrieve the data.
excelAdapter.Fill(table)
 
Dim sqlConnection As New SqlConnection("connection string here")
Dim sqlAdapter As New SqlDataAdapter
Dim insert As New SqlCommand("INSERT statement here", sqlConnection)
 
'Add parameters to insert here.
 
sqlAdapter.InsertCommand = insert
 
'Save the data.
sqlAdapter.Update(table)

[j0shua]

Thanks you for this great information. i will study what you've mentioned and work this out! Now i have a clear direction of what i will do! thank you!!

[j0shua]

one more question, does the SELECT statement used in the Excel the same with the SELECT statement used in SQL? Thank you.

[jmcilhinney]

It IS SQL code, but it must be SQL syntax that is supported by the ADO.NET provider and data source that you're using. If you do the reading that has already been suggested then you'll have your answer. That Data Access Examples thread of mine provides a link to a site that provides the connection string format for various data sources, including Excel. That site also provides the precise SQL syntax for querying Excel.

One point to note is that, if you don't have column headers in your Excel sheet then your column names default to F1, F2, F3, etc. I'm fairly sure that's right, although it might be C1, C2, C3, etc. A quick test will tell you, although, if you do have column headers, it doesn't matter anyway.

[j0shua]

Thank you for the info u gave, i was able to connect to an excel file, and able to view it in a datagridview! Now my next task is to import all the data from the excel file and append the table that will store it.

The situation is like this; The excel file list the products of a company, that contains its item code, description, category, subcategory, price, etc. These new format of items are to be imported to an existing table in the SQL server 2000 database. Somebody told me to use BULK import of excel as well as VBNET. What do you think about it? Is there a better and more efficient way of approaching this kind of problem?