Hello to all,
I´m a newbe from Spain, currently working with PowerBI, Dax and R. I´m also getting a hands-on introduction to SQL, progress and MySQL. So bare with me if I do not use correct terms to explain things sometimes, though I will try and keep things profesional.
I have a dataset saving problem. I've imported a dataset from our ERP that has dates, numers and strings.
I use ODBC conections (Windowds enviroment) to connect to the ERP (Progrress database) and retrieve the data. That works ok and RStudio shows the dataset info correctly.
I then use another ODBC conector to access a MySQL database save the dataset. When I check the info beeing uploaded, it does not import dates correctly. RStudio does detect date fields, and shows them correctly. When I upload the dataset the field that should have dates only shows the year.
Is there a problem with the way I created the MySQL database?
Is there a way to preserve or define the fields of the dataset as strings or the propper format before submiting?
Here's what I run, and some similar data to what I get as output:
(Names of conetions, fields and databases have been changed to preserve privacy)
#Load libraries - Step1
erpcon <-(This is working, so I skip this part)
#Generate datasets -step3
Dataset1<-dbGetQuery(erpcon, 'select * from "TableinERP 1"' ')
#Store datasets Step-4
sqlSave(MySQLdb, Dataset1, tablename="Sales", rownames=FALSE, safer=FALSE, append=FALSE)
The data in Dataset1, when previewed in RStudio, is right. No data corrupted or altered.
When I check the MySQL database, date fields are set to Doulbe, and only store the year. Is as if the sqlSave command does not know how to pass dates.
Am I using the right libraries and commands? Is there an easier way?