32413

How to pass String to Bulk insert instead of file?

Question:

I used to use bulk insert command to Convert a Csv file int table.Resently i saved a CSV file as a VarBinary value in sql server.Now I can get data from Varbinary file by typecasting it to Varchar using CAST and CONVERT functions.But now i got an issue i cant convert this Varchar String containing csv content to table using bulk insert.Can any one help me My example code is given below:

--@String contains varchar value of CSV file content. SET @sql = 'BULK INSERT TempCsv FROM ''' + @String + ''' WITH ( FIRSTROW = 2, FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'', TABLOCK )'

Please help me.Is there any way or alternative to insert data from csv string to table.

Answer1:

EDIT: allow multiple char separators

This is how I solved it. It involves:

<ul><li>A table-valued function (xftSplit) for spliting line breaks (char(10)) into table lines</li> <li>A scalar function (fSubstrNth) for extracting the n-th field of a line, given an separator</li> <li>A scalar function (fPatIndexMulti) for finding the n-th index of the separator</li> <li>(Optional) alternative Right function to accept negative values</li> <li>Finally, some specific code to use in your solution, since SQL doesn't allow dynamic table-function definitions (in other words, you can't SELECT from a function with dynamic columns)</li> </ul>

Now, for the code snippets:

<strong>xftSplit</strong>

-- ============================================= -- Author: Bernardo A. Dal Corno -- Create date: 15/07/2014 -- Description: Quebra valores a partir de caracteres e retorna lista em tabela -- ============================================= CREATE FUNCTION [dbo].[xftSplit] ( @Texto varchar(max), @Splitter varchar(3) ) RETURNS @Lista TABLE ( ValoresQuebrados varchar(8000) ) AS BEGIN DECLARE @Pos Smallint While len(@Texto)>0 BEGIN SET @Pos = Patindex('%'+@Splitter+'%',@Texto) IF @Pos > 0 BEGIN INSERT INTO @Lista SELECT left(@Texto, @Pos-1) SET @Texto = right(@Texto, len(@Texto)-@Pos) END ELSE BEGIN INSERT INTO @Lista SELECT @Texto SET @Texto = '' END End RETURN END

<strong>fSubstrNth</strong>

-- ============================================= -- Author: Bernardo A. Dal Corno -- Create date: 18/07/2017 -- Description: substring com 2 PatIndex limitando inicio e fim -- ============================================= CREATE FUNCTION fSubstrNth ( @Text varchar(max), @Sep varchar(3), @N int --Nth campo ) RETURNS varchar(max) AS BEGIN DECLARE @Result varchar(max) IF @N<1 RETURN '' IF @N=1 SET @Result = substring(@Text, 1, dbo.fPatIndexMulti(@Sep,@Text,1)-1) ELSE SET @Result = substring(@Text, dbo.fPatIndexMulti(@Sep,@Text,@N-1)+LEN(@Sep), CASE WHEN dbo.fPatIndexMulti(@Sep,@Text,@N)>0 THEN dbo.fPatIndexMulti(@Sep,@Text,@N)-dbo.fPatIndexMulti(@Sep,@Text,@N-1)-LEN(@Sep) ELSE LEN(@Text)+1 END) RETURN @Result END

<strong>fPatIndexMulti</strong>

-- ============================================= -- Author: Bernardo A. Dal Corno -- Create date: 17/07/2017 -- Description: recursive patIndex -- ============================================= CREATE FUNCTION [dbo].[fPatIndexMulti] ( @Find varchar(max), @In varchar(max), @N tinyint ) RETURNS int AS BEGIN DECLARE @lenFind int, @Result int, @Texto varchar(max), @index int DECLARE @i tinyint=1 SET @lenFind = LEN(@Find)-1 SET @Result = 0 SET @Texto = @In WHILE (@i <= @N) BEGIN SET @index = patindex('%'+@Find+'%',@Texto) IF @index = 0 RETURN 0 SET @Result = @Result + @index SET @Texto = dbo.xRight(@Texto, (@index + @lenFind)*-1) SET @i = @i + 1 END SET @Result = @Result + @lenFind*(@i-2) RETURN @Result END

<strong>xRight</strong>

-- ============================================= -- Author: Bernardo A. Dal Corno -- Create date: 06/01/2015 -- Description: Right inverso (para nros < 0) -- ============================================= CREATE FUNCTION [dbo].[xRight] ( @Texto varchar(8000), @Qntd int ) RETURNS varchar(8000) AS BEGIN DECLARE @Result varchar(8000) IF (Len(@Texto) = 0) OR (@Qntd = 0) SET @Result = '' ELSE IF (@Qntd > 0) SET @Result = Right(@Texto, @Qntd) ELSE IF (@Qntd < 0) SET @Result = Right(@Texto, Len(@Texto) + @Qntd) RETURN @Result END

<strong>Specific code</strong>

SELECT acolumn = 'any value', field1 = dbo.fSubstrNth(line,',',1), field2 = dbo.fSubstrNth(line,',',2), anothercolumn = 'set your query as you would normally do', field3 = (CASE dbo.fSubstrNth(line,',',3) WHEN 'C' THEN 1 ELSE 0 END) FROM ( SELECT line = ValoresQuebrados FROM dbo.xftSplit(@StringVariable, char(10)) ) lines

Note that:

<ul><li>fSubstrNth receives the n-th field to extract from the line</li> <li>xftSplit receives a variable that contains the string you want to bulk from (whatever the source) and a char(10) as the splitter of \n, but it could be anything else</li> <li>The query can be as any other. This means it can be stored in a procedure, tabled-function, view, etc. You can extract some or all fields, in any order you wish, and process however you want</li> <li>If used in a stored procedure, you could create a generic way of creating a query and temp table that loads the string with dynamic columns, but you have to make a call to another procedure to use the data OR create a specific query like above in the same procedure (which would make it non-generic, just more reusable)</li> </ul>

Recommend

  • Using indexPath.row to get an object from an array
  • How can I exclude one html view in whole project to be applied from CSS?
  • Find all elements that have a certain data attribute (regardless of value)
  • Cannot set password with DirectoryEntry.Invoke when user is created in AD using ASP.NET C#
  • SQL server command to create server alias?
  • Why a class has only one destructor? [duplicate]
  • applying background to first, and every third row in a dynamically generated table
  • jQuery Tipsy won't work with jQuery.each() and live:true
  • Scala Play form validation: different forms for one case class - is it possible?
  • How to make a post call to a Web Api Action?
  • How to use Eclipse Mars to connect to Subversion
  • iText as text Extracting/Reading from PDF on android
  • how to know the last row filled in vba (excel)? [duplicate]
  • Get the negative of an Optional Chain
  • substitute period from abbreviation (single letter + period) unless followed by a capital letter
  • Cannot update firebase version [duplicate]
  • Crafting a LINQ based solution to determine if a set of predicates are satisfied for a pair of colle
  • Packet modification with netfilter queue?
  • jquery full calendar
  • Multiple flexboxes with margin-right, except the last one in the row? Without JS?
  • Pythons argparse default value doesn't work
  • Cypher - matching two different possible paths and return both
  • Shouldn't else be indented in the below code
  • Yii2: Finding file and getting path in a directory tree
  • Clear activity stack before launching another activity
  • Scipy Leastsq Optional Output Variable (Mesg)
  • UWP/C# - Issue with AQS and USB Devices
  • How to make JSON.NET deserialize to Microsoft Date Time?
  • Angular2 Response for preflight is invalid (redirect) from some GET requests
  • Create Instant using a negative year
  • How do I configure context broker accept post requests from my remote sensor?
  • Unable to decode certificate at client new X509Certificate2()
  • one Local Olampyad Questions on Informatic in 2011
  • ilmerge with a PFX file
  • Retrieving value from sql ExecuteScalar()
  • Finding past revisions of files in StarTeam w/ .NET SDK / C#
  • Alternatives to the OPTIONAL fallback SPARQL pattern?
  • KeystoneJS: Relationships in Admin UI not updating
  • Hits per day in Google Big Query
  • Checking variable from a different class in C#