PDA

View Full Version : LT2 with Excel



cameraman91569
08-03-2010, 08:35 PM
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 (8/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.0;Data 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 :help:

cameraman91569
08-03-2010, 10:01 PM
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.0;Data 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

joseburgos
08-04-2010, 09:19 AM
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.

Keith Gandy
08-25-2010, 10:26 PM
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.

SBowie
08-26-2010, 06:21 AM
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 (http://www.databasejournal.com/features/mssql/article.php/3085211/Linked-Servers-on-MS-SQL-Part-1.htm)

(As I said, if this was obvious to everyone else already, my apologies for the noise.)

Keith Gandy
08-26-2010, 07:36 AM
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.

SBowie
08-26-2010, 07:49 AM
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).

SBowie
08-26-2010, 08:26 AM
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).

dnewman31
08-26-2010, 08:44 AM
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>

dnewman31
08-26-2010, 08:50 AM
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.

SBowie
08-26-2010, 08:56 AM
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.


Likely the code they are using is explicitly trying to open a SQL database using SQL drivers.That is also my impression.


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.

dnewman31
08-26-2010, 10:17 AM
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).

SBowie
08-26-2010, 10:41 AM
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. :p

dnewman31
08-26-2010, 10:50 AM
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..

if I had a license to LT2 I could probably even do some community how to videos on it... (hint hint) :)

ZachSchuster
08-26-2010, 11:07 AM
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.

SBowie
08-26-2010, 11:16 AM
if I had a license to LT2 I could probably even do some community how to videos on it... (hint hint) :)Or .... I have Access, and LT2 - so if you come over to my place on a Saturday ... (I'll buy the beer). ;)

SBowie
08-26-2010, 11:17 AM
I never heard back on the suggestion, though.Mostly, I think, because it's a lot easier to type that line that to implement direct OLE support into LT. ;)

dnewman31
08-26-2010, 11:57 AM
During the LT2 beta test I suggest Datalink be able to access Excel files directly.

I agree that this would be good, also support for direct xml access with xpath would be good too. Does is support pulling rss feeds?


I also suggested a means by which you could do calculations in LT2

It might be handy but all this could be accomplished either with stored procs/sql on the sql server or a sheet in the xls that has calculations in it.

Good separation of concerns would make the page simply consume prepared data, that way when you make a quick title with a subset of the information you don't have to reenter (and risk a typo in) the formula. Instead the data would be prepped elsewhere (SQL) always be correct for the screen. Putting the logic in the display may seem great, but trust me, eventually it will bite you.


Back on the subject of SQL and external data:
Another thing worth noting with consuming other data through sql is that you can do an Import and save the SSIS package so that the next time all you have to do is run the package.. Then you could set it up on a job to run at whatever interval you need (not sure if the job support is in sql express) to keep the database current with the file source.

Keith Gandy
08-26-2010, 12:34 PM
I installed Apache, PHP and MySQL locally that will dynamically retrieve data from the internet, update the database and even produce a XML file. When putting the keys into LT2 and Datalink, nothing is referenced.

In my particular case, I am not using Excel nor Access. Building a database would allow things to update dynamically, which is the point in our effort - a Twitter search function that can then be imported to LT2 via Datalink.

I assume that an ODBC System must be installed, but that is where my breakdown occurs. I have never been able to get Datalink to speak to any xml file.

SBowie
08-26-2010, 01:46 PM
I have never been able to get Datalink to speak to any xml file.I'm not aware that it should, apart from reading it's own xml definition file (in the Database Input folder). My understanding was that it uses the values in that file to directly query the SQL database - i.e., not that it pulls values from an xml file constructed by the database.

dnewman31
08-26-2010, 07:55 PM
Steve, can you find out if LT2 understand MySQL connections?

While the query languages are the same (in as much they adhere to the SQL 98 standard) establishing connections is entirely different than MS SQL Server.

I'm just shooting in the dark with suggestions here as I just have LT.

Keith Gandy
08-26-2010, 08:15 PM
Thanks for noting the difference, Steve. I will have to make sure that the proper form of query is placed in the xml subdirectory and then give that a shot. It has been a number of weeks since I worked on the project, so I'll check into that.

ZachSchuster
08-26-2010, 08:36 PM
Mostly, I think, because it's a lot easier to type that line that to implement direct OLE support into LT. ;)

Pash. Details, details. :D

Yeah, I'm sure it's pretty complicated! :eek:

SBowie
08-27-2010, 03:59 AM
Steve, can you find out if LT2 understand MySQL connections? I'll continue trying to find out more. In the meantime, can you direct me to any online infor on MySQL connections?

dnewman31
09-02-2010, 11:44 AM
In the meantime, can you direct me to any online infor on MySQL connections?

I'm afraid not, I'm not that familiar with MySQL (generally use MS SQL). I just know that they don't talk the same protocol (network), just the same/similar query language. I think MySQL supports ODBC connections, but again-not positive the details.