· 2 years ago · May 21, 2023, 03:30 AM
1SET NOCOUNT ON
2SET DATEFORMAT YMD
3
4Declare @arquivo varchar(255) -- nome do arquivo
5Declare @data varchar(80)
6Declare @time varchar(80)
7Declare @comando varchar(1000)
8DECLARE @valores VARCHAR(24)
9
10-- campos da tabele/layout
11DECLARE @idParticipante varchar(24)
12DECLARE @cnpjParticipante varchar(14)
13DECLARE @razaoSocialParticipante varchar(200)
14DECLARE @razaoSocialParticipanteMaiusculo varchar(200)
15DECLARE @dataDeInclusaolParticipante date
16DECLARE @dataDeAlteracaoParticipante date
17DECLARE @funcoesParticipante varchar(24)
18
19DECLARE @cmd VARCHAR(8000)
20DECLARE @fileList TABLE (fileName VARCHAR(500))
21
22SET @cmd = 'dir /b /s C:\Arquivos\*Participantes*.jsonl'
23
24IF OBJECT_ID('tempdb..#TABFILE') IS NOT NULL
25 DROP TABLE #TABFILE
26
27CREATE TABLE #TABFILE (ARQUIVO NVARCHAR(80))
28
29
30IF (OBJECT_ID('tempdb..#TABJSON') IS NOT NULL) DROP TABLE #TABJSON
31CREATE TABLE #TABJSON (TEXTO_JSON NVARCHAR(MAX))
32
33
34EXEC sp_configure 'show advanced options', '1'
35RECONFIGURE
36
37EXEC sp_configure 'xp_cmdshell', '1'
38RECONFIGURE
39
40
41INSERT INTO @fileList (fileName)
42EXEC master..xp_cmdshell @cmd
43
44INSERT INTO #TABFILE (ARQUIVO)
45SELECT RIGHT(fileName, CHARINDEX('\', REVERSE(fileName)) - 1)
46FROM @fileList
47WHERE fileName IS NOT NULL
48
49DECLARE cur CURSOR FOR
50 select arquivo from #TABFILE where arquivo is not null
51OPEN cur
52FETCH NEXT FROM cur INTO @arquivo
53WHILE @@FETCH_STATUS = 0
54BEGIN
55 set @data = REPLACE(@arquivo,'.jsonl','')
56 set @data = SUBSTRING(right(@data,25),2,4) +'-'+ SUBSTRING(right(@data,25),6,2) +'-'+ SUBSTRING(right(@data,25),8,2)
57
58 set @time = RIGHT(REPLACE(@arquivo,'.jsonl',''),14)
59 set @time = SUBSTRING(@time,1,4) + '-' + SUBSTRING(@time,5,2) + '-' + SUBSTRING(@time,7,2) + ' ' + SUBSTRING(@time,9,2) + ':' + SUBSTRING(@time,11,2) + ':' + SUBSTRING(@time,13,2) + '.000'
60
61 set @comando = 'BULK INSERT #TABJSON FROM ''C:\Arquivos\' + @arquivo + ''' WITH(FIRSTROW = 1,FIELDTERMINATOR = '','',ROWTERMINATOR = ''0x0A'', TABLOCK, CODEPAGE=''65001'')'
62
63 EXECUTE (@comando)
64
65 declare @texto varchar(MAX)
66
67 declare cursor2 CURSOR FOR
68
69 select texto_json from #TABJSON
70
71 OPEN cursor2
72 FETCH NEXT FROM cursor2 INTO @texto
73 WHILE @@FETCH_STATUS = 0
74 BEGIN
75 if UPPER(SUBSTRING(@texto,3,2)) = 'ID' -- arquivo que contenha este campo -- não está funfando se não tiver a linha 51. colocar no tabfile apenas participantes
76 -- Obter o valor do campo CNPJ do participante
77 SELECT @cnpjParticipante = JSON_VALUE(@texto, '$.cnpj');
78
79 -- Verificar se o CNPJ já existe na tabela final
80 IF NOT EXISTS (SELECT 1 FROM [raw].[SParticipantes_V0] WHERE cnpj = @cnpjParticipante)
81 BEGIN
82 --if UPPER(SUBSTRING(@texto,3,2)) = 'ID' -- arquivo que contenha este campo
83
84 -- Obter valores da lista separados por vírgulas
85 SELECT @valores = STRING_AGG(value, ',') FROM OPENJSON(@texto, '$.funcoes');
86
87 -- Processar o registro atual da tabela
88 SELECT
89 @idParticipante = JSON_VALUE(@texto, '$.id'),
90 @cnpjParticipante = JSON_VALUE(@texto, '$.cnpj'),
91 @razaoSocialParticipante = JSON_VALUE(@texto, '$.razaoSocial'),
92 @dataDeInclusaolParticipante = JSON_VALUE(@texto, '$.dataDeInclusao'),
93 @dataDeAlteracaoParticipante = JSON_VALUE(@texto, '$.dataDeAlteracao'),
94 @funcoesParticipante = @valores --JSON_VALUE(@texto, '$.funcoes')
95 ;
96
97 -- Converter o campo para maiúsculo
98 SET @razaoSocialParticipanteMaiusculo = UPPER(@razaoSocialParticipante);
99
100 -- Inserir na tabela participante
101 INSERT INTO [raw].[SParticipantes_V0] (dataDeReferencia,timestampDoArquivo,id,cnpj,razaoSocial,dataDeInclusao,dataDeAlteracao,funcoes)
102 VALUES (@data,@time,@idParticipante,@cnpjParticipante,@razaoSocialParticipanteMaiusculo,@dataDeInclusaolParticipante,@dataDeAlteracaoParticipante,@funcoesParticipante);
103 END;
104
105 -- Avançar para o próximo registro da tabela
106 FETCH NEXT FROM cursor2 INTO @texto;
107 END;
108
109 CLOSE cursor2;
110 DEALLOCATE cursor2;
111 FETCH NEXT FROM cur INTO @arquivo;
112END;
113CLOSE cur;
114DEALLOCATE cur;
115