Guided Project - Issues Connecting To New Database File - R / RStudio

Hello. I’m nearly finished with the SQL chunk of the Data Analyst in R path, and after performing some exploratory data analysis on the MLB data sets, the time had come to produce a new .db file and fill it with our .csv files, as well as develop a new data column for our primary key.

However, just like in the last guided project, I had run into issues for properly connecting to the database. With all of my files in one location, instead of manually choosing the directory path, setting the working directory to source file location had solved my problems before, but now even that is currently not enough.

mlb <- dbConnect(SQLite(), "mlb.db")
dbWriteTable(conn = mlb, name = "game_logs", value = "game_logs.csv", header = TRUE)

this first line successfully creates an mlb.db file in the source file location, without it existing prior to this line of code running, however the following line, which aims to define a new table with the information from the game_logs.csv file in mlb.db, produces the following error:

Error in file(file, “rt”) : cannot open the connection

Any ideas as to how to resolve this? Just like last time, it’s a pretty limiting blockade to work around.

hi @lucillejdpeterson

At the onset, I don’t have experience with R at all.

Can you provide details about how last time you were able to solve this issue and additional info such as:

  • If you got any help from any of the posts available on DQ community itself, can you please attach the same here.
  • the OS version you are using if that can help community members to figure out any workaround
  • have you checked for solutions on stack overflow as well, if yes, what all options have you tried other than the given one here?
    etc.

based on a google search I came across these posts, apologies in case you have already tried and tested them:

2 Likes

My apologies for responding so late to this - I had tunneled on the next course in the Data Analyst in R path and did not see the notification email until this morning.

  • The other recent case I cited can be found here.
  • I’m currently running Windows 10.
  • I am familiar with both of those posts, regrettably my fiddling with different file path notation has gotten me the same error message. I initially tried to use the RStudio settings Session > Set Working Directory > Choose Directory, where the suggestion in the previous case to switch to Session > Set Working Directory > To Source File Location solved my problems. I’ve tried both of these methods, as well as manually calling setwd(). If the solution resides in one of those two posts you linked, my reading comprehension may need some work.

Here’s the setwd() call I made:

setwd("/Users/gyran/OneDrive/Desktop/Wig!/Important Documentation/Stats Files Websites and Listings/Dataquest dot io takeaway PDFs/Guided Projects")

(I made several tweaks to the end with additional forward slashes as well.)

Finally, here is the directory and the path in question shown through windows explorer, just to make sure.

hi @casey. Not sure who else to connect for this. Any suggestions?

Well, late reply again, but I hadn’t checked the solution notebook, and I gotta say, the solution to this problem was really straightforward.

mlb <- dbConnect(SQLite(), "mlb.db")
dbWriteTable(conn = mlb, name = "game_logs", value = game_logs, header = TRUE)

It was setting the value to my variable where I had already read in the .csv file, instead of the csv file. Previously the statement was value = “game_logs.csv”. Perhaps a read_csv() call would’ve also worked but, wow, what a silly error to get caught on for so long. I’ll go ahead and leave this here in case anyone else is running into this issue, haha…whoops.

1 Like