我遇到了T代码的问题.我需要导入CSV文件并将其转换为Server表。问题是CSV文件格式不正确,如下所示:
Recipe,Recipe,Recipe,Recipe,...
0,1,3,4,...
Data1,Data2,Data3,Data4,...
...最后的结果至少应该是这样:
Recipe,0,Data1,...
Recipe,1,Data2,...
Recipe,3,Data3,...
Recipe,4,Data4,...
...我使用XML路径将所有行都放在一个字符串中,但是我没有得到任何好的结果。
我掌握的信息:
我总是知道该文件的行数和列数。我使用的是Server 2016
任何帮助,以显示我正确的道路,将非常感谢!
谢谢!
发布于 2019-12-19 19:31:23
示例
Declare @S varchar(max) = 'Recipe,Recipe,Recipe,Recipe
0,1,3,4
Data1,Data2,Data3,Data4'
;with cte as (
Select CN=A.RetSeq
,RN=B.RetSeq
,Value=B.RetVal
From [dbo].[tvf-Str-Parse](@S,char(13)+char(10)) A
Cross Apply [dbo].[tvf-Str-Parse](A.RetVal,',') B
)
Select Str = Stuff((Select ',' +Value From cte Where RN=A.RN Order By CN For XML Path ('')),1,1,'')
From (Select Distinct RN from cte) A
Order By A.RN返回
Str
Recipe,0,Data1
Recipe,1,Data2
Recipe,3,Data3
Recipe,4,Data4如果感兴趣的话,函数
CREATE FUNCTION [dbo].[tvf-Str-Parse] (@String varchar(max),@Delimiter varchar(10))
Returns Table
As
Return (
Select RetSeq = row_number() over (order by 1/0)
,RetVal = ltrim(rtrim(B.i.value('(./text())[1]', 'varchar(max)')))
From (Select x = Cast('<x>' + replace((Select replace(@String,@Delimiter,'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A
Cross Apply x.nodes('x') AS B(i)
);编辑-没有函数的选项
Declare @S varchar(max) = 'Recipe,Recipe,Recipe,Recipe
0,1,3,4
Data1,Data2,Data3,Data4'
;with cte as (
Select CN=A.RetSeq
,RN=B.RetSeq
,Value=B.RetVal
From (
Select RetSeq = row_number() over (order by 1/0)
,RetVal = ltrim(rtrim(B.i.value('(./text())[1]', 'varchar(max)')))
From (Select x = Cast('<x>' + replace((Select replace(@S,char(13)+char(10),'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A
Cross Apply x.nodes('x') AS B(i)
) A
Cross Apply (
Select RetSeq = row_number() over (order by 1/0)
,RetVal = ltrim(rtrim(B.i.value('(./text())[1]', 'varchar(max)')))
From (Select x = Cast('<x>' + replace((Select replace(A.RetVal,',','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A
Cross Apply x.nodes('x') AS B(i)
) B
)
Select Str = Stuff((Select ',' +Value From cte Where RN=A.RN Order By CN For XML Path ('')),1,1,'')
From (Select Distinct RN from cte) A
Order By RN编辑JSON选项--纠正双引号
Declare @S varchar(max) = 'Recipe,Recipe,Recipe,Recipe
1,,3,4
Data1,Data2,Data"3,Data4'
;with cte as (
Select CN = A.[key]
,RN = B.[Key]
,Value = replace(B.Value,'||','"')
From OpenJSON('["'+replace(replace(@S,'"','||'),char(13)+char(10),'","')+'"]') A
Cross Apply (
Select *
From OpenJSON('["'+replace(A.Value,',','","')+'"]')
) B
)
Select Str = Stuff((Select ',' +Value From cte Where RN=A.RN Order By CN For XML Path ('')),1,1,'')
From (Select Distinct RN from cte) A
Order By RN返回
Str
Recipe,1,Data1
Recipe,,Data2 -- null (2 is missing
Recipe,3,Data"3 -- has double quote
Recipe,4,Data4https://stackoverflow.com/questions/59415659
复制相似问题