Professional Web Applications Themes

Extending client to access Excel files (most data still in SQL Server) - Microsoft SQL / MS SQL Server

We are building a general query tool for accessing data in our company. The primary data source we access is SQL Server running on a remote host, using OLE DB and C++. However, our users also want to be able to use our tool to yze data in Excel or delimited text files. We are considering two options: * using the Jet 4.0 driver to directly access the files and to execute SQL against their content. * embedding MSDE 2000 in our application and loading (temporarily) the data into the instance and purging it when the application exits. Any thoughts ...

  1. #1

    Default Extending client to access Excel files (most data still in SQL Server)

    We are building a general query tool for accessing data in our
    company. The primary data source we access is SQL Server running on a
    remote host, using OLE DB and C++. However, our users also want to be
    able to use our tool to yze data in Excel or delimited text files.
    We are considering two options:

    * using the Jet 4.0 driver to directly access the files and to
    execute SQL against their content.
    * embedding MSDE 2000 in our application and loading (temporarily)
    the data into the instance and purging it when the application exits.

    Any thoughts on either approach?

    thanks

    Chris Stolte
    Christopher Stolte Guest

  2. #2

    Default Re: Extending client to access Excel files (most data still in SQL Server)

    Can't you create ETL processes to move the Excel and text file data into
    your database? It's very difficult to get meaningful results from querying
    unstructured text and spreadsheet data because of the lack of validation and
    metadata. Also, if this data is useful to a user in a report then it's
    important enough to go in a shared, managed database. You don't want your
    organisation's valuable data hidden away in spreadsheets and text files if
    you can avoid it.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --

    "Christopher Stolte" <cstoltestanford.edu> wrote in message
    news:ee6ef4bf.0307121418.786916cfposting.google.c om...
    > We are building a general query tool for accessing data in our
    > company. The primary data source we access is SQL Server running on a
    > remote host, using OLE DB and C++. However, our users also want to be
    > able to use our tool to yze data in Excel or delimited text files.
    > We are considering two options:
    >
    > * using the Jet 4.0 driver to directly access the files and to
    > execute SQL against their content.
    > * embedding MSDE 2000 in our application and loading (temporarily)
    > the data into the instance and purging it when the application exits.
    >
    > Any thoughts on either approach?
    >
    > thanks
    >
    > Chris Stolte

    David Portas Guest

  3. #3

    Default Re: Extending client to access Excel files (most data still in SQL Server)

    David,

    Thanks for your answer. Unfortunately, this data can't be imported
    into the main server. The data is structures as a named range in the
    Excel sheet and will be visually joined with the query results from
    SQL Server. In many cases, the data is the output of a query against a
    legacy system. Given our constraints, is there any advice on the best
    way to connect to Excel? We are concerned about Jet because it has
    been 'deprecated' from MDAC.

    thanks
    Chris

    "David Portas" <org> wrote in message news:<phx.gbl>... [/ref]
    Christopher Guest

  4. #4

    Default Re: Extending client to access Excel files (most data still in SQL Server)

    Although Jet 4.0 has been dropped from MDAC I expect it will be supported
    for some time to come. It's distributed with Office (2000) anyway so
    availability shouldn't be a problem. I guess Office XP has a replacement
    OLEDB driver for Excel.
     

    Why not create views in SQL (via OLEDB) or some ETL process to pull the data
    direct from the legacy system? Puttting the queries at the client-end is
    inevitably going to be sub-optimal by comparison.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

Similar Threads

  1. Replies: 2
    Last Post: March 7th, 12:43 PM
  2. Move data from Excel to SQL Server via ASP
    By midwesthills in forum ASP Database
    Replies: 6
    Last Post: August 3rd, 08:18 PM
  3. Extract Data from Excel-files
    By Benjamin Walkenhorst in forum PERL Beginners
    Replies: 4
    Last Post: February 16th, 08:02 PM
  4. Replies: 2
    Last Post: January 15th, 03:43 PM
  5. XP Client can't access Win2K-files
    By Fredrik Ljungbeck in forum Windows Setup, Administration & Security
    Replies: 1
    Last Post: July 21st, 03:16 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139