Excel and SQL

Hi!

I have an CSV file that looks like this:

username1 password1

username2 password2

username3 password3

Usernames are in the A column, and passwords are in B column. Next, I have a table Users with columns Username and Password. My task is this: I have to go through the CSV file and update the password for the specified username. The file is always in the same location. I first wanted to do a DTS from the csv to a temporary table, and then do a regular update, but if there is a way to do this directly from csv to update, without the temp table, that would be great.

Thanks!

Hi,

you can either use a linked server or the openrowset method for update the excel data:

UPDATE
OPENROWSET('Microsoft.JET.OLEDB.4.0',
'Excel 8.0;Database=C:\SomeXLSFile.xls,
'SELECT * FROM [SomeSheet$]')
SET SomeColumn = 1

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de