Loading data with RSQLite which has quoted values

Question

I am trying to load a large-ish csv file into a SQL lite database using the RSQLite package (I have also tried the sqldf package). The file contains all UK postcodes and a variety of lookup values for them.

I wanted to avoid loading it into R and just directly load it into the database. Whilst this is not strictly necessary for this task, I want to do so in order to have the technique ready for larger files which won't fit in memory should I have to handle them in the future.

Unfortunately the csv is provided with the values in double quotes and the dbWriteTable function doesn't seem able to strip them or ignore them in any form. Here is the download location of the file: http://ons.maps.arcgis.com/home/item.html?id=3548d835cff740de83b527429fe23ee0

Here is my code:

# Load library
library("RSQLite")

# Create a temporary directory
tmpdir <- tempdir()

# Set the file name
file <- "data\\ONSPD_MAY_2017_UK.zip"

# Unzip the ONS Postcode Data file
unzip(file, exdir = tmpdir )

# Create a path pointing at the unzipped csv file
ONSPD_path <- paste0(tmpdir,"\\ONSPD_MAY_2017_UK.csv")

# Create a SQL Lite database connection
db_connection <- dbConnect(SQLite(), dbname="ons_lkp_db")

# Now load the data into our SQL lite database
dbWriteTable(conn = db_connection,
              name = "ONS_PD",
              value = ONSPD_path,
              row.names = FALSE,
              header = TRUE,
              overwrite = TRUE
              )

# Check the data upload
dbListTables(db_connection)
dbGetQuery(db_connection,"SELECT pcd, pcd2, pcds from ONS_PD  LIMIT 20")

Having hit this issue, I found a reference tutorial (https://www.r-bloggers.com/r-and-sqlite-part-1/) which recommended using the sqldf package but unfortunately when I try to use the relevant function in sqldf (read.csv.sql) then I get the same issue with double quotes.

This feels like a fairly common issue when importing csv files into a sql system, most import tools are able to handle double quotes so I'm surprised to be hitting an issue with this (unless I've missed an obvious help file on the issue somewhere along the way).

EDIT 1

Here is some example data from my csv file in the form of a dput output of the SQL table:

structure(list(pcd = c("\"AB1 0AA\"", "\"AB1 0AB\"", "\"AB1 0AD\"", 
"\"AB1 0AE\"", "\"AB1 0AF\""), pcd2 = c("\"AB1  0AA\"", "\"AB1  0AB\"", 
"\"AB1  0AD\"", "\"AB1  0AE\"", "\"AB1  0AF\""), pcds = c("\"AB1 0AA\"", 
"\"AB1 0AB\"", "\"AB1 0AD\"", "\"AB1 0AE\"", "\"AB1 0AF\"")), .Names = c("pcd", 
"pcd2", "pcds"), class = "data.frame", row.names = c(NA, -5L))

EDIT 2

Here is my attempt using the filter argument in sqldf's read.csv.sql function (note that Windows users will need rtools installed for this). Unfortunately this still doesn't seem to remove the quotes from my data, although it does mysteriously remove all the spaces.

library("sqldf")
sqldf("attach 'ons_lkp_db' as new")
db_connection <- dbConnect(SQLite(), dbname="ons_lkp_db")
read.csv.sql(ONSPD_path,
              sql = "CREATE TABLE ONS_PD AS SELECT * FROM file",
              dbname = "ons_lkp_db",
              filter = 'tr.exe -d ^"'
              )

dbGetQuery(db_connection,"SELECT pcd, pcd2, pcds from ONS_PD  LIMIT 5")

Also, thanks for the close vote from whoever felt this wasn't a programming question in the scope of Stack Overflow(?!).

Answer 1

The CSV importer in the RSQLite package is derived from the sqlite3 shell, which itself doesn't seem to offer support for quoted values when importing CSV files (How to import load a .sql or .csv file into SQLite?, doc). You could use readr::read_delim_chunked():

callback <- function(data) {
  name <- "ONS_PD"
  exists <- dbExistsTable(con, name)
  dbWriteTable(con, name, data, append = exists)
}

readr::read_delim_chunked(ONSPD_path, callback, ...)

Substitute ... with any extra arguments you need for your CSV file.

Answer 2

Use read.csv.sql from the sqldf package with the filter argument and provide any utility which strips out double quotes or which translates them to spaces.

The question does not provide a fully reproducible minimal example but I have provided one below. If you are using read.csv.sql in order to pick out a subset of rows or columns then just add the appropriate sql argument to do so.

First set up the test input data and then try any of the one-line solutions shown below. Assuming Windows, ensure that the tr utility (found in R's Rtools distribution) or the third party csvfix utility (found here and for Linux also see this) or the trquote2space.vbs vbscript utility (see Note at end) is on your path:

library(sqldf)
cat('a,b\n"1","2"\n', file = "tmp.csv")

# 1 - corrected from FAQ
read.csv.sql("tmp.csv", filter = "tr.exe -d '^\"'")

# 2 - similar but does not require Windows cmd quoting
read.csv.sql("tmp.csv", filter = "tr -d \\42")

# 3 - using csvfix utility (which must be installed first)
read.csv.sql("tmp.csv", filter = "csvfix echo -smq")

# 4 - using trquote2space.vbs utility as per Note at end
read.csv.sql("tmp.csv", filter = "cscript /nologo trquote2space.vbs")

any of which give:

  a b
1 1 2

You could also use any other language or utility that is appropriate. For example, your Powershell suggestion could be used although I suspect that dedicated utilities such as tr and csvfix would run faster.

The first solution above is corrected from the FAQ. (It did work at the time the FAQ was written many years back but testing it now in Windows 10 it seems to require the indicated change or possibly the markdown did not survive intact from the move from Google Code, where it was originally located, to github which uses a slightly different markdown flavor.)

For Linux, tr is available natively although quoting differs from Windows and can even depend on the shell. csvfix is available on Linux too but would have to be installed. The csvfix example shown above would work identically on Windows and Linux. vbscript is obviously specific to Windows.

Note: sqldf comes with a mini-tr utility written in vbscript. If you change the relevant lines to:

Dim sSearch  : sSearch  = chr(34)
Dim sReplace : sReplace = " "

and change the name to trquote2space.vbs then you will have a Windows specific utility to change double quotes to spaces.

Answer 3

Honestly I could not find anything to solve this problem. sqldf documentation tells "so, one limitation with .csv files is that quotes are not regarded as special within files so a comma within a data field such as "Smith, James" would be regarded as a field delimiter and the quotes would be entered as part of the data which probably is not what is intended"

So, It looks like there is no solution as far as I know.

One possible suboptimal approach (other then obvious find and replace in text editor) is to use SQL commands like this

dbSendQuery(db_connection,"UPDATE ONS_PD SET pcd = REPLACE(pcd, '\"', '')")