How to force R to not use E as a scientific notation in data.tables

General
1

for example:

The value 4E5 is a result from a sql query and hence I cannot specify str before calling data.tables. Is there a parameter in data.table such as 'columntype' where I can specify the data type for the variables

x = data.table( val = 4E5)
x
val
1: 4e+05

But I want it to be a string

x = data.table( val = 4E5)
x
val
1: '4E5'

2

x = data.table( val = "4E5")

3

This doesn't work as I am getting the value 4E5 from a sql query. Even if you tried to do str(4E5) it transforms it to a num. So your solution wouldn't work for my use case.

4

Well, you'll need to specify how you import the data in that case.

If you are reading the data directly from an obdc connection then the column type should be respected. If you are reading it from a file, then you can specify the string as part of the file read function.

It's impossible to know without your code.

5

Yeah makes sense, I've added a sample query below, how would I be able to work around this.

dt = data.table(sqlQuery('odbc',"SELECT '23E5' AS val"))

or

dt = data.table(sqlQuery('odbc',"SELECT CAST('23E5' AS char(4)) AS val"))

here the dt has a column called val but the value is 2300000 instead of 23E5. Even though the variable is a string in the sql query.

6

Unfortunately I cannot replicate your second function (probably differences in allowed SQL syntax between our two systems).

One way to handle this may be to use the DBI and odbc packages rather than RODBC. (You may also consider dbplyr, too. I use them and have no such issues with column types. You can then always convert to a data.table for further manipulation once you have retrieved your data.

https://db.rstudio.com/

7

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.