question

NickK-0102 avatar image
0 Votes"
NickK-0102 asked NickK-0102 commented

Help with Bae64Encode CSV to SQL Server table

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

  1. the o/p I have a csv data which looks like
    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,

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@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

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?




sql-server-generalsql-server-transact-sqlsql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered NickK-0102 commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

THANKS A LOT . Tom.


I really Appreciate Mellisa, Viorel for helping since the beginning .

Thanks a TON.

0 Votes 0 ·
MelissaMa-msft avatar image
1 Vote"
MelissaMa-msft answered NickK-0102 commented

Hi @NickK-0102,

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Melissa, thanks for the help, i tried running but it returned col as NULL.

fyi- I am having a column File that has a csv data and I want to take that o/p and convert to csv ( first rows has headers )or insert into a table .

0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered NickK-0102 commented

Hi @NickK-0102,

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.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Melissa, Thank you for looking into it.
Thank you, Tom.

  1. I am downloading from an EndpointURL using the Taskfactory tool which I don't have any option to add row delimiter in CSV file while downloading using SSIS

  2. the second's script gave some output

  3. But, my requirement is to get it like below:

Agentid,agentname, phone, email, address,state,country, zip,ssn---------------------these are columns and below are rows

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


please help


0 Votes 0 ·

The header row contains more columns than the data rows. Therefore the CSV is problematic.


0 Votes 0 ·

Viorel: i have test data posted manually.sorry, but i have latest below:

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

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered NickK-0102 commented

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.



· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

wow Thanks, Viorel-
i tried with my data and got the below error( looks like there is a problem with my data )
Msg 10709, Level 16, State 1, Line 567
The number of columns for each row in a table value constructor must be the same.


Thanks a lot

0 Votes 0 ·

any chance that i can just insert NULL into the missing columns?

0 Votes 0 ·

Hi @NickK-0102,

any chance that i can just insert NULL into the missing columns?

Could you please provide some sample data to reproduce your issue?

Best regards,
Melissa

0 Votes 0 ·
Show more comments
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered NickK-0102 commented

Hi @NickK-0102,

Please also refer to below updated one and check whether it is working:

 declare @sql NVARCHAR(MAX) 
 set @sql='Agent_Session_ID,Agent_No,Agent_Name,Team_No,Bus_No,StateIndex,Start_Date,Working,Available,Outstate_Code,Outstate,Log_State,Skill_No,Skill_Name,Duration,End_Date  166413780696,25394407,Skybox Support,5618680,4602511,1,9/29/2021 1:21:32 PM,0,0,0,,0,,,3,9/29/2021 1:21:32 PM  166413780696,25394407,Skybox Support,5618680,4602511,2,9/29/2021 1:21:32 PM,0,0,0,,1,,,413140,9/29/2021 1:28:25 PM  166413780696,25394407,Skybox Support,5618680,4602511,3,9/29/2021 1:28:25 PM,0,1,0,,1,,,13,9/29/2021 1:28:25 PM  166413780696,25394407,Skybox Support,5618680,4602511,4,9/29/2021 1:28:25 PM,0,0,-8,CallbackPending,1,,,5493,9/29/2021 1:28:31 PM'
    
 SET @sql=REPLACE(@SQL,'  ','$')
    
 declare @table table
 (id int identity(1,1),
 col VARCHAR(MAX))
    
 INSERT INTO @table
 select * from string_split(@sql,'$')
        
 DECLARE @Sql1 nvarchar(max) = '';
        
 SELECT @Sql1 += 'UNION ALL SELECT '''+ REPLACE(col, ',', ''',''') + ''' '
 FROM @table 
 WHERE id > 1;
        
 SELECT @Sql1 = STUFF(@Sql1, 1, 10, ';WITH CTE('+ col +') AS (') + ') SELECT * FROM CTE'
 FROM @table 
 WHERE id = 1;
        
 exec (@Sql1)

Output:

144898-output.png

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.


output.png (7.5 KiB)
output.png (15.8 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

wow, that was cool... it works when I set the values, but when I select them from the table it did not work for me. gives 1-row affcetd. as output.
144963-image.png


0 Votes 0 ·
image.png (124.1 KiB)