{"id":1321,"date":"2012-04-13T00:00:00","date_gmt":"2012-04-13T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/the-tsql-of-csv-comma-delimited-of-errors\/"},"modified":"2026-04-14T17:20:13","modified_gmt":"2026-04-14T17:20:13","slug":"the-tsql-of-csv-comma-delimited-of-errors","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/the-tsql-of-csv-comma-delimited-of-errors\/","title":{"rendered":"Working with CSV Files in SQL Server: ODBC, Linked Server, and T-SQL Parsing"},"content":{"rendered":"
\n

Executive Summary<\/h2>\n

SQL Server can read and write CSV files using three main approaches: the ODBC Text Driver (simplest – treats the CSV file as a queryable data source), a linked server (most flexible – allows CSV data to be queried like any other linked database), and a pure T-SQL CSV parser (no external dependencies – useful in restricted environments). Each approach has different performance and flexibility trade-offs. This article covers all three with working T-SQL code, explains the underappreciated nuances of the CSV format specification, and reviews the common errors that arise from treating CSV as simpler than it is.<\/strong><\/p>\n

Introduction<\/h2>\n

This article is about using ‘Comma-Separated Values’ (CSV) format in SQL Server.\u00a0 You’ll come across CSV if you’re\u00a0 a SQL Server developer when you are importing, or exporting, tabular data from ‘foreign’ applications, data feeds, or the wilder fringe of databases. Exporting via CSV is useful, since CSV is generally supported by even the most archaic legacy systems. We’ll\u00a0 demonstrate a few tricks such as that of saving the tables and views of AdventureWorks to CSV format, parsing a CSV file to a table, converting CSV\u00a0 to XML and JSON and so on.<\/p>\n

Although we’ll cover some of the many ways of manipulating CSV in this article, we’ll be concentrating more on using ODBC, since it is pretty fast, versatile, and reliable, and is easy to access from TSQL.<\/p>\n

The CSV Format<\/h2>\n

There is nothing much wrong with the CSV file format, beyond the neglect it suffers. It’s been around far longer than MSDOS. When properly implemented, It is the most efficient available way of representing the contents of a data table in a readable document, and is therefore very effective in transferring such data between different applications or platforms. Unlike JSON or XML, it is designed for the single purpose of representing tabular data. It doesn’t do hierarchical data, and it was designed assuming that both ends of the data transfer process understood what each column represented, as well as the data type, collation and character set being used.\u00a0 If it is done properly, it can transfer any type of tabular data reliably.<\/p>\n

We come across CSV files in SSIS, Excel, ODBC, SQLCMD and even in PowerShell.\u00a0 A Faulty implementation, nicknamed ‘comedy-limited’, exists in BCP. The term ‘CSV format’ is not the correct description of the practice of using commas or other ‘special’ characters,\u00a0 to separate data elements in some lists, this generally applies only to ‘private’ data where there is a guarantee that the delimiting character does not appear within the data.<\/p>\n

If CSV transfers fail, it is generally because of mistakes in the implementation.\u00a0RFC 4180<\/a> gives a good description of what it is supposed to do. It is easy to describe:<\/p>\n

Each row, or tuple, is separated by a linefeed, and the last line can be terminated by a linefeed. Each line should contain the same number of fields, which are separated by a single character, usually a comma. Fields may be enclosed in double-quote characters. If they are, then fields may contain commas or linefeed characters. They can also contain double-quote characters if ‘escaped’ with a second adjacent double-quote character. Null data values are denoted by two delimiters in a row with no data between them. Character strings in a delimited text line can be enclosed in double quotation marks (“”). No blanks can occur before or after delimited values<\/p>\n

The first line of the file may be a header line which contains the name of each column. There may be an initial ‘comment’ line initiated by a hash (#) character, and this is either terminated by a linefeed character or, if followed by a double-quote, until a subsequent unescaped\u00a0 double-quote, followed by a linefeed character.<\/p>\n

CSV is unusual in that it encloses the entire field in double quotes if it contains the separator. If the field contains double quotes, it must be ‘escaped’ by a second double quotes so that all double quotes in the field are repeated twice to indicate that they don’t end the field. However, If\u00a0 a repeated double-quote are the only two characters within the field, this indicates a field containing an empty string, which is quite different from NULL, signifying an unknown value.<\/p>\n

Because the rules are underspecified, different implementations diverge in their handling of edge cases. You will find in some products that continuation lines are supported by starting the last field of the line with an un-terminated double-quote. Microsoft, surprisingly, sometimes has incompatible versions of CSV files between its own applications, and has even had incompatible CSV files in different versions of the same application<\/p>\n

There is a good formal grammar for producing a CSV reader or writer here<\/a>.<\/p>\n

A quick tour of some of the alternatives<\/h2>\n

You are generally struck with one of four alternative requirements:<\/p>\n