Looking for the basic procedure to join an Excel spreadsheet with a table from a database running on MSDE, and then updating the MSDE table with select columns of info from the Excel spreadsheet._ I believe the first set of syntax will join the file with MSDE table, and the second set of syntax will run the import/update._ If you can assist, please advise._ Thank you in advance.

You can connect to a csv file (which can be opened in Excel and which an Excel file can be converted into) in good form just as you can to a regular database.

The .csv file needs to be uploaded to the server.

And you can have two recordsets open at the same time.

So I'd suggest going through this recordset one row at a time and within this loop add a new record to the "real" database's recordset.

And for help connecting to a text file using the Jet OLE DB provider:
http://www.able-consulting.com/MDAC/...crosoftJetText

And based on the above link realize that the actual filename does NOT go in the connection string - rather it goes in the SQL statement (definitely a little tricky).

Paul,

Thank you for your reply. Here is a brief description of my actual data:

Excel File name = inventory.xls
File location = C:\Documents and Settings\POS\Desktop\inventory.xls
Excel current worksheet sample (both columns are 16 character strings):

ITEMNO LOG_SOURCE
A1 13110 B1 White 3 Ring
A2 13111 B2 White 3 Ring
A3 13112 B3 09/09/03
A4 13113 B4 09/09/03
A5 13114 B5 09/09/03

SQL Table Name = ITEMS
SQL current table sample (both columns are 16 character strings):

ITEMNO LOG_SOURCE
13110
13111
13112
13113
13114

My primary objective is to update the LOG_SOURCE column of the SQL table with the values from the Excel worksheet (using ITEMNO as the link).

I have to apologize in advance for my limited SQL experience, but here is code that I've collected thus far to perform the link:

EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'C:\Documents and Settings\POS\Desktop\inventory.xls',
NULL,
'Excel 5.0'

(not sure what I should change Excel version name to if I'm using Microsoft Office Excel 2003)

EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false'

EXEC sp_tables_ex ExcelSource
EXEC sp_columns_ex ExcelSource

SELECT *
FROM ExcelSource...Sheet1$

CREATE TABLE test_excel
(id int,
name varchar(255))
GO

INSERT INTO test_excel
SELECT *
FROM ExcelSource...Sheet1$

SELECT *
FROM test_excel

/* Now define two ranges in Excel on the 2nd sheet as tables */
/* Select the range, Insert->Name->Define */
/* Note: sp_tables_ex does not recognize the defined tables */
/* We can still refer to the tables explicitly */

EXEC sp_tables_ex ExcelSource
EXEC sp_columns_ex ExcelSource

SELECT *
FROM ExcelSource...Table1

SELECT *
FROM ExcelSource...Table2

(I wonder if I could avoid defining table ranges to save a step or two and simply use column ranges -in other words, what commands could we eliminate by using Sheet1 A1:A5 & Sheet1 B1:B5)

Here is the code I've collected thus far to perform the update:

update Items set log_source = t1.log_source

update items
set
items.LOG_SOURCE = t1.LOG_SOURCE
from t1
inner join Items
on items.ItemNo = t1.ItemNo

I'd like to consolidate this code or simplify this process, if possible. It has also been suggested that I might use DTS for this type of procedure - does DTS come as part of MSDE & if so, how do I access it?

Thank you again for your time and expertise.