Post reply

Excel and SQL

  • May 30, 2006 at 4:38 am

    #172716

    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!

  • May 30, 2006 at 5:12 am

    #640640

    Alex

    Will this work for you?

    (1) Save your spreadsheet as an xls file.

    (2) Create a named range in the spreadhseet to cover all the data.

    (3) In Enterprise Manager, create a Linked Server, using Microsoft Jet 4.0 OLE DB Provider as the data provider.  Use the name of the named range in the connection string.

    (4) Run your update queries against the linked server.

    You should be able to find more information on doing the above in Books Online.

    John

  • May 30, 2006 at 5:33 am

    #640644

    John,

    no, it will not work for me I have to automatize this operation, so I could perform this operation as a job in sql server. So, saving as xls and creating a named range simply won't do, because I never know how much data there will be in the csv file.

  • May 30, 2006 at 6:24 am

    #640655

    Alex

    In that case, you can write some VB in another Excel workbook that saves your file as an xls and creates the name, then schedule that from SQL Server or Windows.  Or you may take the view that it's easier to go back to your original idea of the temp table!

    John

  • May 30, 2006 at 9:12 am

    #640716

    Alex,

    What i feel here is using temp tables in DTS is the best option as it will be faster as well as automate solution which is your requirement here. You can save the dts and schedule it as a job to run at the regular interval which will be better & stable solution.

     

    ------------
    Prakash Sawant

    http://psawant.blogspot.com

  • May 31, 2006 at 1:39 am

    #640839

    Well, Prakash, after pondering what to do, I also came to the conclusion that the best way is to use the temp table, only with a slight change. I used a bulk insert statement, instead of DTS. But it does the job

    Thanks a lot!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply