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

Views: 1.897
Reading Time: 7 minutes

Hello guys.
Hope all is well with you.

In this post, I would like to demonstrate a very interesting and commonly used feature of those who create integration routines between systems using files with tabular data, that is, text files that use a delimiter to separate information into “columns”, such as for example, the CSV (Comma-Separated Values) file type. To help you with this, 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 Server, 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 good and Unix (LF) line break

Result:

Using “|” separator and no 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 archive data into a table, with no column indication, with 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