Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004

    Unanswered: Joining Excel Database with MSDE Database Table, & Import

    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.

  2. #2
    Join Date
    Jun 2003
    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:

    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).
    Last edited by Bullschmidt; 02-19-04 at 00:40.
    J. Paul Schmidt, Freelance Web and Database Developer
    Access Database Sample, Web Database Sample, ASP Design Tips

  3. #3
    Join Date
    Feb 2004

    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):

    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):


    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',
    'C:\Documents and Settings\POS\Desktop\inventory.xls',
    '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))

    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
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts