Pasted

/*
    *** DO NOT USE IN PRODUCTION -- TESTING / DEMONSTRATION CODE ONLY ***
 
    This script relates to the following SQL Server Central Forum topic:
    Processing strings ( https://www.sqlservercentral.com/forums/topic/processing-strings )
 
    *** DO NOT USE IN PRODUCTION -- TESTING / DEMONSTRATION CODE ONLY ***
 
    This script provides the .NET / C# source code for the [ParseCSV] SQLCLR Stored Procedure.
 
    *** DO NOT USE IN PRODUCTION -- TESTING / DEMONSTRATION CODE ONLY ***
 
    A T-SQL installation script (no external DLL) containing only two Stored Procedures is located at:
    https://pastebin.com/aqsWiX1e
 
    *** DO NOT USE IN PRODUCTION -- TESTING / DEMONSTRATION CODE ONLY ***
 
    A T-SQL script containing tests for the [ParseCSV] SQLCLR Stored Procedure is located at:
    https://pastebin.com/WGDkQTxt
 
    *** DO NOT USE IN PRODUCTION -- TESTING / DEMONSTRATION CODE ONLY ***
 
    Date: 2016-04-11
    Version: 1.0.0
 
    For more functions like this, please visit: https://SQLsharp.com
 
    Stairway to SQLCLR series: https://www.sqlservercentral.com/stairways/stairway-to-sqlclr
 
    Copyright (c) 2016 Sql Quantum Leap. All rights reserved.
    https://SqlQuantumLeap.com
*/
 
using System;
using System.Data.SqlTypes;
using System.IO;
using Microsoft.SqlServer.Server;
using Microsoft.VisualBasic.FileIO;
 
 
public class CSVUtils
{
    [SqlProcedure()]
    public static SqlInt32 ParseCSV(
        [SqlFacet(MaxSize = 1)] SqlString Delimiter,
        [SqlFacet(MaxSize = -1)] SqlString InputString,
        [SqlFacet(MaxSize = 500)] SqlString FilePath)
    {
        if (Delimiter.IsNull)
        {
            return 1;
        }
 
        if (!FilePath.IsNull && !FilePath.Value.Trim().Equals(string.Empty))
        {
            using (TextFieldParser _CSV = new TextFieldParser(FilePath.Value))
            {
                ParseStuff(_CSV, Delimiter.Value);
            }
        }
        else
        {
            if (InputString.IsNull || InputString.Value.Trim().Equals(string.Empty))
            {
                return 0;
            }
 
            using (StringReader _Reader = new StringReader(InputString.Value))
            {
                using (TextFieldParser _CSV = new TextFieldParser(_Reader))
                {
                    ParseStuff(_CSV, Delimiter.Value);
                }
            }
        }
        return 0;
    }
 
    private static void ParseStuff(TextFieldParser CSV, string Delimiter)
    {
        bool _IsResultSchemaSet = false;
        SqlDataRecord _ResultSet = null;
        int _FieldCount = 0;
 
        CSV.SetDelimiters(Delimiter); // this also sets TextFieldType = FieldType.Delimited;
        CSV.HasFieldsEnclosedInQuotes = true;
        string[] _Fields;
 
        while (!CSV.EndOfData)
        {
            _Fields = CSV.ReadFields();
 
            if (!_IsResultSchemaSet)
            {
                _FieldCount = _Fields.Length;
                SqlMetaData[] _TempStructure = new SqlMetaData[_FieldCount];
 
                for (int _Index = 0; _Index < _FieldCount; _Index++)
                {
                    _TempStructure[_Index] = new SqlMetaData(string.Format("Field{0:D3}", _Index + 1),
                        System.Data.SqlDbType.NVarChar, SqlMetaData.Max);
                }
                _ResultSet = new SqlDataRecord(_TempStructure);
 
                SqlContext.Pipe.SendResultsStart(_ResultSet);
 
                _IsResultSchemaSet = true;
            }
 
            _ResultSet.SetValues(_Fields);
            if (_Fields.Length < _FieldCount)
            {
                for (int _Index = _Fields.Length; _Index < _FieldCount; _Index++)
                {
                    _ResultSet.SetDBNull(_Index);
                }
            }
            SqlContext.Pipe.SendResultsRow(_ResultSet);
        }
 
        SqlContext.Pipe.SendResultsEnd();
 
        return;
    }
 
    [SqlProcedure]
    public static void GarbageCollect()
    {
        GC.Collect();
 
        return;
    }
 
}