How to Read and Update Sql File in R

Data professionals get requests to import, export data into various formats. These formats tin be such as Comma-separated information(.CSV), Excel, HTML, JSON, YAML, Tab-separated information(.TSV). Normally, we utilize SQL Server integration service ETL packages for data transformations, import or export data.

SQL Car Learning tin be useful in dealing with various file formats. In the article, External packages in R SQL Server, we explored the R services and the various external packages for performing tasks using R scripts.

In this article, we explore the useful Rio package developed by Thomas J. Leeper to simplify the information export and import process.

Surround requirements

For this commodity, you should have the SQL Server environment. In this article, I use the followings:

Version: SQL Server 2019
Machine learning Linguistic communication: R
Node: SQNode2\INST1
SQL Server Launchpad and Database engine service should exist in running state
SQL Server Direction Studio

Yous tin can follow An overview of SQL Machine Learning with R scripts, to meet these requirements.

Data Import and Export using SQL Automobile Learning R Scripts

R Script in SQL Server is capable of performing advanced analytic and predictive applications using the Microsoft R runtime. Nosotros tin execute the R lawmaking directly from the SQL Server using the sp_execute_external_script stored procedure.

Nosotros can apply the Rio package for data import and consign in diverse formats using the R scripts. It is an external package and does not install when you configure the SQL Server Machine Learning for R.

To check the Rio package in your environment, you lot can run the following script.

EXECUTE sp_execute_external_script

@ language = N 'R' ,

@ script = N '

library(rio)

print (packageVersion("rio"))

As shown below, it gives an error bulletin that in that location is no bundle chosen Rio.

error message

To install the Rio package, open up the administrative R console from the C:\Program Files\Microsoft SQL Server\MSSQL15.INST1\R_SERVICES\bin path and run the following command.

install.packages("rio")

It downloads the required configuration files and installs it for your R server. You should take an internet connection for downloading these components.

administrative R console for R and SQL Server

Install the package

Once the Rio packet installation completes, you go the following screen.

Rio package installation

You tin notice the Rio library in the default SQL instance directory C:\Program Files\Microsoft SQL Server\MSSQL15.INST1\R_SERVICES\library.

Rio library

Now, rerun the stored process, and it returns the Rio bundle version.

Rio package version

Download a CSV file from the Web URL and import it

For the commodity, let's download a sample CSV file from the following download.file() function. It downloads the file and places information technology into the default R directory.

> download.file("http://flake.ly/BostonSnowfallCSV", "WinterSnowfalls.csv")

Download a CSV file

You can come across the CSV file in the C:\Program Files\Microsoft SQL Server\MSSQL15.INST1\R_SERVICES\bin folder.

Downloaded file

The Rio packet uses the import() office to read the CSV file. You lot can use the import() function to read data from compressed directories and URLs (HTTP or HTTPS). Refer to the GitHub repository for supported file formats.

> Mydata <- rio::import("WinterSnowfalls.csv")
> Mydata

Y'all tin see data in the R client console, as shown below.

import() function

In the below R and SQL Server, nosotros use \\ for specifying the CSV file path. R script considers \ as an escape character.

EXECUTE sp_execute_external_script

@ language = N 'R' ,

@ script = N '

library(rio)

CSV <- import("C://Programme Files//Microsoft SQL Server//MSSQL15.INST1//R_SERVICES//bin//WinterSnowfalls.csv")

OutputDataSet <- CSV

As shown below, using the R script in SQL Server, nosotros become the data from the CSV file. Information technology reads a total of 76 records from the specified CSV file.

Note: Yous tin utilise any directory for the CSV file however your SQL service account should have permissions to access the files

Use of escape character

In the above screenshot, we get the data from the CSV, but information technology does non display any column names. Our CSV file has the column names. To display it with R and SQL Server, y'all can use WITH Effect SETS and define columns, their t-SQL data types. In the below query, we ascertain two columns [Winter], [Total] and their data types varchar(100) and float respectively.

EXECUTE sp_execute_external_script

@ language = N 'R' ,

@ script = N '

library(rio)

CSV <- import("C://Plan Files//Microsoft SQL Server//MSSQL15.INST1//R_SERVICES//bin//WinterSnowfalls.csv")

OutputDataSet <- CSV

WITH RESULT SETS ( ( Winter varchar ( 100 ) , Total float ) )

In the query output, the T-SQL script returns the column names likewise.

get the data from the CSV

You can use the print() function in R script. It displays the column names, their values in the message tab of SSMS.

the print() function in R script

Catechumen CSVs into Excel files using R and SQL Server

Run the post-obit R scripts in SQL Server to catechumen the CSV into an excel file.

> CSV <- import("WinterSnowfalls.csv")
> OutputDataSet <- export(CSV,"WinterSnowfalls.xlsx")

Run the script in the R console client from the bin directory.

Convert the CSV into an Excel file

It converts the file into an excel file and saves it into the source file directory.

Check excel file

To execute the R script from the SQL Server, embed it into the sp_execute_external_script stored procedure. Here, y'all need to specify the path to salve the excel file.

EXECUTE sp_execute_external_script

@ language = N 'R' ,

@ script = North '

library(rio)

CSV <- import("C://Programme Files//Microsoft SQL Server//MSSQL15.INST1//R_SERVICES//bin//WinterSnowfalls.csv")

OutputDataSet <- export(CSV,"C://temp//WinterSnowfalls.xlsx")

Import an excel file using R and SQL Server

The Rio package uses the same import() function for the information import from an excel file. Information technology can read both XLS and XLSX format files.

In the previous example, nosotros converted the CSV into Excel using R scripts. Let's try to read the excel file, and information technology should give output similar to the CSV information considering we oasis't modified the contents.

EXECUTE sp_execute_external_script

@ linguistic communication = Northward 'R' ,

@ script = N '

library(rio)

Excel <- import("C://Temp//WinterSnowfalls.xlsx")

OutputDataSet <-Excel

Equally we can see beneath, information technology returned 76 rows from the excel file. You lot tin compare the output of CSV and Excel files every bit well.

Import an excel file

Similar to the example shown for CSV, yous can employ the WITH Result Set up or Print() function to display the column names also.

Import JSON files using R and SQL Server

As highlighted earlier, the rio module can piece of work with various file formats. Coffee Script Notation (JSON) is a pop format for storing log data. It is also used widely to manage the cloud infrastructure likewise.

To generate the JSON data for this article, nosotros employ Azure Data Studio. Information technology has an integrated functionality to save the T-SQL output in a JSON format.

Import JSON File

It converts the query output in JSON format, as shown below.

JSON format

Use import() part like to CSV, Excel examples and it returns the JSON data in the output of sp_execute_external_script.

EXECUTE sp_execute_external_script

@ language = N 'R' ,

@ script = N '

library(rio)

JSON <- import("C://Temp//AdventureWorks.json")

OutputDataSet <-JSON

Import the JSON file

You can export the JSON into the CSV, Excel as per your requirements. In the below R script, we consign the file and save it as AdventureWorks.xlsx

ToJSON <- import("AdventureWorks.JSON")
OutputDataSet <- export(ToJSON,"Adventureworks.xlsx")

export the JSON into the CSV

You tin validate the JSON information converted into Excel using the import() function. Hither, I specified the varchar(100) for all columns for demonstration purposes. Yous should apply the appropriate data types for every column.

three

viii

nine

xiii

fourteen

EXECUTE sp_execute_external_script

@ language = Northward 'R' ,

@ script = N '

library(rio)

Excel <- import("C://Temp//AdventureWorks.xlsx")

OutputDataSet <- Excel

WITH Event SETS ( (

[ BusinessEntityID ] varchar ( 100 )

, [ Title ] varchar ( 100 )

, [ FirstName ] varchar ( 100 )

, [ MiddleName ] varchar ( 100 )

, [ LastName ] varchar ( 100 )

, [ Suffix ] varchar ( 100 )

, [ JobTitle ] varchar ( 100 )

, [ PhoneNumber ] varchar ( 100 )

, [ PhoneNumberType ] varchar ( 100 )

, [ EmailAddress ] varchar ( 100 )

, [ EmailPromotion ] varchar ( 100 )

, [ AddressLine1 ] varchar ( 100 )

, [ AddressLine2 ] varchar ( 100 )

, [ Metropolis ] varchar ( 100 )

, [ StateProvinceName ] varchar ( 100 )

, [ PostalCode ] varchar ( 100 )

, [ CountryRegionName ] varchar ( 100 )

, [ AdditionalContactInfo ] varchar ( 100 )

) )

Import information from a compressed file using R and SQL Server

Normally, to read data from a compressed file, we have to extract information technology so read data. Now, using the Rio package in the R and SQL server, we can read information directly from a compressed file.

To prepare the data for this sit-in, right-click on the AdventureWorks.xlsx and navigate Send to-> Compressed (zipped) folder.

validate the JSON data

You can use third-political party applications such as WinZip, G-Nil to prepare the compressed file. In the below screenshot, we have a compressed (nada) file. We do not see much compressed because our source file size is minor.

compressed file

In the below R script, notation that nosotros provide input of a compressed file (C://Temp//AdventureWorks.zip)

one

two

half dozen

ten

thirteen

EXECUTE sp_execute_external_script

@ language = N 'R' ,

@ script = North '

library(rio)

Excel <- import("C://Temp//AdventureWorks.goose egg")

OutputDataSet <- Excel

WITH Upshot SETS ( (

[ BusinessEntityID ] varchar ( 100 )

, [ Title ] varchar ( 100 )

, [ FirstName ] varchar ( 100 )

, [ MiddleName ] varchar ( 100 )

, [ LastName ] varchar ( 100 )

, [ Suffix ] varchar ( 100 )

, [ JobTitle ] varchar ( 100 )

, [ PhoneNumber ] varchar ( 100 )

, [ PhoneNumberType ] varchar ( 100 )

, [ EmailAddress ] varchar ( 100 )

, [ EmailPromotion ] varchar ( 100 )

, [ AddressLine1 ] varchar ( 100 )

, [ AddressLine2 ] varchar ( 100 )

, [ City ] varchar ( 100 )

, [ StateProvinceName ] varchar ( 100 )

, [ PostalCode ] varchar ( 100 )

, [ CountryRegionName ] varchar ( 100 )

, [ AdditionalContactInfo ] varchar ( 100 )

) )

It returns a similar effect of data import directly from an excel file.

Import data from a compressed file

Import data from a CSV to SQL Server tables using RIO package

In the earlier examples, we imported data from a CSV, Excel or compressed file but did not store into SQL Server tables.

Before we import information directly into SQL Server tables, create the table with advisable information types and columns. For case, for the in a higher place case data, we create a SQL table with the following script.

two

three

five

nine

sixteen

Create table dataimportR

(

[ BusinessEntityID ] varchar ( 100 )

, [ Title ] varchar ( 100 )

, [ FirstName ] varchar ( 100 )

, [ MiddleName ] varchar ( 100 )

, [ LastName ] varchar ( 100 )

, [ Suffix ] varchar ( 100 )

, [ JobTitle ] varchar ( 100 )

, [ PhoneNumber ] varchar ( 100 )

, [ PhoneNumberType ] varchar ( 100 )

, [ EmailAddress ] varchar ( 100 )

, [ EmailPromotion ] varchar ( 100 )

, [ AddressLine1 ] varchar ( 100 )

, [ AddressLine2 ] varchar ( 100 )

, [ Metropolis ] varchar ( 100 )

, [ StateProvinceName ] varchar ( 100 )

, [ PostalCode ] varchar ( 100 )

, [ CountryRegionName ] varchar ( 100 )

, [ AdditionalContactInfo ] varchar ( 100 )

)

Now, nosotros use Insert Into statement earlier executing the sp_execute_external_script stored procedure in the R script and SQL Server.

Insert into AdventureWorks2019 . dbo . dataimportR

EXECUTE sp_execute_external_script

@ language = Northward 'R' ,

@ script = N '

library(rio)

Excel <- import("C://Temp//AdventureWorks.zip")

OutputDataSet <- Excel

Select * from AdventureWorks2019 . dbo . dataimportR

It imports information directly into the specified SQL table [DataImportR]. Y'all can utilise the Select statement to verify the information.

Import data from a CSV to SQL Server tables using R and SQL Server

Conclusion

In this article, we explored the useful Rio parcel using R and SQL Server. It tin can read data from various file formats using the import() function. Further, nosotros can export or convert data into some other format. Yous tin can directly read a compressed file and import it into the SQL Server tables.

It is an exciting feature of the machine learning language in SQL Server. I would recommend you explore it.

Author
Recent Posts

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than than 650 technical manufactures on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of 1 of the biggest free online collections of manufactures on a single topic, with his 50-role series on SQL Server Always On Availability Groups.

Based on my contribution to the SQL Server community, I have been recognized equally the prestigious All-time Author of the Year continuously in 2019, 2020, and 2021 (second Rank) at SQLShack and the MSSQLTIPS champions honor in 2020.

Personal Blog: https://world wide web.dbblogger.com
I am always interested in new challenges so if you need consulting assistance, reach me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

mcdanielyoutund.blogspot.com

Source: https://www.sqlshack.com/import-and-export-data-using-r-and-sql-server/

How to Read and Update Sql File in R

Surround requirements

Data Import and Export using SQL Automobile Learning R Scripts

Download a CSV file from the Web URL and import it

Catechumen CSVs into Excel files using R and SQL Server

Import an excel file using R and SQL Server

Import JSON files using R and SQL Server

Import information from a compressed file using R and SQL Server

Import data from a CSV to SQL Server tables using RIO package

Conclusion

0 Response to "How to Read and Update Sql File in R"

Postar um comentário

Iklan Atas Artikel

Iklan Tengah Artikel 1

Iklan Tengah Artikel 2

Iklan Bawah Artikel