We use cookies to improve your experience and for analytics. By continuing to use this site, you accept our use of cookies. Privacy Policy

Connection Strings / Files – Find And Replace

Global Office Find & Replace allows you to change various aspects of your data connections in EXCEL.

There are several possible use cases.

  1. The database name has changed.
  2. The shared connection file stored in SharePoint has changed.
  3. The server name or url has changed.
  4. You want to change the connection name.
  5. You want to check the box for the “Always Use Connection File” setting.

Etc.

GOFR allows you to inspect ALL of your connection strings located in ALL of your EXCEL documents on a computer or file share, and allows you to find and replace any aspect of the connection strings.

The Connection String Center screen

When you click on the “Excel Data Connections” button in step 3 of the main screen, the program reads the connections of the documents you selected in step 2 and you will be presented with 3 different tabs.

Connections By MS Office Document

Here you can browse thru all the files you selected in step 2, and by clicking on “Show Connections” reveal ALL the connection strings and files for the document. The property screen shows the property name and its value. If you click “UPDATE” here, a new find and replace operation will be created in the “Find/Replace/Update Connection” tab and you will be taken there to make the necessary changes. Click “Return To Document List” to go back to the list of files.

Unique Connections

Here you will see a list of all unique connection values with the number of documents each value is in. You can review all the values and click “Show Files” to see all the files where they are located (“Return To Unique Property List” takes you back). Click “UPDATE” to create an entry in the “Find/Replace/Update Connection” tab to find and replace that value for that connection string.

Find/Replace/Update Connection

This is where you will be making all the find and replace operations for the different connection string values. Each row has:

  • Delete – remove an entry from the find replace operation.
  • Select Property – this is a drop down list of the connection properties you can change.
  • Text To Find – enter text to find. This can be all or a part of the connection string, for example Server=myServer.
  • OPT (next to Text To Find) – this will let you enable a regular expression or wild card in the text to find: choose “Text”, “WildCard” or “Regular Expressions”. This works the same way as it does in the text substitution, see Text Options.
  • Text To Replace – enter the text you want to replace with, for example Server=myNewServer.
  • OPT (next to Text To Replace) – you can also enter a regular expression or wild card substitution AFTER the find operation is successful. This allows you to further manipulate the data. Please see our help topics on this subject.
  • Ignore Case – when checked, the case of the text is ignored, i.e. JoHn SmItH is considered equivalent to John Smith. This does not apply to regular expressions and wild card substitutions.

The buttons at the bottom of this tab:

  • ADD CHANGE – creates a new row to allow a new find replace operation.
  • LOAD CHANGES FROM EXCEL OR CSV TEMPLATE – loads the operations from an excel spreadsheet or a csv file, see Upload Connection string substitutions from an EXCEL/CSV template.
  • EXPORT CHANGES TO CSV – exports your connection string operations to a csv file. The file can be used as a template for loading.

Other buttons

  • Export All Connections to csv – exports ALL connection properties of all the documents to a csv file which can be opened in Excel. The file has the columns File, Property and Value, and it is opened when the export is finished.
  • RESET – removes all connection string find replace operations and reads the connections from your documents again.
  • Save And Close – click it when done. Then go back to the main screen, select the output in step 4 and click “Perform Substitutions or Extractions”.

Notes

  1. Unlike text substitutions, you can leave the text to find blank. This is important if you want to populate an empty property with a value. Simply leave the field blank and enter a value into the text to replace text box.
  2. Just like in the text substitution, if you select a regular expression or a wild card substitution in the text to replace, you must also use a regular expression or a wild card in text to find.
  3. If you select a regular expression or wild card, Ignore Case will be disabled as it has no bearing on regular expressions.
  4. Regular expressions are color coded in Green.
  5. Wild Card expressions are color coded in Yellow.
  6. Errors in regular expression or wild card entry are color coded in Pink.
  7. Some connection properties appear as check boxes in Excel, such as the “Always Use Connection File” and “Save Password”. These are treated as “True” or “False”, or “1” and “0”, and are shown in the program as “1 (Checked)” or “0 (Unchecked)”. When making the change for these values regular expression settings are ignored. When entering text to find, enter “1” or “true” for checked, and “0” or “false” for unchecked.
  8. Note that if you are updating a connection string and you are adding the password, if the save password check box is not selected Excel will remove that password. You must also update the Save Password option in that case.
  9. The trial version is limited to 2 connection string changes at a time.