0

I have a database file (a .mdf file from a microsoft SQL server) that I copied on my disk. It comes from a device, and I would like to read the data, preferably in R. I am totally new to SQL, and I don't understand how to deal with a local file. I tried

library(RMySQL)
con <- dbConnect(RMySQL::MySQL(), dbname = "MGCDBase")

which gave me

Error in .local(drv, ...) : 
  Failed to connect to database: Error: Can't connect to MySQL server on 'localhost' (0)

I don't get if it is because there is a password, if I am doing it wrong, or if I should use something else than R or RMySQL. Any help or advise would be welcome

denis
  • 5,580
  • 1
  • 13
  • 40
  • If you have an `.mdf` file, which is basically a dump of some database, then why are you trying to also connect to another database? – Tim Biegeleisen Dec 13 '18 at 14:30
  • My guess is that you'll first have to import the file into your local deploy of MySQL and fetch the data from there. Not sure if you can fetch directly from a file. sqlite is designed like that, but not sure if MySQL can do it... For example, see [here](https://stackoverflow.com/questions/18304045/what-to-use-to-open-an-mdf-sql-database-file). – Roman Luštrik Dec 13 '18 at 14:41
  • @TimBiegeleisen I actually don't know what I am trying. I though I could open the file (it is a dump indeed) this way, but I have no knowledge of how SQL works. Am I forced to set a local server with SQL, or can I use R to directly open the dump ? – denis Dec 13 '18 at 14:44
  • @RomanLuštrik Ok thanks for the hint, I ll try to install MySQL and import the file. – denis Dec 13 '18 at 14:45
  • Several options for you: 1) Try to connect directly to your database from R using the appropriate package, 2) Export the tables you care about to CSV, then use `read.csv` in R, 3) Otherwise maybe try to work with the `.mdf` file. – Tim Biegeleisen Dec 13 '18 at 15:10
  • @TimBiegeleisen 1) that exactly why I would like to do, but I was seeking help to do that actually... 2) I don't know how to do that either. How can I get the tables ? – denis Dec 13 '18 at 15:30
  • #2 is more of a SQL question. Most databases have tools for dumping tables to CSV. Check the docs. – Tim Biegeleisen Dec 13 '18 at 15:38
  • @TimBiegeleisen Thank you for your patience. Actually I am doing that because I don't have the tool to make dump. It is database, and you can't export anything while I need all the data in the device. The only thing you can do is display some data on their soft and copy-paste them. They use DBtool, you can export the data but they are encrypted. I just copied the data base and I would like to find a way to open it. Total newbie on SQL, sorry – denis Dec 13 '18 at 16:30
  • You should be able to install a version of SQL Server locally, and then import/attach that `.mdf` file. I have done this before, but I would direct you to other questions on this site for a full explanation of how to do this. – Tim Biegeleisen Dec 13 '18 at 16:31
  • @TimBiegeleisen ok I ll do that. I am installing SQL server express to test, I ll see if I can do something. – denis Dec 13 '18 at 16:33

0 Answers0