Help with Bae64Encode CSV to SQL Server table

Question

Hi Experts, i would like to get help on solving a issue.Kindly please help.

1. I have a CSV file which I downloaded using SSIS from an endpoint URL
2. that file has 4 columns and 1 column row which I matter is File column with base64 encoded value
3. I was able to dump that column to SQL SERVER table using ssis
4. then used below to decode using t SQL

SELECT
CONVERT
(
VARCHAR(MAX),
CAST('' AS XML).value('xs:base64Binary(sql:column("FILE"))', 'VARBINARY(MAX)')
) AS RESULT

FROM ABC

5. the o/p I have a csv data which looks like
   Agentid,agentname, phone, email, address,state,country, zip,ssn 1,nick,56677700987,abc@réalisations .com,address,tx,12345,9990009999 2,Jack,12677700987,abcuu@réalisations .com,address,tx,145,12990009999 3,mike,56677700987,abcd@réalisations .com,address,tx,12345,9990009999,

something like that as 1 ROW .
but when I copy and paste in csv or ssms I see
Agentid,agentname, phone, email, address,state,country, zip,ssn
1,nick,56677700987,abc@réalisations .com,address,tx,12345,9990009999
2,Jack,12677700987,abcuu@réalisations .com,address,tx,145,12990009999
3,mike,56677700987,abcd@réalisations .com,address,tx,12345,9990009999

now, what I would like to do take /convert do some t SQL magic with function and insert this same row into a new readable table( as 1 row as column and then all as rows)

kindly please help:
any help?

Answer 1

This should work:

 CREATE FUNCTION [dbo].[split](  
          @delimited NVARCHAR(MAX),  
          @delimiter NVARCHAR(100)  
        ) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))  
        AS  
        BEGIN  
          DECLARE @xml XML  
          SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'  
  
          INSERT INTO @t(val)  
          SELECT  r.value('.','nvarchar(MAX)') as item  
          FROM  @xml.nodes('/t') as records(r)  
          RETURN  
        END  
GO  
   
 declare @csv as varchar(max) =  
 'Agentid,agentname, phone, email, address,state,country, zip,ssn  
 1,nick,56677700987,abc@yahoo.com,address,tx,USA,12345,9990009999  
 2,Jack,12677700987,abcuu@yahoo.com,address,tx,USA,145,12990009999  
 3,mike,56677700987,abcd@yahoo.com,address,tx,USA,12345,9990009999'  
      
  
SELECT   
	Row_Num,  
	[1] as [Agentid],  
	[2] as [agentname],  
	[3] as [phone],  
	[4] as [email],  
	[5] as [address],  
	[6] as [state],  
	[7] as [country],  
	[8] as [zip],  
	[9] as [ssn]  
FROM (  
SELECT   
	r.id as [Row_Num],  
	c.id as [ColNum],  
	LTRIM(RTRIM(c.val)) as [ColValue]  
FROM dbo.split(@csv,CHAR(13)+CHAR(10)) as r  
	CROSS APPLY dbo.split(r.[val],',') as c  
WHERE r.id <> 1  
) as src  
PIVOT  
(  
	MAX(ColValue) FOR ColNum IN ([1],[2],[3],[4],[5],[6],[7],[8],[9])  
) as pvt  

You cannot use the string_split() function because it does not return the position of the fields.

Answer 2

Hi @NickK ,

Welcome to Microsoft Q&A!

Please have a try with below and check whether it is helpful.

declare @sql VARCHAR(MAX)  
  
SELECT @sql=  
CONVERT  
(  
VARCHAR(MAX),  
CAST('' AS XML).value('xs:base64Binary(sql:column("FILE"))', 'VARBINARY(MAX)')  
)   
FROM ABC  
  
declare @table table  
(col VARCHAR(MAX))  
  
DECLARE @StartIndex INT, @EndIndex INT  
SET @StartIndex = 1  
  
while PATINDEX('% [1-9],%', @sql)>0  
begin  
SET @EndIndex = PATINDEX('% [1-9],%', @sql)  
  
insert into @table   
SELECT SUBSTRING(@sql, @StartIndex, @EndIndex - 1)  
  
SET @sql = SUBSTRING(@sql, @EndIndex + 1, LEN(@sql))  
end  
  
insert into @table   
SELECT SUBSTRING(@sql, @StartIndex, @EndIndex - 1)  
  
select * from @table    

Best regards,
Melissa

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

It sounds like your CSV file appears to be using CR instead of CR/LF as the row delimiter.

Answer 4

Hi @NickK ,

I tried with below which is working from my side.

declare @sql VARCHAR(MAX)   
set @sql='Agentid,agentname, phone, email, address,state,country, zip,ssn 1,nick,56677700987,abc@yahoo.com,address,tx,12345,9990009999 2,Jack,12677700987,abcuu@yahoo.com,address,tx,145,12990009999 3,mike,56677700987,abcd@yahoo.com,address,tx,12345,9990009999,'  
  
declare @table table  
(col VARCHAR(MAX))  
      
DECLARE @StartIndex INT, @EndIndex INT  
SET @StartIndex = 1  
      
while PATINDEX('% [1-9],%', @sql)>0  
begin  
SET @EndIndex = PATINDEX('% [1-9],%', @sql)  
      
insert into @table   
SELECT SUBSTRING(@sql, @StartIndex, @EndIndex - 1)  
      
SET @sql = SUBSTRING(@sql, @EndIndex + 1, LEN(@sql))  
end  
      
insert into @table   
SELECT SUBSTRING(@sql, @StartIndex, @EndIndex - 1)  
      
select * from @table    

Output:

col  
Agentid,agentname, phone, email, address,state,country, zip,ssn  
1,nick,56677700987,abc@yahoo.com,address,tx,12345,9990009999  
2,Jack,12677700987,abcuu@yahoo.com,address,tx,145,12990009999  
3,mike,56677700987,abcd@yahoo.com,address,tx,12345,9990009999  

If it is not working after combining above and your original query, please provide your CSV file and table if possible so that we could reproduce your issue and proceed to fix this issue.

Or you could try to add row delimiter in your CSV file while downloading using SSIS as mentioned by Tom.

Best regards,
Melissa

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 5

Check an example:

declare @csv as varchar(max) =
'Agentid,agentname, phone, email, address,state,country, zip,ssn
1,nick,56677700987,abc@yahoo.com,address,tx,USA,12345,9990009999
2,Jack,12677700987,abcuu@yahoo.com,address,tx,USA,145,12990009999
3,mike,56677700987,abcd@yahoo.com,address,tx,USA,12345,9990009999'

set @csv = replace(@csv, char(13), char(10))
set @csv = replace(@csv, char(10)+char(10), char(10))

declare @i as int = charindex(char(10), @csv)

declare @first_line as varchar(max) = trim(left(@csv, @i-1))
declare @rows as varchar(max) = trim(substring(@csv, @i+1, len(@csv)))

set @first_line = replace(@first_line, ',', ' varchar(max), ') +  ' varchar(max)'
set @rows = '(''' + replace(@rows, ',', ''', ''')
set @rows = replace(@rows, char(10), '''),' + char(10) + '(''') + ''')'

declare @sql as varchar(max) = 'drop table if exists ##t create table ##t (' + @first_line + ')'

exec (@sql)

set @sql = 'insert ##t values ' + @rows

exec (@sql)

select * from ##t

/* Result:
    Agentid  agentname   phone         email             address   state    country    zip      ssn
    1        nick        56677700987   abc@yahoo.com     address   tx       USA        12345    9990009999
    2        Jack        12677700987   abcuu@yahoo.com   address   tx       USA        145      12990009999
    3        mike        56677700987   abcd@yahoo.com    address   tx       USA        12345    9990009999
*/

However, there are other special method of importing CSV data, such as OPENROWSET and BULK INSERT, which take into consideration various details.