ODBC to Socrata

This walkthrough goes through the steps of taking data from any system that uses ODBC and then loads that into an existing Socrata table using write.Socrata from RSocrata

Step 1: Install Required Packages

install.packages('RODBC')
install.packages('RSocrata')

Step 2: Connect to ODBC Database

Create constants so that R can access your database instance.

ch <- odbcConnect("some dsn", uid = "user", pwd = "****")

Step 3: Write to Your Data Frame

Use con to write to data.frame:

res <- sqlFetch(ch, "table name")

Step 4: Identify Destination Dataset

Ensure that you have a Socrata dataset with proper fields and proper datatypes to write to. Capture the 4x4 ID for the dataset you want to publish to.

Step 5: Write Your Dataset

socrataEmail <- Sys.getenv("SOCRATA_EMAIL", "XXX@socrata.com")
socrataPassword <- Sys.getenv("SOCRATA_PASSWORD", "XXXXXXX")
datasetToAddToUrl <- "https://opendata.socrata.com/resource/evnp-32vr.json" 
write.socrata(table, datasetToAddToUrl, "UPSERT", socrataEmail, socrataPassword)

For full documentation on RODBC please see https://cran.r-project.org/web/packages/RODBC/vignettes/RODBC.pdf