Paths to linked Datasources in Excel

Hi There.

Does Aspose Cells provide reading and/or editing a connection string to a connected data source. In my case the datasource is an Access database file.

I would either like to edit the path to the file, or the connection string.

Thanks in advance.

@JohnGrahamLT,

Thanks for your query.

Aspose.Cells provides features to read and/or edit the existing data connection in a workbook. You may visit the following article to get a working example for modifying the exisitng connection string. This sample code guides you to modify SQL data string and if it does not fulfil your requirements, please share your sample Excel file, Access database file and sample code used for testing. We will look into it and provide our feedback at the earliest.

Modify existing SQL Data Connection using Aspose.Cells

Hi there and thank you for the reference code.

I have a file with a connection to an Access File, but using some of the code and properties in the example you referenced, I am not finding the current path to the Access file.

Is there a way to read this path without doing anything else to the (parent) excel file?

Attached is a picture of the values I’m getting for all the properties of the ExternalConnection object:
values.PNG (18.4 KB)

FYI: the file path information can be seen from within Excel, see the following image:
connection to Access.PNG (51.8 KB)

I am wanting to read it (programmatically first, and change the value sometimes.

Thanks in advance for your future comment.

@JohnGrahamLT,

You are right, currently Aspose.Cells does not provide modification of connection information for the Access database. This feature is only available for SQL database. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

Thank you for your reply.

We are currently obtaining (and can edit) the aforementioned file link paths from an xml part within the .xlsx file.

Are you planning to add this feature to Aspose.Cells? As this is an issue preventing us from using Aspose.Cells to process Excel Workbook files.

Thanks.

@JohnGrahamLT,

Could you please explain more about modifying this aforementioned file link paths from an XML part within the .XLSX file? Provide images, sample file and sample database file as well. Also if the new database file has different table structure, then how do you modify the queries which are used for fetching data from database? We will analyze all this information and provide our feedback.

1.) Creating An Excel Workbook that is Linked to an Access Database table:
1.1. Create and open a Microsoft Access file.
1.2. Create a single table and some arbitrary data, and save and close the Access file.
2.1. Create and open an Excel Workbook.
2.2. Select the Data tab.
2.3. In the Get and Transform Data section, click the Get Data button.
2.4. Choose From Database and then From Microsoft Access Database
2.5. Select the Microsoft Access file created in step 1.1.
2.6. In the Navigator window, select the table created in step 1.2. and press load .
2.7. Save and close the Workbook.

2.1.) Some of our Notes
Microsoft Excel 2016 has a new, built-in functionality called “Get & Transform”. It is also known as PowerQuery.
It suppports a.) querying data from files, databases, web services & custom sources and b.) transforming these.
Also, because it can combine data from multiple sources, it is called Mashups.
While the user typically specifies this in easy to use user interface, the source, query and transformations are stored in form of a new programming language called M.
The way Microsoft stores this is specified in the above referenced documents, but basically they compress the source text file, compress it in zip format, base64 it and store it in a package (Item1.xml) root element’s (DataMashup) content.

2.2.) At the moment we access the OOXML file parts and can decode and edit file paths to linked database files as per 1.)

3.) Microsoft Link

If Aspose.Cells could offer reading and editing the file paths associated with connected databases as described in 1.) then we would like to use Aspose.Cells with our development.

Thanks.

@JohnGrahamLT,

We have reviewed your requirement and have logged an investigation ticket for this feature request. We will write back here as soon as some feedback is ready to provide here.

CELLSNET-46564 - Provision to modify Access database file name linked with Excel

@JohnGrahamLT,

Could you try the following sample code with the template file (attached) on how to change the MS Access database file name for your needs:
e.g
Sample code:

Workbook workbook = new Aspose.Cells.Workbook(dir + "book1.xlsx");
ExternalConnectionCollection conns = workbook.DataConnections;

DBConnection con = (DBConnection)conns[0];
string str = con.SourceFile;     
Console.WriteLine(con.ConnectionInfo);     
con.SourceFile = @"D:\doc_n\Database22.accdb";
con.ConnectionInfo = con.ConnectionInfo.Replace(str, con.SourceFile);
workbook.Save(dir + "dest.xlsx");
``` 

Let us know your feedback.
files1.zip (7.5 KB)

The new functionality regarding this request is very good! Thank you.

There are two issues, however.

1.) [A BUG]
I notice that In the case where a “Connection” in an Excel file has no associated connection file (see image, below), the Aspose DBConnection object is not providing the value for DBConnection.ConnectionInfo (the connection string).

Attached is an example file where there are 5 connections.
Two of them do NOT have a connection file but DO have a value for the connection string. For these two connections, your API is not providing the connection string in the DBConnection.ConnectionInfo property.

Image showing a connection in an Excel file with no connection file. This connection, however, DOES have a connection string.
no-connection-file.PNG (34.3 KB)
Example Excel file (as in image).
CustomTesting_Send.zip (8.5 KB)

2.) [FOR FEATURE COMPLETION]
The second issue involves the following example Excel file:
Excel Master Test File.zip (96.2 KB)
Text file showing decoded mashup from example Excel file.
decoded mashup-text.zip (570 Bytes)

This file contains 3 “Queries”. Queries each contain a data source (a file path) and a query (i.e. an SQL query). This data is also found in the mashup text.

We need to read and write to Queries (similar to how you have implemented this for Connections), namely, we need to read and write the data source (file path) and the query itself. Providing read/write functionality with Queries would complete this mashup-text feature. We can then use your DLL in our software!

Thanks!

@JohnGrahamLT,
We have logged the issues in our database for investigation and for a fix.

This issues have been logged as

CELLSNET-46829 - DBConnection object is not providing value for DBConnection.ConnectionInfo
CELLSNET-46830 - Read and write to Queries

Once, we will have some news for you, we will update you in this topic.

@JohnGrahamLT,

This is to inform you that we have fixed your issues (logged earlier as “CELLSNET-46829” and “CELLSNET-46830”) now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

Regarding the issue “CELLSNET-46830”, you may get ExternalConnection with the following codes:

DBConnection conn = (DBConnection)workbook.DataConnections.GetExternalConnectionById(workbook.Worksheets[2].ListObjects[0].QueryTable.ConnectionId);

We will add QueryTable.ExternalConnection property to get connection in the upcoming fix/version.

Excellent!
Thank you.

@JohnGrahamLT,
You are welcome.

@JohnGrahamLT,

Please try our latest version/fix: Aspose.Cells for .NET v19.7.3 (attached)

Your issues (“CELLSNET-46829” and “CELLSNET-46830”) should be fixed in it.

Let us know your feedback.
Aspose.Cells19.7.3 For .Net2_AuthenticodeSigned.Zip (4.9 MB)
Aspose.Cells19.7.3 For .Net4.0.Zip (4.9 MB)

Excellent.

“CELLSNET-46829 - DBConnection object…” works great, thanks!

Regarding “Queries” and the example file “Excel Master Test File.xlsx” above, you suggest the values I need will be in the queryTable.ExternalConnection object.

Using your revised DLL and processing this file, for one of the queries we get:
queryTable.ExternalConnection.SourceFile = null, and
queryTable.ExternalConnection.Command = "SELECT * FROM [Query2]".

The definition for queries are contained in the decoded mashup text (like the definitions for DBConnections are), i.e. for Query2 you can see there is:

shared Query2 = let Source = OleDb.DataSource("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=E:\...\child3.accdb;", [Query="SELECT * FROM 'E:\...\child3.accdb'.[Stuff];"]) in Source;

The values for data source and query are obtained from here, i.e.

Data Source=E:\...\child3.accdb;", and
Query="SELECT * FROM 'E:\...\child3.accdb'.[Stuff];"]

Perhaps these can be DBConnections too?

These are the values need read/write functionality. Thanks!

@JohnGrahamLT,
Thank you for the feedback. We have logged it along with the ticket for our reference. We will write back soon to share our feedback.

@JohnGrahamLT,
Please force casting the ExternalConnection to DBConnection

ExternalConnection extConn = workbook.Worksheets[2].ListObjects[0].QueryTable.ExternalConnection;
if(extConn is DBConnection)
{
    DBConnection conn = (DBConnection)extConn;
    Console.WriteLine(conn.ConnectionInfo);                
    Console.WriteLine(conn.Command);
}

Let us know your feedback.

Thanks for your reply.

Here is an image of the values contained in the ExternalConnection object, as obtained via ListObjects[i].QueryTable…
dbConnection.PNG (32.2 KB)

As can see, the value for ExternalConnection.Command does not match the query statement in the mashups text.

Note that I need read/write on the actual query, as defined in the mashups text. See my previous message.

@JohnGrahamLT,
Thank you for the feedback. We are discussing it and will share our response after detailed analysis.