Graphics

Hi,

The excel datalinq on our Xpression Studio has somehow broken overnight. We had two worksheets with their own datalinqs and when trying to re-establish the links, I get a blue circle. I can create a new datalinq using the same excel file and worksheets and it seems to be working, but this seems like a big problem if I have to do this every day or during a production if it fails again.

Any ideas on why this would be happening?

Xpression Version: 5.5 Build 2722

Where are you getting the 'blue circle'?

Can you hit 'browse' in the Datalinq Manager and see the Excel Data Source content?

Are you using the Excel Datalinq or ADODB?

Are you running Excel locally on XPression or remotely?

#XPression

It's when I hit 'Browse' in the manager.

We're using Excel and it's locally on the Xpression machine.

#XPression

Do you still have the 'broken' Excel Data Source connections in your Datalinq server? In addition to the new ones?

Can you save your config and upload it? File > Save Configuration

#XPression

Yes. Both connections are still on the Datalinq server. One works but the other doesn't, even though they're setup the exact same.

Here's the Config: https://drive.google.com/file/d/0B-20A0ffjvbJRUlSNkxQZnlaQ28/view?usp=sharing

#XPression

#19 works and #17 doesn't?

#XPression

Correct.
..........
#XPression

If you bring online a scene that is using #16 does #19 stop working?

#XPression

We've actually never got the ODB connection to work unfortunately. For some reason it adds a '$' to any table you try to browse and no data comes up.

#XPression

You should remove Datalinq connections that aren't working. I would speculate that Datalinq got into a strange state because it is trying to connect to the same file from multiple Datalinq sources.

#XPression

That seemed to do the trick! We removed some of the extra failed ones we had and it reconnected. Thanks!

#XPression

Michael, with the ADODB datalink, you should create a named range in excel to define the regions you want to use the data from. To create the named range you can just highlight the rows/columns in excel and then type a name for that range into the location shown in this screenshot.. (Or you can use the "Name Manager" found on the Formulas ribbon bar. I highly recommend the ADODB datalinq over the Excel datalinq. It is way more flexible and reliable and it doesn't even require Excel to be installed on the machine to read the XLS files.



#XPression

Michael,

Take a look at the Datalinq Part 1 video shown here: http://xpression.rossvideo.com/videos/

#XPression

Hi Brian

Trying to get an ADODB datalinq to work with Excel after watching the tutorial you mention.

Everything appears to be the same except the Datalinq Server configuration is different as mentioned below the video. The "test connection" comes back with a "Connection successfully established" message.

When I try to Browse the spreadsheet it comes up blank and there is no dropdown menu of tables as shown in the video.

I have tried experimenting with a previous spreadsheet of data as well as creating a new simple 3 column worksheet but still the same result. No data or table dropdown.

Using Designer v5.5 build 2731 and Excel 2010

#XPression

Toby, Can you post screenshots of your results and also your adodb datalinq setup?

#XPression

Turns out that it was because we hadn't saved out the Excel file as .xls as opposed to .xlsx. So it all works now as shown once we saved as the older format.

#XPression

For .XLSX files download the Microsoft ACE OLEDB 12.0 driver (32-bit) and use the following connection string (with the correct file location).

Provider=Microsoft.ACE.OLEDB.12.0;

Data Source=c:xlsx;

Extended Properties="Excel 12.0 Xml;

HDR=YES";

If I remember correctly it's implemented in the 5.9 BETA, so should be standard in future releases.

#XPression

Tim, You'll probably want to add IMEX=1 to that connection string. Otherwise you sometimes run into issues where numbers don't get imported into datalinq when they are in the same column as text (non-numbers).

#XPression

I make everything "plain text" in Google Spreadsheet, so not running in to that issue. But I do recommend it yes.

#XPression