Page 1 of 2 12 LastLast
Results 1 to 15 of 25

Thread: LT2 with Excel

  1. #1

    LT2 with Excel

    Hey guys, I'm trying to use LT2 with an Excel spreadsheet to pull election totals into LT2 for an election return program on Thursday (5).

    I've got my XLS file and my XML code, but when I go to add the KEY in LT2, it doesn't show them in the DROP down.

    Here is my XML...anyone have any ideas or know where to point me? The manual is a little vague when it comes to using Datalink and the Database Linker.


    <!--
    Example connection string and query for DataLink using MySQL
    This produces the keys %book.1%, %book.2%... etc
    ?>



    <sql_databases>
    <database connection_string = "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0ata Source=C:\Election2010.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1">
    <query command = "SELECT candidate FROM [Sheet1$] WHERE race = 'Roane County Mayor';" key = "candidate" timeout = "10000" multiple_keys = "true" />
    <query command = "SELECT votes FROM [Sheet1$] WHERE race = 'Roane County Mayor';" key = "votes" timeout = "10000" multiple_keys = "true" />
    <query command = "SELECT percentage FROM [Sheet1$] WHERE race = 'Roane County Mayor';" key = "percentage" timeout = "10000" multiple_keys = "true" />
    </database>
    </sql_databases>


    ANYONE know which way to push me???

    Thanks so much,

    J. Brad Jones

  2. #2

    Revision

    Here is a revised XML file...but STILL not luck. Any ideas folks?


    <!--
    Example connection string and query for DataLink using MySQL
    This produces the keys %book.1%, %book.2%... etc
    ?>



    <sql_databases>
    <database connection_string = "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0ata Source=c:\Election2010.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1">
    <query command = "SELECT candidate FROM [Sheet1$] WHERE race = 'Roane County Mayor';" key = "candidate" timeout = "10000" multiple_keys = "true" />
    <query command = "SELECT votes FROM [Sheet1$] WHERE race = 'Roane County Mayor';" key = "votes" timeout = "10000" multiple_keys = "true" />
    <query command = "SELECT percentage FROM [Sheet1$] WHERE race = 'Roane County Mayor';" key = "percentage" timeout = "10000" multiple_keys = "true" />
    </database>
    </sql_databases>

    any help would be appreciated.

    thanks,

    Brad

  3. #3
    LiveSet Making Machine joseburgos's Avatar
    Join Date
    Feb 2003
    Location
    NYC
    Posts
    3,333
    I have not upgraded to LT2 yet but will be very soon (next week). So for now, I am replying on your thread just to garner any information on your request as I too would like to use Excel with LT2 in some shape or form.
    Jose Burgos
    NewTek Certified Trainer
    NewTek Certified on all TriCaster's
    NewTek Training & Certification Testing
    www.burgosfx.com
    FaceBook
    Twitter @NYTriCaster

  4. #4
    Registered User
    Join Date
    Feb 2003
    Location
    Germany
    Posts
    270
    I have also been unable to get LT2 to accept any variation of a XML file, Brad. After calling Newtek, the statement was that this is a known problem. I was assured that I would receive feedback, but that has not been received. The only workaround has been the TXT file. Bummer.

  5. #5
    'the write stuff' SBowie's Avatar
    Join Date
    Feb 2003
    Location
    The stars at night are big and bright
    Posts
    19,395
    It's ages (literally) since I did database work, and even then I did not need to use a SQL server. For whatever little it is worth, though and in case this isn't something everyone else on the planet already knows:

    I gather that Access and Excel data are not automatically addressable as SQL servers, but must be 'published' (my term) as a linked server first - using SQL Server Express, or one of its siblings. Presumably, until this is done, DataLink cannot 'see' the data for the SQL query to return anything.

    I'd love nothing better than to drop everything and spend a week figuring this all out an testing it (or better yet having a db guru explain it all to me with examples in about an hour), but I just can't. I did, however, find an abundance of reference material. These pages seem to offer an excellent starting point and lead to extensive resources:

    Linked Servers on MS SQL Part 1

    (As I said, if this was obvious to everyone else already, my apologies for the noise.)
    --
    Regards, Steve
    Forum Moderator
    ("You've got to ask yourself one question ... 'Do I feel lucky?' Well, do ya, spammer?")

  6. #6
    Registered User
    Join Date
    Feb 2003
    Location
    Germany
    Posts
    270
    Steve, the problem is that the xml sample file that is delivered with LT2 will not work. It is not that we can't get the database to deliver a proper XML file, it is that LT2 has not been able to access any of the files. Newtek has not yet given an answer, nor provided a working solution on either a website or via tech support.

    More modules are required to be installed in order to create the bridge between the MySQL and LiveText, but Newtek is yet to give us a hint as to what that might be. Headquarters stated that they are aware of the problem, but that is as far as we have advanced.

  7. #7
    'the write stuff' SBowie's Avatar
    Join Date
    Feb 2003
    Location
    The stars at night are big and bright
    Posts
    19,395
    Quote Originally Posted by Keith Gandy View Post
    Steve, the problem is that the xml sample file that is delivered with LT2 will not work. It is not that we can't get the database to deliver a proper XML file, it is that LT2 has not been able to access any of the files. Newtek has not yet given an answer, nor provided a working solution on either a website or via tech support.
    So you're saying you have taken whatever steps are required for your Excel table to be distributed and seen as a SQL server, and that - once properly addressed and logged into - it can successfully be queried by other external SQL applications?

    My point is, that as far as I can see, neither Excel nor Access are natively SQL databases nor act as SQL servers, which (again, afaics) is why M$ provides a number of tools that support that functionality. In other words, I do not believe you can expect the sample xml file to have any success unless you are addressing a SQL server, which neither Access or Excel are on their own.

    Edit: In a related vein, has anyone tested DataLink keys using an actual SQL database/server? (There are a number of public domain ones, which I really wish I had time to test).
    Last edited by SBowie; 08-26-2010 at 07:58 AM.
    --
    Regards, Steve
    Forum Moderator
    ("You've got to ask yourself one question ... 'Do I feel lucky?' Well, do ya, spammer?")

  8. #8
    'the write stuff' SBowie's Avatar
    Join Date
    Feb 2003
    Location
    The stars at night are big and bright
    Posts
    19,395
    A little further on this - docs for M$ SQL server products often refer to "sp_addlinkedserver", which appears to be a dll distributed in those packages. M$ states the following in this context:

    Creates a linked server. A linked server allows for access to distributed, heterogeneous queries against OLE DB data sources. After a linked server is created by using sp_addlinkedserver, distributed queries can be run against this server. If the linked server is defined as an instance of SQL Server, remote stored procedures can be executed.
    It is my assumption that absent this "linked server", one cannot run 'distributed queries' against Excel or Access data sources (although I think there may be some other third-party utilities with similar capability).
    --
    Regards, Steve
    Forum Moderator
    ("You've got to ask yourself one question ... 'Do I feel lucky?' Well, do ya, spammer?")

  9. #9
    Code Jockey
    Join Date
    Oct 2009
    Location
    Blue Springs, MO
    Posts
    48
    sp_addlinkedserver is a stored proc (built in function), not a dll.

    You can also use management studio and navigate to Server Objects->Linked Servers in teh object explorer, right click on Linked Servers and select add linked server. It is worth noting that unless you are linking to another SQL server you will also need to create an ODBC System DSN pointing to your XML, excel file. Interesting idea, I've never used a linked server to connect to anything other than another SQL server.

    I don't have LT2 so I can't comment directly on it, but I can see that the above XML would have problems as you are populating a <sql_databases> element but not giving it a SQL database. Likely the code they are using is explicitly trying to open a SQL database using SQL drivers.

    Also, you are specifying the JET engine to connect to an excel file, I'm not sure that is the right provider (might be, been a long time since directly manipulating access/excel).

    Also, your xml itself is malformed and invalid, you have a quote in the middle of another string (connection_string attribute), which isn't allowed (plus the quote wasn't terminated). Try using ticks (') for the enclosing string, this allows your value to contain quotes.

    maybe this:

    <sql_databases>
    <database connection_string = 'OLEDB;Provider=Microsoft.Jet.OLEDB.4.0ata Source=c:\Election2010.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"'>
    <query command = "SELECT candidate FROM [Sheet1$] WHERE race = 'Roane County Mayor';" key = "candidate" timeout = "10000" multiple_keys = "true" />
    <query command = "SELECT votes FROM [Sheet1$] WHERE race = 'Roane County Mayor';" key = "votes" timeout = "10000" multiple_keys = "true" />
    <query command = "SELECT percentage FROM [Sheet1$] WHERE race = 'Roane County Mayor';" key = "percentage" timeout = "10000" multiple_keys = "true" />
    </database>
    </sql_databases>
    Duane Newman
    FrogPond Technologies, Inc.
    www.frogpondtech.com

  10. #10
    Code Jockey
    Join Date
    Oct 2009
    Location
    Blue Springs, MO
    Posts
    48
    Also, you might use a tool like Query Express to test you connection string and your queries: http://www.albahari.com/queryexpress.aspx

    It is free and requires no install. Just select the OLE-DB tab when it starts and enter your connection string, then you should be able to run each of your queries and make sure they are correct.
    Duane Newman
    FrogPond Technologies, Inc.
    www.frogpondtech.com

  11. #11
    'the write stuff' SBowie's Avatar
    Join Date
    Feb 2003
    Location
    The stars at night are big and bright
    Posts
    19,395
    Quote Originally Posted by dnewman31 View Post
    sp_addlinkedserver is a stored proc (built in function), not a dll.
    OK, thanks. Something I read this am referred to it as a dll, while another place it was referred to as a service - but it may well be a process embedded in some dll associated with the various add-on SQL packages. I am still not of the impression that it is natively part of either Excel or Access, else one would not find so many pages discussing different ways to use it (whether by some SWL management front-end or using an EXEC statement) to create a linked server.

    Quote Originally Posted by dnewman31 View Post
    Likely the code they are using is explicitly trying to open a SQL database using SQL drivers.
    That is also my impression.

    Quote Originally Posted by dnewman31 View Post
    Also, you are specifying the JET engine to connect to an excel file, I'm not sure that is the right provider (might be, been a long time since directly manipulating access/excel).
    I think there have been several, and some versions may support more than one. I did see one article in passing this am that made specific reference to Excel 2007 as requiring a specific server software as distinct from its predecessors.

    This is definitely not something for the faint of heart. Again, I wonder if it would be much more transparent if one was using an actual SQL dbase and server, rather than trying to tap data from Excel.
    --
    Regards, Steve
    Forum Moderator
    ("You've got to ask yourself one question ... 'Do I feel lucky?' Well, do ya, spammer?")

  12. #12
    Code Jockey
    Join Date
    Oct 2009
    Location
    Blue Springs, MO
    Posts
    48
    Quote Originally Posted by SBowie View Post
    This is definitely not something for the faint of heart. Again, I wonder if it would be much more transparent if one was using an actual SQL dbase and server, rather than trying to tap data from Excel.
    Agreed. It would probably be a much better experience if it was a sourced from a sql database. Maybe a simple front end app for whoever to update data fields.

    In fact, you should even be able to use excel itself to push data to the sql server from inside the spreadsheet.. That might be the best way (short of having a custom app to update the database for you, something that would be ridiculously easy for that level of need).
    Duane Newman
    FrogPond Technologies, Inc.
    www.frogpondtech.com

  13. #13
    'the write stuff' SBowie's Avatar
    Join Date
    Feb 2003
    Location
    The stars at night are big and bright
    Posts
    19,395
    Quote Originally Posted by dnewman31 View Post
    That might be the best way (short of having a custom app to update the database for you, something that would be ridiculously easy for that level of need).
    ... says you.
    --
    Regards, Steve
    Forum Moderator
    ("You've got to ask yourself one question ... 'Do I feel lucky?' Well, do ya, spammer?")

  14. #14
    Code Jockey
    Join Date
    Oct 2009
    Location
    Blue Springs, MO
    Posts
    48
    heck, now that I think about it, if you already have access you could link to the SQL tables, and use access as the front end to do edits..

    [EDIT]if I had a license to LT2 I could probably even do some community how to videos on it... (hint hint) [/EDIT]
    Last edited by dnewman31; 08-26-2010 at 10:52 AM.
    Duane Newman
    FrogPond Technologies, Inc.
    www.frogpondtech.com

  15. #15
    Registered User ZachSchuster's Avatar
    Join Date
    May 2007
    Location
    St. Paul, MN
    Posts
    783
    During the LT2 beta test I suggest Datalink be able to access Excel files directly. The key/value would be something like:

    C4 = c:\spreadsheet.xls:C4

    where C4 is a cell designation.

    I also suggested a means by which you could do calculations in LT2, something like this typed in to a text line on the CG page:

    SUM=((C4+C3)-(B12+D9))

    I never heard back on the suggestion, though.

Page 1 of 2 12 LastLast

Tags for this Thread

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
  •