Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /*
- *** 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;
- }
- }
Add Comment
Please, Sign In to add comment