Skip to main content

Posts

Showing posts from November, 2008

SQL 2005 - Easiest Way To Convert CSV To Table

Here is a function that will easily let you convert from a CSV (you can modify it to be ;sv or -sv... you get the idea!) to a Table using SQL's Table-valued Functions. SQL Function: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go CREATE FUNCTION [dbo].[charlist_to_table] (@list ntext, @delimiter char(1) = N',') RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL, str varchar(4000), nstr varchar(2000)) AS BEGIN DECLARE @pos int, @textpos int, @chunklen smallint, @tmpstr varchar(4000), @leftover varchar(4000), @tmpval varchar(4000) SET @textpos = 1 SET @leftover = '' WHILE @textpos <= datalength(@list) / 2 BEGIN SET @chunklen = 4000 - datalength(@leftover) / 2 SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen) SET @textpos = @textpos + @chunklen SET @pos = charindex(@delimiter, @tmpstr) WHILE @pos > 0 BEGIN SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1))) INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval) SET @tmpstr = substring(@tmpstr, @pos + 1