Luigi Marongiu
2021-Nov-10 19:30 UTC
[R] how to connect libreoffice base odb using R package odbc?
I tried this: ``` x = dput(sapply(names(ODB::odb.tables(db)), function(tbl) { sqlQuery <- paste0('SELECT * FROM "', demographic, '"') ODB::odb.read(db, sqlQuery) }, simplify=F)) y = x$demographic ``` so now `y` is the dataframe for the table `demographic`: ```> yID Study_ID Hosp_ID Hospital Address 1 5 0 NA Royal Free - Tottenham Court road Tottenham Court road 2 6 0 NA Santa Croce in Gerusalemme - Via Latina Barcelona Occ_ID Age Sex day_ID month_ID century year_ID Year Occupation Day DOB Enrollment 1 NA NA 2 NA NA 19 NA 10 Student 24 NA 2020-11-01 2 NA NA 1 NA NA 20 NA 9 Researcher 26 NA 2022-01-01 EnrolTime 1 13:24:00 2 14:25:00> str(y)'data.frame': 2 obs. of 18 variables: $ ID : num 5 6 $ Study_ID : num 0 0 $ Hosp_ID : logi NA NA $ Hospital : chr "Royal Free - Tottenham Court road" "Santa Croce in Gerusalemme - Via Latina" $ Address : chr "Tottenham Court road" "Barcelona" $ Occ_ID : logi NA NA $ Age : logi NA NA $ Sex : num 2 1 $ day_ID : logi NA NA $ month_ID : logi NA NA $ century : num 19 20 $ year_ID : logi NA NA $ Year : chr "10" "9" $ Occupation: chr "Student" "Researcher" $ Day : num 24 26 $ DOB : logi NA NA $ Enrollment: chr "2020-11-01" "2022-01-01" $ EnrolTime : chr "13:24:00" "14:25:00" ``` Still, I thought it might have been easier to run `odb.read`. But it works, thank you again! On Wed, Nov 10, 2021 at 7:03 PM Rasmus Liland <jral at posteo.no> wrote:> > On 2021-11-10 18:55 +0100, Luigi Marongiu wrote: > > no, it worked. that table was indeed > > empty and you for the right entries on > > the other table. I'll try on my > > machine, thank you! > > Cheers man!-- Best regards, Luigi
Rasmus Liland
2021-Nov-10 21:33 UTC
[R] how to connect libreoffice base odb using R package odbc?
Dear Luigi, Yes, from the named list of dataframes, you access the demographic table like that. You can remove dput() now, I used it only to print the output; read ?dput, it's very handy the next time you ask questions on this list. Take a closer look at my obfuscated code ... To only query the demographic table, you would use ODB::odb.read(odb, 'SELECT * FROM "demographic"') Best, Rasmus -------------- next part -------------- A non-text attachment was scrubbed... Name: signature.asc Type: application/pgp-signature Size: 833 bytes Desc: not available URL: <https://stat.ethz.ch/pipermail/r-help/attachments/20211110/5c3a5c05/attachment.sig>