How to Import Data into MS SQL Server via R? (Part 1: csv, Excel, Snowflake)
Manipulating the data resides in multiple platforms plays fundamental role when you do the data analysis, machine learning, reporting, etc. We are going to discuss multiple approaches of leveraging R to upload the data in csv file, MS Excel, Snowflake into MS SQL Server.
csv into MS SQL Server
- Required package: RODBC
- Steps:
Step 0: Set up MS SQL server in ODBC Data Source Admin.
Step 1: Define SQL Server connection.
Step 2: Set working directory and Read the csv file as a data frame df.
Step 3: Import the df into target_laptop_price_csv via sqlSave function.
Step 4: Close the connection.
library(RODBC)
setwd('H:/Tutorial')
conn <- odbcDriverConnect('driver={SQL Server};server=Your Server;database=Your DB;trusted_connection=true')
df <- read.csv("target_laptop_price.csv")
sqlSave(conn, df, tablename="target_laptop_price_csv", rownames=FALSE)
odbcClose(conn)MS Excel into MS SQL Server
- Required packages: RODBC/readxl
- Steps:
Step 0: Set up MS SQL server in ODBC Data Source Admin.
Step 1: Define SQL Server connection.
Step 2: Set working directory and Read the csv file as a data frame df.
Step 3: Import the df into target_laptop_price_excel via sqlSave function.
Step 4: Close the connection.
library(readxl)
library(RODBC)
conn <- odbcDriverConnect('driver={SQL Server};server=Your Server;database=Your DB;trusted_connection=true')
setwd('H:/Tutorial')
df <- read_excel("target_laptop_price.xlsx")
sqlSave(conn, df, tablename="target_laptop_price_excel", rownames=FALSE)
odbcClose(conn)Snowflake into MS SQL Server (Method 1)
- Required packages: RODBC/DBI/dplyr/dbplyr/odbc
- Steps:
Step 0: Set up MS SQL server/ Snowflake in ODBC Data Source Admin.
Step 1: Define SQL Server connection and Snowflake connection.
Step 2: Define query and run it via DBI::dbGetQuery in Snowflake.
Step 3: Import the df into sf_to_sql_1 via sqlSave function.
Step 4: Close the connection.
library(DBI)
library(dplyr)
library(dbplyr)
library(odbc)
library(RODBC)
conn <- odbcDriverConnect('driver={SQL Server};server=Your Server;database=Your DB;trusted_connection=true')
sfconn <- DBI::dbConnect(odbc::odbc(), "SNOWFLAKE_General", uid="Username", pwd='Your Passcode')
tablename <- "TestTableFromSFtoSqlServer"
query <- paste0("SELECT * FROM DB.SCHEMA", shQuote(tablename))
df <- DBI::dbGetQuery(sfconn, query )
sqlSave(conn, df, tablename="sf_to_sql_1", rownames=FALSE)
odbcClose(conn)Snowflake into MS SQL Server (Method 2)
- Required package: RODBC
- Steps:
Step 0: Set up MS SQL server/ Snowflake in ODBC Data Source Admin.
Step 1: Define SQL Server connection and Snowflake connection.
Step 2: Define query and run it via sqlFetch in Snowflake.
Step 3: Import the df into sf_to_sql_2 via sqlSave function.
Step 4: Close the connection.
library(RODBC)
conn <- odbcDriverConnect('driver={SQL Server};server=Your Server;database=Your DB;trusted_connection=true')
sfconn <- odbcConnect("SNOWFLAKE_General", uid="Username", pwd="Your Passcode")
df <- sqlFetch(sfconn , "TestTableFromSFtoSqlServer")
sqlSave(conn, df, tablename="sf_to_sql_2", rownames=FALSE)
odbcClose(conn)
odbcClose(sfconn)Here are some other our articles for data import & flow across the platforms. Take a look if you are interested in these topics.
Python
JavaScript
- How to Import Data into MS SQL Server via JavaScript Node JS (Part 1: csv & Excel)?
- How to Import Data into Snowflake via JavaScript Node JS (Part 1: csv & Excel)?
- How to Import Notion data into MS SQL Server via JavaScript NodeJS?
Real Projects
- How to do Newegg Website Crawling for laptop price and spec via Scrapy and importing the data into SQLite ?
- How to do B&H Website Crawling for laptop price and spec via Scrapy and importing the data into SQLite?
- How to Scrape Fire Bureau website via regular expression and importing the data into SQLite?
Thank you and enjoy it! If you want to support Informula, you can buy us a coffee here :)
