How to move ODBC DSN information from one computer to another

YAAAAAAAAY!!!! I got a new computer!!

Oh crap how do I move the DSN connections that I made on this computer?? This is how (assuming you’re on windows)! But before, we do, we have to make sure we have the right drivers… so lemme first go over the basikses my precious:

TO ADD NEW CONNECTIONS:

Build a shortcut on your desktop to: C:\Windows\SysWOW64\odbcad32.exe
Add connections from there.

IF IN THE NEW COMPUTER YOU CAN’T ADD THE NEW CONNECTIONS YOU NEED:

You need to download the drivers. In my case, since I want to talk to Azure databases, I need this driver: http://www.microsoft.com/en-us/download/confirmation.aspx?id=36434. That worked for me, you get what you need and download it.
Install it. It will automatically add the connection type ODBC Driver 11 for SQL Server (or whatever) to your odbcad32.

OK, now that you’re all set and you know your new pc can do the connections, without further ado:

TRANSFER DNS FILES FROM COMPUTER A TO COMPUTER B:

On Computer A, open the Registry Editor by typing “regedit” in the Windows search box under the Start menu and selecting regedit.exe
Navigate to the following registry key : HKEY_CURRENT_USER > Software > ODBC
Right-click on the ODBC.INI folder, choose Export, and save the .reg file to the location of your choice
Copy the .reg file (or files if you have both User and System DSN data sources) to any location Computer B
On Computer B, double click the .reg file(s) to import the DSN data sources to the registry (select “Yes” and “OK” when prompted)

done!

EDIT: What if we are going from a Computer to a Server?

One of the steps of operationalizing scripts is to move them from a computer to a server. When that happens now we have to sync data connections too… in addition, it’s possible there’s a different driver and syntax (if we’re going from Windows to Linux). Therefore, we propose a workthrough to keep ODBC sources synced.

From the server, create a git repo in the folder that contains the ODBC.ini file (in my version of Ubuntu 16.04, it’s /etc. You DONT need to add all files to the repo… only ODBC.ini. Back that up to a private repo on github, and clone it to your windows machine.
Write a cmd script that will automatically export the ODBC keys, from here. Make sure you are in the cloned folder, then startup a Command promt (type Windows Key and then cmd ). Inside there, type: reg export HKEY_CURRENT_USER\Software\ODBC export.reg This has exported the ODBC sources to a file called export.reg. Which is cool! We just need to do some gentle manipulations to get it to work on the server.
In order to get this to work on the server, we need to do a few things (this is for me, in Ubuntu 16.04):
1. Delete the top stuff (you don’t see it in the above, but we need to export only the sources)
2. Remove quotation marks
3. Change the Driver
4. Delete the extra stuff before the DNS stuff.

Let’s write a R script to accomplish this:

## Get latest stuff
#system("reg export HKEY_CURRENT_USER\Software\ODBC export.reg",intern = F) 
## ^ Hrm... this part is a bit finecky... just do it manually through cmd for now.

## Read in
file <- file("export.reg")
a <- readLines(file,skipNul = T)
close(file)

## Lose head
a <- a[-1:-12]

## Remove quotes
a <- gsub('"','',a)

## Remove path to DNS name
a <- gsub('.+ODBC.INI\\\\','[',a)

## Replace Driver
a <- gsub('C.+?msodbcsql11.dll','ODBC Driver 13 for SQL Server',a)

## Delete blank lines
a <- a[a !=""]

## Write
file <- file("odbc.ini")
writeLines(a,file)
close(file)

Done! Now you have a way of keeping the server data sources synced with your laptop!