question

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.

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

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.

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.

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:

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.