As a developer we may need to convert csv to sql data table every now and then. There are many methods to implement it. In others article of mind, I did shared you how to generate csv form table by using stored procedure. Now, I would like to share you how to retrieve table data from csv text by using stored procedure.

We need to create one 'Table-valued Function' and one 'Stored Procedure' in sql server. Here is the code.


CREATE FUNCTION [dbo].[fn_split]
(
      @FullText varchar(Max),
      @Delimiter varchar(5) = ','
)
RETURNS @RtnValue table
(
      Id int identity(1,1),
      Data nvarchar(max)
)
AS
BEGIN
      DECLARE @Cnt INT
      SET @Cnt = 1

      WHILE (CHARINDEX(@Delimiter,@FullText)>0)
      BEGIN
            INSERT INTO @RtnValue (data)
            SELECT
                  Data = LTRIM(RTRIM(SUBSTRING(@FullText,1,CHARINDEX(@Delimiter,@FullText)-1)))

            SET @FullText = SUBSTRING(@FullText,CHARINDEX(@Delimiter,@FullText)+1,LEN(@FullText))
            SET @Cnt = @Cnt + 1
      END

      INSERT INTO @RtnValue (data)
      SELECT Data = LTRIM(RTRIM(@FullText))

      RETURN
END
That function will return single line of csv to data table. * I got that function from the web long time ago and I didn't remember who is the original author. I would like to say thanks to him. :)
CREATE PROCEDURE [dbo].[sp_csv_to_table]
(
	@CSV VARCHAR(MAX),
	@RowSeparator VARCHAR(5)='',
	@ColSeparator VARCHAR(5)=',',
	@FirstRowAsHeader BIT= 0,
	@RemoveDoubleQuote BIT= 0
)
AS
BEGIN

DECLARE @Sql NVARCHAR(MAX)
DECLARE @Headers VARCHAR(MAX)
DECLARE @Cols VARCHAR(MAX)

IF(@RowSeparator = '')
	SET @RowSeparator = CHAR(13) + CHAR(10)

SET @Cols = ''
SET @Headers = (SELECT TOP 1 Data FROM dbo.fn_Split(@CSV,@RowSeparator))

SELECT @[email protected] +
	CASE @RemoveDoubleQuote WHEN 0 THEN
	'(SELECT Data FROM dbo.fn_Split(R.Data,''' + @ColSeparator + ''') WHERE ID = ' + CAST(C.ID AS VARCHAR) + ') AS ' + CASE @FirstRowAsHeader WHEN 0 THEN 'COL' + CAST(C.ID AS VARCHAR) ELSE C.Data END + ','
	ELSE
	'(SELECT CASE LEFT(Data,1)+ RIGHT(Data,1) WHEN ''""'' THEN SUBSTRING(Data,2,LEN(Data)-2) ELSE Data END FROM dbo.fn_Split(R.Data,''' + @ColSeparator + ''') WHERE ID = ' + CAST(C.ID AS VARCHAR) + ') AS ' + CASE @FirstRowAsHeader WHEN 0 THEN 'COL' + CAST(C.ID AS VARCHAR) ELSE C.Data END + ','
	END
FROM (SELECT ID,Data FROM dbo.fn_Split(@Headers,@ColSeparator)) AS C

SET @Cols =  SUBSTRING(@Cols,0,LEN(@Cols))

SET @Sql = 'SELECT ' + @Cols + ' FROM dbo.fn_Split(''' + @CSV + ''',''' + @RowSeparator + ''') AS R'

IF(@FirstRowAsHeader=1)
	SET @Sql = @Sql + ' WHERE R.ID > 1'

EXEC SP_EXECUTESQL @Sql

END
There are five parameters in that stored procedure. But just the first one is compulsory. All others are optional.
  • @CSV VARCHAR(MAX),
  • @RowSeparator VARCHAR(5)='',
  • @ColSeparator VARCHAR(5)=',',
  • @FirstRowAsHeader BIT= 0,
  • @RemoveDoubleQuote BIT= 0
All parameters have user friendly and self-defined name. So, I think I may not need to define each one. Just try it out.
exec [dbo].[sp_csv_to_table] '"COL A","COL B","COL C"|"d","e","f"|"g","h","i"','|',',',1,1
Output
COL A COL B COL C
d e f
g h i

Thanks for reading and have fun coding.



author's photo

Author : Blue Cloud

Just a normal software developer who love to write code. Developing professional software since 1999. After working in the software industry for many years, I've started my own website to share knowledges and experiences.

COMMENTS
blog comments powered by Disqus