How to read integer daata type column NULL value as nothing into a df using sqlquery?

Question

I am reading a table using SQLqruey function and saving it to a df. code is as below

table_data_query<-"select * from sample_data"

tables_data<- sqlQuery(cn, table_data_query,errors=TRUE,rows_at_time = 100,stringsAsFactors = FALSE, as.is = TRUE, na.string = "NULL", nullstring = "")

The varchar and char data type columns NULL values are reading as empty(nothing) when reading into df but with one column(int data type) NULL value is reading "NA"(text/string)into data frame

How to read int data type column NULL values as nothing.

score 1 · Accepted Answer · edited May 23 '17 at 10:30

I cannot reproduce your problem. When I do the following:

table_data_query <-  "SELECT * FROM (VALUES(NULL,'a','b'),(1,NULL,'c')) V(a,b,c)"
dat <- sqlQuery(con, table_data_query, na.strings = c("NULL"),
                errors=TRUE,rows_at_time = 100,
                stringsAsFactors = FALSE, as.is = TRUE,
                nullstring = "")

str(dat)

It results in

'data.frame':   2 obs. of  3 variables:
$ a: int  NA 1
$ b: chr  "a" ""
$ c: chr  "b" "c"

Which shows that the integer is read into R as an integer. The NA you read here is how R displays missing values.

For example:

> sum(dat$a)
[1] NA

> sum(dat$a, na.rm = T)
[1] 1

edit: to save NA as blanks to CSV look here: Write a dataframe to csv file with value of NA as blank

How to read integer daata type column NULL value as nothing into a df using sqlquery?

1 Answers1