Handle spaces in Column names (spotfire, R)

Question

I am trying to write a row from Spotfire data table to an MS Access database through RODBC R library API (called sqlUpdate): sqlUpdate(channel, inputTable,tablename = dTable,index = Index)

When there is a column which has spaces i.e First Name or special Chars. The R engine removes these spaces and populates a new column called FirstName. How to prevent R or Spotfire form doing this?

Can you provide an example of what the columns look like in the native environment? It might be something as simple as defining the column separator and decimal. — Gautam, Sep 17 '18 at 15:40
library(RODBC) driver="Driver={Microsoft Access Driver (*.mdb, *.accdb)}" channel<- odbcDriverConnect(paste(driver,';DBQ=',dLocation)) sqlUpdate(channel, inputTable,tablename = dTable,index = Index) — Mohammad Taradeh, Sep 17 '18 at 16:12

score 0 · Answer 1 · answered Sep 17 '18 at 18:04

0

Try adding the following line of code to remove the column names.

names(tablename) <- gsub(" ", "", colnames(tablename)) #remove spaces from column names

answered Sep 17 '18 at 18:04

BMAnalytics

114
7

The problem is my MS Access DB has few columns with spaces and special chars. So If I remove the spaces from the frame it will not remove it from the DB. And renaming the columns from the DB is nit a choice. – Mohammad Taradeh Sep 17 '18 at 18:06
There is a function which is invoked inside the sqlUpdate: function (colnames) gsub("[^[:alnum:]_]+", "", colnames) – Mohammad Taradeh Sep 17 '18 at 18:09

score 0 · Answer 2 · answered Sep 17 '18 at 20:09

As source code shows, RODBC::sqlUpdate() cleans column names of special characters (non-alphanumeric characters) with a mangleColNames() function:

mangleColNames <- function(colnames) gsub("[^[:alnum:]_]+", "", colnames)

Which removes any spaces as seen here:

colnames <- "First Name"

mangleColNames <- function(colnames) gsub("[^[:alnum:]_]+", "", colnames)
mangleColNames(colnames)
# [1] "FirstName"

To resolve, consider one of three options:

Update the source code of your RODBC library package to comment out mangleColNames() call in sqlUpdate. Specifically, use the approach below as shown here.
```
trace("sqlUpdate", edit=TRUE)
```
However, above is a temporary, interactive fix for your current R session. You will need to go to underlying library package code to adjust it permanently. Do note: as changing any code there is possibility of side effects can occur. Tread cautiously with this approach.

In fact, you may still need to add a wrapper to handle such spaces and special characters. In MS Access, you would use square brackets or backticks to work with such names.
Update column names in MS Access with DDL commands passed in sqlQuery. By the way, like all DBMS's it is highly recommended not to use spaces, special characters, or reserved keywords in names of columns, tables, views, or other stored objects.
```
ALTER TABLE mytable ALTER COLUMN fieldname fieldtype
```
Which for you would do as the following and can be looped to update all columns:
```
# ADD NEW COLUMN
sqlQuery(channel, "ALTER TABLE mytable ADD COLUMN [FirstName] Text(255)")
sqlQuery(channel, "UPDATE mytable SET [FirstName] = [First Name]")

# DROP OLD COLUMN
sqlQuery(channel, "ALTER TABLE mytable DROP COLUMN [First Name]")
```
Use a different library such as the R DBI-complaint, odbc, which does not have sqlUpdate but can be accommodated with dbWriteTable() without assumptions of column names.

Handle spaces in Column names (spotfire, R)

2 Answers2