Import csv or text file into SQL Server table

In this post, let us see how to import csv or text file into SQL Server table. If you want to import some random files on adhoc basis then we can make use of Import Flat File Wizard from SQL Server Management Studio.

If the requirement is to do on regular basis or import multiple files simultaneously then we can do this using t-sql script that executes R script to read an csv or text file & load into SQL Server table.

In my previous post on SQL Server export adhoc SQL or table to CSV file, I have exported few csv files. I am going to make use of those three csv files for this example.

SQL Server export adhoc SQL or table to CSV file

In this post, let us see how to export adhoc sql query output or list of tables, views data into csv file. I am going to make use of R script to do this.

I have tried this example with SQL Server 2019 & WideWorldImporters sample database.

For this approach, I have created a scalar function and Stored procedure.

Scalar function - returns a unique table name for a session. This function is required only if adhoc sql query has to be exported to csv file.

Stored procedure - Has below three input parameters and writes the output to csv file
i)   @ExportPath - path for exporting csv file
ii)  @ObjectlisttoExport - list of tables, views to be exported
iii) @Objectlistdelim  - delimiter for above object list

SQL Server Export/Import excel using R script

In this post, let us see new approach to import excel into SQL Server and export SQL server data to excel. There are some existing methods to do this using BCP, Bulk Insert, Import & Export wizard from SSMS, SSIS, Azure data factory, Linked server & OPENROWSET query and SQLCMD.

BULK INSERT statement, the BCP tool, or Azure Data Factory can't read Excel files directly
BCP - Work around has to be done to include the header  
SSIS - With dynamic source & destination, handling mapping increases the complexity of the package
SQLCMD - Cannot export output in Excel

R & Python language extension was introduced in SQL Server 2016 & 2017 as part of machine learning. With support of R in Azure SQL database and Java language extension support in SQL Server 2019, this new approach can be used extensively as it easy, fast and flexible.