Post
Ask Question

Convert CSV To Table In SQL

One of the very common requirements in programming is the need of storing CSV data into a database table. Let's see how we can do this.
 
Let's start by creating a temporary table with two columns - ID and CSVData. ID will contain a random integer value and CSVData will contain the comma separated values. So, our SQL script will look like the following.
  1. CREATE TABLE #CsvData  
  2. (  
  3.    Id INT,  
  4.    CSVData VARCHAR(MAX)  
  5. )  
  6.   
  7. INSERT INTO #CsvData VALUES(1, 'A,B,C')  
  8. INSERT INTO #CsvData VALUES(2, 'D,E')  
Next, in order to convert these CSV values to an SQL table, we will use the following logic.
  1. Convert the CSV string to XML data.
  2. Use the XML functions in SQL to retrieve the actual data into SQL table format.
In order to convert this string to XML, we will first replace the ',' with the '</Data><Data>' tags. Then, we append the '<Data>'  and the '</Data>' tags with the replaced string. So, our SQL query with output will look like the following.
 
 
 
Now, we use the XML functions named nodes and value, along with the Cross Apply function, to get the values from XML. So, the query will change to the following.
  1. SELECT ID,  
  2. tbl.csvdata.value('.[1]','VARCHAR(MAX)')  
  3. FROM  
  4. (  
  5. Select   
  6.      Id  
  7.     ,CAST('<Data>' +  REPLACE(CSVData, ',', '</Data><Data>') + '</Data>' AS XML)  AS XmlString  
  8. FROM #CsvData  
  9. ) tdata  
  10. CROSS APPLY XmlString.nodes('/Data') tbl(csvdata)  
Execute the query and see the results.

 
 
That's it. We have the data in the SQL table format. Hope you enjoyed reading it. Happy querying...!!!