I use a streaming table-valued function for this sort of thing:
select b.A as FirstName
, b.C as LastName
, Cast(b.Q as date) as DateOfBirth
from <FileTable> as a
cross apply dbo.ReadDelimited(
a.file_stream
, @delimitChars
, @escapeChars
, @newLineChars
, @encoding
, default
, default
) as b
where a.Name = @fileName
ReadDelimited will return an Excel-style result set of columns A:CZ. We then select only columns A, C, and Q and add proper typing and aliasing using standard T-SQL. If your data contains a header then you can skip it using Row_Number(). The example also demos how you can easily combine the STVF with Sql Server's FileTables feature.
In your case you'd want to set @delimitChars = ';' and @newLineChars = ','.
C# CLR Code:
using Microsoft.SqlServer.Server;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlTypes;
using System.IO;
using System.Text;
public partial class ReadDelimitedFunctions
{
private const int MAX_COLUMN_COUNT = 26 * 4;
[SqlFunction(
DataAccess = DataAccessKind.None,
FillRowMethodName = "ReadDelimited_FillRow",
IsDeterministic = true,
IsPrecise = true,
SystemDataAccess = SystemDataAccessKind.None,
TableDefinition =
@"A nvarchar(4000), B nvarchar(4000), C nvarchar(4000), D nvarchar(4000), E nvarchar(4000), F nvarchar(4000)
, G nvarchar(4000), H nvarchar(4000), I nvarchar(4000), J nvarchar(4000), K nvarchar(4000), L nvarchar(4000)
, M nvarchar(4000), N nvarchar(4000), O nvarchar(4000), P nvarchar(4000), Q nvarchar(4000), R nvarchar(4000)
, S nvarchar(4000), T nvarchar(4000), U nvarchar(4000), V nvarchar(4000), W nvarchar(4000), X nvarchar(4000)
, Y nvarchar(4000), Z nvarchar(4000), AA nvarchar(4000), AB nvarchar(4000), AC nvarchar(4000), AD nvarchar(4000)
, AE nvarchar(4000), AF nvarchar(4000), AG nvarchar(4000), AH nvarchar(4000), AI nvarchar(4000), AJ nvarchar(4000)
, AK nvarchar(4000), AL nvarchar(4000), AM nvarchar(4000), AN nvarchar(4000), AO nvarchar(4000), AP nvarchar(4000)
, AQ nvarchar(4000), AR nvarchar(4000), [AS] nvarchar(4000), AT nvarchar(4000), AU nvarchar(4000), AV nvarchar(4000)
, AW nvarchar(4000), AX nvarchar(4000), AY nvarchar(4000), AZ nvarchar(4000), BA nvarchar(4000), BB nvarchar(4000)
, BC nvarchar(4000), BD nvarchar(4000), BE nvarchar(4000), BF nvarchar(4000), BG nvarchar(4000), BH nvarchar(4000)
, BI nvarchar(4000), BJ nvarchar(4000), BK nvarchar(4000), BL nvarchar(4000), BM nvarchar(4000), BN nvarchar(4000)
, BO nvarchar(4000), BP nvarchar(4000), BQ nvarchar(4000), BR nvarchar(4000), BS nvarchar(4000), BT nvarchar(4000)
, BU nvarchar(4000), BV nvarchar(4000), BW nvarchar(4000), BX nvarchar(4000), [BY] nvarchar(4000), BZ nvarchar(4000)
, CA nvarchar(4000), CB nvarchar(4000), CC nvarchar(4000), CD nvarchar(4000), CE nvarchar(4000), CF nvarchar(4000)
, CG nvarchar(4000), CH nvarchar(4000), CI nvarchar(4000), CJ nvarchar(4000), CK nvarchar(4000), CL nvarchar(4000)
, CM nvarchar(4000), CN nvarchar(4000), CO nvarchar(4000), CP nvarchar(4000), CQ nvarchar(4000), CR nvarchar(4000)
, CS nvarchar(4000), CT nvarchar(4000), CU nvarchar(4000), CV nvarchar(4000), CW nvarchar(4000), CX nvarchar(4000)
, CY nvarchar(4000), CZ nvarchar(4000)"
)]
public static IEnumerable ReadDelimited(SqlBytes bytes, SqlChars delimitChars, SqlChars escapeChars, SqlChars newLineChars, SqlChars encodingName, SqlInt32 columnCount, SqlInt32 bufferSize) {
if (delimitChars.IsNull) { delimitChars = new SqlChars(","); }
if (escapeChars.IsNull) { escapeChars = new SqlChars("\""); }
if (newLineChars.IsNull) { newLineChars = new SqlChars("\n"); }
if (encodingName.IsNull) { encodingName = new SqlChars("utf-8"); }
if (columnCount.IsNull) { columnCount = MAX_COLUMN_COUNT; }
else if (columnCount.Value > MAX_COLUMN_COUNT) { throw new IndexOutOfRangeException(string.Format("ReadDelimited supports a maximum of {0} columns.", MAX_COLUMN_COUNT)); }
if (bufferSize.IsNull) { bufferSize = 4096; }
return ReadDelimited_Parse(bytes.Stream, delimitChars.Value, escapeChars.Value, newLineChars.Value, new string(encodingName.Value), columnCount.Value, bufferSize.Value);
}
private static IEnumerable<SqlChars[]> ReadDelimited_Parse(Stream stream, char[] delimitChars, char[] escapeChars, char[] newLineChars, string encodingName, int columnCount, int bufferSize = 4096) {
var encoding = Encoding.GetEncoding(encodingName);
var builder = new StringBuilder();
var rawBuffer = new byte[bufferSize];
var charBuffer = new char[bufferSize];
var result = new SqlChars[MAX_COLUMN_COUNT];
var escaping = false;
var escapeIndex = 0;
var delimitIndex = 0;
var columnIndex = 0;
var rowIndex = 0;
while (stream.Read(rawBuffer, 0, bufferSize) > 0) { // while there are bytes to read...
encoding.GetChars(rawBuffer, 0, bufferSize, charBuffer, 0); // decode rawBuffer into charBuffer
Array.Clear(rawBuffer, 0, bufferSize); // reset rawBuffer
for (var i = 0; i < bufferSize; i++) { // process each character in the buffer
builder.Append(charBuffer[i]); // capture the current character in the builder
if (charBuffer[i] == escapeChars[escapeIndex] && (++escapeIndex == escapeChars.Length)) { // toggle escaping
escapeIndex = 0;
escaping = !escaping;
builder.Remove(builder.Length - escapeChars.Length, escapeChars.Length);
}
if (!escaping) {
if (columnIndex < columnCount && charBuffer[i] == delimitChars[delimitIndex] && (++delimitIndex == delimitChars.Length)) { // process columns
delimitIndex = 0;
result[columnIndex++] = new SqlChars(builder.ToString(0, builder.Length - delimitChars.Length));
builder.Clear();
}
if (charBuffer[i] == newLineChars[rowIndex] && (++rowIndex == newLineChars.Length)) { // process rows
if (columnIndex < columnCount) { // process last column
result[columnIndex++] = new SqlChars(builder.ToString(0, builder.Length - newLineChars.Length));
}
while (columnIndex < MAX_COLUMN_COUNT) { // null-extend columns
result[columnIndex++] = SqlChars.Null;
}
yield return result; // return current row
// reset variables for next row
builder.Clear();
rowIndex = 0;
columnIndex = 0;
Array.Clear(result, 0, MAX_COLUMN_COUNT);
}
}
}
}
// process last row
var s = builder.ToString();
result[columnIndex++] = string.IsNullOrEmpty(s) ? SqlChars.Null : new SqlChars(s); // process last column
if (result[1] != null) { // skip last row if empty
while (columnIndex < MAX_COLUMN_COUNT) { // null-extend columns
result[columnIndex++] = SqlChars.Null;
}
yield return result;
}
}
private static void ReadDelimited_FillRow(object obj
, out SqlChars A, out SqlChars B, out SqlChars C, out SqlChars D, out SqlChars E, out SqlChars F
, out SqlChars G, out SqlChars H, out SqlChars I, out SqlChars J, out SqlChars K, out SqlChars L
, out SqlChars M, out SqlChars N, out SqlChars O, out SqlChars P, out SqlChars Q, out SqlChars R
, out SqlChars S, out SqlChars T, out SqlChars U, out SqlChars V, out SqlChars W, out SqlChars X
, out SqlChars Y, out SqlChars Z, out SqlChars AA, out SqlChars AB, out SqlChars AC, out SqlChars AD
, out SqlChars AE, out SqlChars AF, out SqlChars AG, out SqlChars AH, out SqlChars AI, out SqlChars AJ
, out SqlChars AK, out SqlChars AL, out SqlChars AM, out SqlChars AN, out SqlChars AO, out SqlChars AP
, out SqlChars AQ, out SqlChars AR, out SqlChars AS, out SqlChars AT, out SqlChars AU, out SqlChars AV
, out SqlChars AW, out SqlChars AX, out SqlChars AY, out SqlChars AZ, out SqlChars BA, out SqlChars BB
, out SqlChars BC, out SqlChars BD, out SqlChars BE, out SqlChars BF, out SqlChars BG, out SqlChars BH
, out SqlChars BI, out SqlChars BJ, out SqlChars BK, out SqlChars BL, out SqlChars BM, out SqlChars BN
, out SqlChars BO, out SqlChars BP, out SqlChars BQ, out SqlChars BR, out SqlChars BS, out SqlChars BT
, out SqlChars BU, out SqlChars BV, out SqlChars BW, out SqlChars BX, out SqlChars BY, out SqlChars BZ
, out SqlChars CA, out SqlChars CB, out SqlChars CC, out SqlChars CD, out SqlChars CE, out SqlChars CF
, out SqlChars CG, out SqlChars CH, out SqlChars CI, out SqlChars CJ, out SqlChars CK, out SqlChars CL
, out SqlChars CM, out SqlChars CN, out SqlChars CO, out SqlChars CP, out SqlChars CQ, out SqlChars CR
, out SqlChars CS, out SqlChars CT, out SqlChars CU, out SqlChars CV, out SqlChars CW, out SqlChars CX
, out SqlChars CY, out SqlChars CZ
) {
var fields = (SqlChars[])obj;
A = fields[0]; B = fields[1]; C = fields[2]; D = fields[3]; E = fields[4]; F = fields[5];
G = fields[6]; H = fields[7]; I = fields[8]; J = fields[9]; K = fields[10]; L = fields[11];
M = fields[12]; N = fields[13]; O = fields[14]; P = fields[15]; Q = fields[16]; R = fields[17];
S = fields[18]; T = fields[19]; U = fields[20]; V = fields[21]; W = fields[22]; X = fields[23];
Y = fields[24]; Z = fields[25]; AA = fields[26]; AB = fields[27]; AC = fields[28]; AD = fields[29];
AE = fields[30]; AF = fields[31]; AG = fields[32]; AH = fields[33]; AI = fields[34]; AJ = fields[35];
AK = fields[36]; AL = fields[37]; AM = fields[38]; AN = fields[39]; AO = fields[40]; AP = fields[41];
AQ = fields[42]; AR = fields[43]; AS = fields[44]; AT = fields[45]; AU = fields[46]; AV = fields[47];
AW = fields[48]; AX = fields[49]; AY = fields[50]; AZ = fields[51]; BA = fields[52]; BB = fields[53];
BC = fields[54]; BD = fields[55]; BE = fields[56]; BF = fields[57]; BG = fields[58]; BH = fields[59];
BI = fields[60]; BJ = fields[61]; BK = fields[62]; BL = fields[63]; BM = fields[64]; BN = fields[65];
BO = fields[66]; BP = fields[67]; BQ = fields[68]; BR = fields[69]; BS = fields[70]; BT = fields[71];
BU = fields[72]; BV = fields[73]; BW = fields[74]; BX = fields[75]; BY = fields[76]; BZ = fields[77];
CA = fields[78]; CB = fields[79]; CC = fields[80]; CD = fields[81]; CE = fields[82]; CF = fields[83];
CG = fields[84]; CH = fields[85]; CI = fields[86]; CJ = fields[87]; CK = fields[88]; CL = fields[89];
CM = fields[90]; CN = fields[91]; CO = fields[92]; CP = fields[93]; CQ = fields[94]; CR = fields[95];
CS = fields[96]; CT = fields[97]; CU = fields[98]; CV = fields[99]; CW = fields[100]; CX = fields[101];
CY = fields[102]; CZ = fields[103];
}
}
