Click on the banner to learn about and purchase my database training on Azure

SQL Server - How to export and import tabular data files (Ex: CSV) using CLR (C #)

Views: 2.379
Reading Time: 7 minutes

Hello guys.
Hope all is well with you.

In this post, I would like to demonstrate a very interesting resource used in the daily lives of those who create integration routines between systems using files with tabular data, that is, text files that use a delimiter to separate the information in “columns”, as for example, the CSV (Comma-Separated Values) file type. To assist you in this matter, I will simplify this process by showing you how to export and import tabular files, including CSV files.

To do this, I will use the CLR feature, which allows you to create code written in the C # programming language and take advantage of various Microsoft .NET Framework features within SQL Server, ie you create code using the C # programming language in Visual Studio, as if you were creating an application, but the result is procedures and functions that are executed by Transact-SQL commands within SQL Server.

Want to know more about this powerful feature of SQL Server? Access the post Introduction to SQL Common Language Runtime (CLR) in SQL Server.

Talking about this post, I had already created a solution to import CSV files using Transact-SQL and OLE Automation, in the post Importing CSV Files into the SQL Server Database and decided to demonstrate this solution using another technology, more practical and performative.

Prerequisites for using Procedures

As prerequisites for using the procedures below, you will need to create this .cs file, which contains the classes employee, CurrentServer e ROI.

How to export a table or query to CSV file

To facilitate the export of SQL Server data to CSV files, I will provide a Stored Procedure (written in C #) to be used in CLR, which allows you to execute a query and export the result to text files with tabular data. You can specify the delimiter character and define whether or not the resulting query header is exported.

View source

Examples of use

Using separator “;”, header, UTF-8 encoding WITHOUT BOM and Unix line break (LF)

Result:

Using the “|” tab and without header, ISO-8859-1 encoding and Windows line break (CR + LF)

Result:

How to import a CSV file into the database

After demonstrating how to export database data to files, I will show you how to do the reverse path. Using the stpImporta_CSV Stored Procedure, you can import delimited files into the database in table form.

View source

Examples of use

Importing file data into a table, with no column indication, with a “|” separator

Sample file:

Result:

Importing the file, returning a SELECT, skipping a few lines and heading

Sample file:

Result:

That's it folks!
I hope you enjoyed this tip.

A hug!

sql server clr how to import export save import export file csv files pipe delimited files tabular data

sql server clr how to import export save import export file csv files pipe delimited files tabular data