Office Data Connection Bulk Editor Tool for Excel files

A friend that works with Excel Services a lot and moves a ton of files around from development environment to production reached out and asked me to put together a tool that could update embedded Office Data Connection properties. Here it is: XLODCTool.

I don’t have time for a complete read me file right now, so here is first pass at “how to” instructions:

Setup trusted Excel file library to use both odc and embedded connections in your SharePoint. Now Excel Services will first use embedded in the file connection string unless you have “Always use connection file” check box checked.

XLODCTool will allow you to swap various connection string parameters in bulk in all selected files from a trusted Excel files library or the file system. If you have WebDAV on open your trusted doc lib in explorer, drag and drop Excel files with ODCs on the grid and then:

Step 1: Fill out ODC parameter name exactly as it appears in the connection string. Most likely it will be: “data source”.

Step 2. Fill out From value if you want to replace specific parameter value or leave it blank to set all occurrences of the parameter set in Step 1 to new value. Fill out To value (To can’t be left blank). E.g., if you supply value of “dev.box.com” in From box and set To box to “test.box.com” you will change: “data source=dev.box.com” to “data source=test.box.com” only in embedded connections where “data source= dev.box.com”. If From is left blank then all connections will be updated to “data source=test.box.com”.

Step 3. Click Process Files button and watch the progress.

NOTE: I’m skipping all connections that contain Extended Properties for now. Didn’t have much use for it yet, but will add it if there are users that need it, so leave a comment if you want it.

This entry was posted in Computers and Internet. Bookmark the permalink.

20 Responses to Office Data Connection Bulk Editor Tool for Excel files

  1. Kingkong says:

    Hi,
    The tool is very useful, but would you mind to add a function to replace the command text as well? This would definitely help me.
    Thanks,
    Kingkong

    • XLGeek says:

      Shouldn’t be too hard. Can you give me some specific scenarios? MDX, SQL or other sample string you want to feed would be very helpful.

      • Kingkong says:

        For example, my SQL statement is:
        SELECT ViewA.ColumnA, ViewA.ColumnB, ViewA.ColumnC FROM DatabaseToBeReplaced.dbo.ViewA ViewA

        I want to replace the string “DatabaseToBeReplaced” with another string so that I can move from development environment to testing/production environment.

        Thanks in advance.

      • XLGeek says:

        Ok, looks like you want partial command string replacement. It can get a little weird because of how command string is stored in the file part. I’ll see what I can do here. Busy with day job, but I’ll try to carve some time out for this. This ask makes perfect sense to me.

      • Kingkong says:

        I add “DATABASE=xxx” in the connection string and remove the database part in SQL command text as a workaround.
        Anyway, this is really a great tool and save me a lot of time. Thank you again.

      • XLGeek says:

        Glad it worked and thank you for sharing workaround steps with everyone.

  2. sateesh says:

    Hi, nice article, however i have a question. i have multiple odc files and i want to update command text query .
    example:
    current:
    select * from table_name where condition_1 = 2.0 and condition_2 = date

    want to update to:
    select * from table_name where condition_1 = 2.0 and condition_2 = date

    in multiple odc files. is there any easy way to do it?

  3. sateesh says:

    sorry for confusing .. correcting my comments

    Hi, nice article, however i have a question. i have multiple odc files and i want to update command text query .
    example:
    current:
    select * from table_name where condition_1 = 2.0 and condition_2 = date

    want to update to:
    select * from table_name where condition_1 = 10.0 and condition_2 = different date

    in multiple odc files. is there any easy way to do it?

    • XLGeek says:

      you shouldn’t have any problems. Property name is command. Pay attention to original command string encoding and match the pattern in the string to replace with.

      • bernycat says:

        I’m trying to modify the Command Text in my connection. Command text that I want to change is EMPLOYEE_ID = ‘00000’ to EMPLOYEE_ID = ‘00131’. Is that possible with the tool. I saw this post but couldn’t make it work. Whenever I try it, it puts EMPLOYEE_ID = ‘00131’ into the Connect String box. If it’s possible, what do I put in the 3 fields of the tool to make it work (Name, From, and To)?

        By the way, this tool saved me a ton of time when switching to a new server in January. Thank you!

      • XLGeek says:

        I think you are trying to modify SQL query attribute. You need the name of the attribute and full string to replace it with. It may work. I can check it out if you want to share the file.

      • Bernycat says:

        Yes, trying to bulk modify the sql query in the command text section of the data connection. How would I share the file?

      • XLGeek says:

        I’ll send you an e-mail in a moment.

  4. Michael says:

    This massivly helpful, thank you for sharing this tool!

  5. Terrance says:

    Hi, I have thousands of excel documents that query a Dynamics CRM 2011 server using web queires. We have changed the domain name for CRM and these have stopped working. I have tried to use your tool to change the connections, it picks up the ODC correctly but doesn’t replace the URL. Is this something the tool can do?

    Thanks

    • XLGeek says:

      URL replacement works fine. Internally it is just an encoded string replacement. Check two things: proper name of the property “Data Source” (no quotes) and make sure your URL doesn’t have any incompatible with XML characters that are not XML encoded in the replacement string. If you send me a sample URL I can check it out for encoding. I haven’t has any issues with URL based data sources. The only thing that breaks this tool is presence of “Extended Properties…”.

  6. marcdp2013 says:

    Each time I attempt to replace the the data source it just adds the new details to the bottom, so in turn adds rather then replaces

    • XLGeek says:

      I don’t understand the issue from your description. Can you provide more details? All named parameters of the connection string get replaced. Code doesn’t have a path to add anything (unless of course you have it in the replacement string “To:” field). This app doesn’t work with connection strings that contain “Extended Properties”.

Leave a Comment or Ask a Question

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s