none
Inserindo dados do arquivo txt no sql server RRS feed

  • Pergunta

  • Ola pessoal,

    Preciso incluir dados das batidas de ponto do arquivo .txt na tabela do SQL.

    Os dados estão nesta estrutura. 

    0000024183111120191415000000000006167

    Eu só preciso das informações em negritos, que seria: Data, hora e CPF.

    0000024183111120191415000000000006167 -  DATA

    0000024183111120191415000000000006167  -  HORA

    0000024183111120191415000000000006167  - CPF

    No caso, preciso comparar o CPF do arquivo com o da tabela de funcionário, pra pegar o id do funcionario e inserir junto com DATA e HORA na tabela de batidas.

    Serão 4 horarios:  Entrada1, Saida1, Entrada2, Saida2.




    • Editado larosas quarta-feira, 13 de novembro de 2019 14:27
    quarta-feira, 13 de novembro de 2019 14:25

Todas as Respostas

  • Pela amostra de dados, o arquivo texto utiliza campos com tamanho fixo. Há algumas formas de separar os campos, desde através da utilização de arquivo de formato até importar cada registro como uma linha para uma tabela temporária e então separar os campos utilizando a função SUBSTRING.

    De imediato sugiro que leia o artigo “Importação de dados em massa”, que contém diversos métodos de importação.

    ---

    Como se sabe se o registro contém horário de saída ou de entrada e qual é o turno?

    Há alguma informação que indique a ordem em que os registros devem ser analisados? Isto é, se existe uma sequência numérica.

    É garantido que sempre haverão quatro registros para cada CPF/data?

    Qual é a estrutura da tabela de batidas? Isto é, nome das colunas, como estão declaradas, etc.

    Como estão declaradas as colunas CPF e ID do funcionário na tabela de funcionários?


    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    • Editado José Diz quarta-feira, 13 de novembro de 2019 15:01
    quarta-feira, 13 de novembro de 2019 14:52
  • Como se sabe se o registro contém horário de saída ou de entrada e qual é o turno?

    é um arquivo gerado pelo relógio de ponto.

    É garantido que sempre haverão quatro registros para cada CPF/data?

    Não é garantido. Tem os casos de entrada tardia ou saída  antecipada, nesses casos, as vezes o funcionário deixa de almoçar.

    Qual é a estrutura da tabela de batidas? Isto é, nome das colunas, como estão declaradas, etc.

    CREATE TABLE [dbo].[batidas](
    	[id] [int] IDENTITY(1,1) NOT NULL,
    	[data] [smalldatetime] NOT NULL,
    	[idfuncionario] [int] NOT NULL,
    	[entrada1] [varchar](10) NULL,
    	[saida1] [varchar](10) NULL,
    	[entrada2] [varchar](10) NULL,
    	[saida2] [varchar](10) NULL,
    	[entrada3] [varchar](10) NULL,
    	[saida3] [varchar](10) NULL,
    	[entrada4] [varchar](10) NULL,
    	[saida4] [varchar](10) NULL,
    	[entrada5] [varchar](10) NULL,
    	[saida5] [varchar](10) NULL,
    	[ajuste] [varchar](6) NULL,
    	[abono2] [varchar](5) NULL,
    	[abono3] [varchar](5) NULL,
    	[abono4] [varchar](5) NULL,
    	[reserva_refeicao] [bit] NOT NULL,
    	[obs] [varchar](50) NULL,
    	[mem_entrada1] [varchar](5) NULL,
    	[mem_saida1] [varchar](5) NULL,
    	[mem_entrada2] [varchar](5) NULL,
    	[mem_saida2] [varchar](5) NULL,
    	[mem_entrada3] [varchar](5) NULL,
    	[mem_saida3] [varchar](5) NULL,
    	[mem_entrada4] [varchar](5) NULL,
    	[mem_saida4] [varchar](5) NULL,
    	[mem_entrada5] [varchar](5) NULL,
    	[mem_saida5] [varchar](5) NULL,
    	[auto_entrada1] [bit] NOT NULL,
    	[auto_saida1] [bit] NOT NULL,
    	[auto_entrada2] [bit] NOT NULL,
    	[auto_saida2] [bit] NOT NULL,
    	[auto_entrada3] [bit] NOT NULL,
    	[auto_saida3] [bit] NOT NULL,
    	[auto_entrada4] [bit] NOT NULL,
    	[auto_saida4] [bit] NOT NULL,
    	[auto_entrada5] [bit] NOT NULL,
    	[auto_saida5] [bit] NOT NULL,
    	[compensado] [bit] NOT NULL,
    	[almoco_livre] [bit] NOT NULL,
    	[neutro] [bit] NOT NULL,
    	[nbanco] [bit] NOT NULL,
    	[t_extra] [tinyint] NULL,
    	[t_falta] [tinyint] NULL,
    	[bac_entrada1] [varchar](5) NULL,
    	[bac_saida1] [varchar](5) NULL,
    	[bac_entrada2] [varchar](5) NULL,
    	[bac_saida2] [varchar](5) NULL,
    	[bac_entrada3] [varchar](5) NULL,
    	[bac_saida3] [varchar](5) NULL,
    	[bac_entrada4] [varchar](5) NULL,
    	[bac_saida4] [varchar](5) NULL,
    	[bac_entrada5] [varchar](5) NULL,
    	[bac_saida5] [varchar](5) NULL,
    	[acrescimo] [varchar](6) NULL,
    	[somar_t] [bit] NOT NULL,
    	[horario_num] [int] NULL,
    	[equip_entrada1] [int] NULL,
    	[equip_saida1] [int] NULL,
    	[equip_entrada2] [int] NULL,
    	[equip_saida2] [int] NULL,
    	[equip_entrada3] [int] NULL,
    	[equip_saida3] [int] NULL,
    	[equip_entrada4] [int] NULL,
    	[equip_saida4] [int] NULL,
    	[equip_entrada5] [int] NULL,
    	[equip_saida5] [int] NULL,
    	[extra_especial_id] [int] NULL,
    	[folga] [bit] NOT NULL,
    	[fdados_entrada1] [int] NULL,
    	[fdados_saida1] [int] NULL,
    	[fdados_entrada2] [int] NULL,
    	[fdados_saida2] [int] NULL,
    	[fdados_entrada3] [int] NULL,
    	[fdados_saida3] [int] NULL,
    	[fdados_entrada4] [int] NULL,
    	[fdados_saida4] [int] NULL,
    	[fdados_entrada5] [int] NULL,
    	[fdados_saida5] [int] NULL,
    	[dsr_especial] [bit] NOT NULL,
     CONSTRAINT [PK_batidas] PRIMARY KEY CLUSTERED 
    (
    	[data] ASC,
    	[idfuncionario] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    Como estão declaradas as colunas CPF e ID do funcionário na tabela de funcionários?

    CREATE TABLE [dbo].[funcionarios](
    	[id] [int] IDENTITY(1,1) NOT NULL,
    	[n_folha] [varchar](22) NULL,
    	[nome] [varchar](100) NULL,
    	[n_identificador] [varchar](20) NULL,
    	[carteira] [varchar](50) NULL,
    	[empresa_id] [int] NULL,
    	[horario_num] [int] NULL,
    	[estado] [int] NULL,
    	[funcao_id] [int] NULL,
    	[departamento_id] [int] NULL,
    	[admissao] [smalldatetime] NULL,
    	[demissao] [smalldatetime] NULL,
    	[nao_digital] [bit] NOT NULL,
    	[afast_data_ini] [smalldatetime] NULL,
    	[afast_data_fim] [smalldatetime] NULL,
    	[afast_motivo] [varchar](100) NULL,
    	[afast_justificativa_id] [int] NULL,
    	[invisivel] [bit] NOT NULL,
    	[escala_id] [int] NULL,
    	[escala_mensal] [bit] NOT NULL,
    	[t_horario] [varchar](50) NULL,
    	[senha_equipamento] [int] NULL,
    	[filtro1_id] [int] NULL,
    	[filtro2_id] [int] NULL,
    	[obs] [varchar](255) NULL,
    	[xcampo1] [varchar](50) NULL,
    	[alt2_horario_num] [int] NULL,
    	[alt3_horario_num] [int] NULL,
    	[alt4_horario_num] [int] NULL,
    	[endereco] [varchar](100) NULL,
    	[bairro] [varchar](30) NULL,
    	[cidade] [varchar](50) NULL,
    	[uf] [varchar](2) NULL,
    	[cep] [varchar](9) NULL,
    	[telefone] [varchar](20) NULL,
    	[celular] [varchar](20) NULL,
    	[email] [varchar](255) NULL,
    	[rg] [varchar](255) NULL,
    	[expedicao] [smalldatetime] NULL,
    	[ssp] [varchar](2) NULL,
    	[cpf] [varchar](20) NULL,
    	[mae] [varchar](100) NULL,
    	[pai] [varchar](100) NULL,
    	[nascimento] [smalldatetime] NULL,
    	[sexo_masculino] [bit] NOT NULL,
    	[estado_civil_id] [int] NULL,
    	[nacionalidade] [varchar](50) NULL,
    	[naturalidade] [varchar](50) NULL,
    	[web_senha] [varchar](10) NULL,
    	[web_nao_altera] [bit] NOT NULL,
    	[web_bloqueado] [bit] NOT NULL,
    	[alt_usuario_id] [int] NULL,
    	[alt_data] [smalldatetime] NULL,
    	[master] [bit] NOT NULL,
    	[n_provisorio] [varchar](20) NULL,
    	[provisorio_data_ini] [smalldatetime] NULL,
    	[provisorio_data_fim] [smalldatetime] NULL,
    	[cidade_empresa_id] [int] NULL,
    	[estrutura_id] [int] NULL,
    	[assinatura_eletronica] [varchar](50) NULL,
    	[integracao_id] [int] NULL,
    	[bh_inicio] [smalldatetime] NULL,
    	[centro_custos] [varchar](255) NULL,
    	[nivel_id] [int] NULL,
    	[web_auto_aceitar] [bit] NOT NULL,
    	[n_pis] [varchar](20) NULL,
    	[web_solicitacoes] [bit] NOT NULL,
    	[data_alteracao_portaria] [smalldatetime] NULL,
    	[web_data_fechamento] [smalldatetime] NULL,
    	[especial_insight_sinha] [smallint] NULL,
    	[motivo_demissao_id] [int] NULL,
    	[web_somente_visto] [bit] NOT NULL,
    	[web_nao_incluir_manual] [bit] NOT NULL,
     CONSTRAINT [PK_funcionarios] PRIMARY KEY CLUSTERED 
    (
    	[id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]


    No arquivo, tem alguns casos, que o nome do funcionário está vindo no lugar do CPF.

    • Editado larosas quarta-feira, 13 de novembro de 2019 15:12
    quarta-feira, 13 de novembro de 2019 15:10
  • Na coluna [data] deve-se registrar somente a data, mesmo sendo uma coluna que aceita data e hora?

    Como gravar o horário nas colunas [entrada1], [entrada2], [saida1] e [saida2] : hhmm? hh:mm?

    A coluna [id] da tabela FUNCIONARIOS é a que deve ser utilizada para gravar na coluna [idfuncionario] da tabela BATIDAS?

    Na tabela de funcionários o CPF também é gravado com 11 caracteres, sem os separadores?

    --

    Como não é garantido que o arquivo de texto possua entradas e saídas consistentes, deve-se primeiro importar o conteúdo do arquivo para uma tabela temporária, fazer nessa tabela a consistência e eventual correção dos dados para somente após (tentar) incluir na tabela BATIDAS.

    Sobre "tem alguns casos que o nome do funcionário está vindo no lugar do CPF", refere-se ao arquivo de texto ou à tabela de funcionários?

    Nos casos em que não há 4 registros por funcionário/data, como detectar qual horário está faltando?

    Há tabela de turnos que indique quais são os horários estabelecidos de entrada e de saída para cada funcionário/turno?

    ---

    Eis esboço dos códigos para importação do arquivo texto para uma tabela de trabalho:

    -- código #1 v2
    USE stageDB;
    CREATE TABLE dbo.Importado (Registro varchar(200));
    go
    -- importação para a tabela temporária
    BULK INSERT dbo.Importado from 'C:\Downloads\batidas.txt';
    go

    Não testei; pode conter erro(s).

    Dependendo das características do arquivo texto pode ser necessário acrescentar FIRSTROW, ROWTERMINATOR e outras opções no comando BULK INSERT.

    ---

    O código seguinte que deve ser construído é o de consistência dos dados, procurando batidas que estejam faltando etc etc. Isto é mais fácil de fazer na aplicação, embora seja possível de ser programado em T-SQL.

    -- código #2
    -- apaga linhas de início e final de trecho, além de CPF zerado
    DELETE Importado
    where left (Registro, 9) in ('000000000', '999999999')
    or (len (Registro) = 37 and substring (Registro, 23, 11) = '00000000000');

    ---

    Eis esboço do código SQL de carga da tabela de batidas.

    -- código #3 v2
    USE banco;
    
    --
    with 
    Importado_2 as (
    SELECT substring (Registro, 23, 11) as CPF, 
           convert (smalldatetime, 
    (substring (Registro, 11, 2) + '/' + substring (Registro, 13, 2) + '/' + substring (Registro, 15, 4)),
    103) as DATA, (substring (Registro, 19, 2) + ':' + substring (Registro, 21, 2)) as HORA from stageDB.dbo.Importado ), Importado_3 as ( SELECT *, seq= row_number() over (partition by CPF, DATA order by HORA) from Importado_2 ) INSERT into BATIDAS (idfuncionario, data, entrada1, saida1, entrada2, saida2) SELECT F.id, I.DATA, max (case when seq = 1 then HORA end) as E1, max (case when seq = 2 then HORA end) as S1, max (case when seq = 3 then HORA end) as E2, max (case when seq = 4 then HORA end) as S2 from Importado_3 as I inner join FUNCIONARIOS as F on F.CPF = I.CPF
    group by F.id, I.DATA;

    Não testei; pode conter erro(s).

    O código SQL acima somente carrega dados que contenham CPF.


    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    • Editado José Diz segunda-feira, 18 de novembro de 2019 19:20
    quarta-feira, 13 de novembro de 2019 16:00
  • Oi José, deu esse erro.

    Msg 4863, Level 16, State 1, Line 9
    Bulk load data conversion error (truncation) for row 323, column 1 (Registro).
    Msg 4863, Level 16, State 1, Line 9
    Bulk load data conversion error (truncation) for row 324, column 1 (Registro).
    Msg 4863, Level 16, State 1, Line 9
    Bulk load data conversion error (truncation) for row 327, column 1 (Registro).
    Msg 4863, Level 16, State 1, Line 9
    Bulk load data conversion error (truncation) for row 329, column 1 (Registro).
    Msg 4863, Level 16, State 1, Line 9
    Bulk load data conversion error (truncation) for row 331, column 1 (Registro).
    Msg 4863, Level 16, State 1, Line 9
    Bulk load data conversion error (truncation) for row 333, column 1 (Registro).
    Msg 4863, Level 16, State 1, Line 9
    Bulk load data conversion error (truncation) for row 522, column 1 (Registro).
    Msg 4863, Level 16, State 1, Line 9
    Bulk load data conversion error (truncation) for row 524, column 1 (Registro).
    Msg 4863, Level 16, State 1, Line 9
    Bulk load data conversion error (truncation) for row 671, column 1 (Registro).


    Meu arquivo esta + OU - assim:

    0000000001110000000000198            NOMEDAEMPRESA                                                    0000000000005680000
    000000540314102019122000000000089663
    0000005413141020191530000000000050F0

    000000872501112019104000000000000NOMEFUNCIONARIO                                         00000000000856AFA

    99999999900000000000000000000000000000000000000000000387
    ANNOD2JF5FIA2IJFHQE5VN3BXAF6AFMVG4AHXZPC4DTZP76EBOUIAATL55K6LHMO   



    • Editado larosas quarta-feira, 13 de novembro de 2019 17:39
    quarta-feira, 13 de novembro de 2019 17:30
  • Meu arquivo esta + OU - assim:

    0000000001110000000000198            NOMEDAEMPRESA                                                    0000000000005680000
    000000540314102019122000000000089663
    0000005413141020191530000000000050F0
    000000872501112019104000000000000NOMEFUNCIONARIO                                         00000000000856AFA

    A informação acima não coincide com a informação inicial:

          0000024183111120191415000000000006167

    Existem vários tipos de registros no mesmo arquivo texto?


    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    quarta-feira, 13 de novembro de 2019 20:20
  • Sim, porém mexi no arquivo (por ser urgente) e deletei essas informações a mais, mas mesmo assim não inseri os dados na tabela batidas.
    quarta-feira, 13 de novembro de 2019 20:24
  • Sim, porém mexi no arquivo (por ser urgente) e deletei essas informações a mais, mas mesmo assim não inseri os dados na tabela batidas.

    No código #1 você pode alterar a declaração da coluna Registro de
             varchar(40)

    para
             varchar(n)

    onde n é o maior tamanho de registro no arquivo texto.

    Mas o máximo que conseguirá é carregar a tabela "Importado". O código #3, que carrega a tabela de batidas, foi construído considerando-se a informação inicial que você forneceu.


    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    quarta-feira, 13 de novembro de 2019 20:49
  • Iarosas,

    Por acaso o relógio de ponto é um Madis? O Software de acesso aos dados é um Toutatis?


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    quarta-feira, 13 de novembro de 2019 22:32
  • -- importação para a tabela temporária
    BULK INSERT dbo.Importado
      from 'C:\arquivo.txt';
    
    with 
    Importado_2 as (
    SELECT substring (Registro, 24, 11) as CPF, 
              (substring (Registro, 11, 2) + '-' + substring (Registro, 13, 2) + '-' + substring (Registro, 15, 4)) --,103) 
    	as DATA,
           (substring (Registro, 19, 2) + ':' + substring (Registro, 21, 2)) as HORA
      from  [PontoSecullum4].dbo.Importado
    ),
    Importado_3 as (
    SELECT *, seq= row_number() over (partition by CPF, DATA order by HORA)
      from Importado_2
    )
    
      SELECT F.nome, f.CPF , I.DATA,
             max (case when seq = 1 then HORA end) as E1,
             max (case when seq = 2 then HORA end) as S1,
             max (case when seq = 3 then HORA end) as E2,
             max (case when seq = 4 then HORA end) as S2
        from Importado_3 as I
             inner join FUNCIONARIOS as F on F.CPF = I.CPF
        group by F.nome, f.CPF,I.DATA;

    Consegui com essa query, porem fazendo ajuste no arquivo como falei. Como faço pra ignorar a parte que não usarei.


    0000000000000000000000100            NOME  DA EMPRESA                                                                                        000010044500013411410201914112012000000019135008DF
    00000055431410201914230132200080003A75
    000000555314102019162601307034951952F3
    00000055631410201917220000000000000097
    00000055731410201917300106900000000028
    00000055831410201917470134000000000000
    00000055931410201918100000000000000091
    00000312231411201913490000000000000D68

    0000007735311020191706A0134900000006NOME DO FUNCIONARIO                

    0021145749977024EF8000000000014112019134902
    9999999990000000000000025200000000000000000000000000009
    00000000I3WAMCVL6NRPX4AENDHRZ4BFWGT5JO2POVUGUPTTWLANF5UPW3GZIOYA

    quinta-feira, 14 de novembro de 2019 18:25
  • Consegui com essa query, porem fazendo ajuste no arquivo como falei. Como faço pra ignorar a parte que não usarei.

    E como é possível identificar quais linhas deverão ser rejeitadas? Você possui documentação que informe o que contém cada campo do arquivo texto?

    Conhecendo-se o formato do arquivo texto, é possível rejeitá-las no código #2 ou mesmo no código #3. Preferencialmente no código #2, que é justamente a etapa de tratamento do arquivo importado para uma tabela intermediária.

    Acrescentei um comando DELETE no código #2, para apagar duas das linhas que foi possível deduzir a função delas.

    ---

    Você fez a atualização recomendada em acesso ao Reporting Services?


    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    • Editado José Diz sexta-feira, 15 de novembro de 2019 10:55
    quinta-feira, 14 de novembro de 2019 21:13
  • -- importação para a tabela temporária
    BULK INSERT dbo.Importado
      from 'C:\arquivo.txt';
    
    with 
    Importado_2 as (
    SELECT substring (Registro, 24, 11) as CPF, 
              (substring (Registro, 11, 2) + '-' + substring (Registro, 13, 2) + '-' + substring (Registro, 15, 4)) --,103) 
    	as DATA,
           (substring (Registro, 19, 2) + ':' + substring (Registro, 21, 2)) as HORA
      from  [PontoSecullum4].dbo.Importado
    ),
    Importado_3 as (
    SELECT *, seq= row_number() over (partition by CPF, DATA order by HORA)
      from Importado_2
    )
    
      SELECT F.nome, f.CPF , I.DATA,
             max (case when seq = 1 then HORA end) as E1,
             max (case when seq = 2 then HORA end) as S1,
             max (case when seq = 3 then HORA end) as E2,
             max (case when seq = 4 then HORA end) as S2
        from Importado_3 as I
             inner join FUNCIONARIOS as F on F.CPF = I.CPF
        group by F.nome, f.CPF,I.DATA;

    Consegui com essa query, porem fazendo ajuste no arquivo como falei. Como faço pra ignorar a parte que não usarei.


    0000000000000000000000100            NOME  DA EMPRESA                                                                                        000010044500013411410201914112012000000019135008DF
    00000055431410201914230132200080003A75
    000000555314102019162601307034951952F3
    00000055631410201917220000000000000097
    00000055731410201917300106900000000028
    00000055831410201917470134000000000000
    00000055931410201918100000000000000091
    00000312231411201913490000000000000D68

    0000007735311020191706A0134900000006NOME DO FUNCIONARIO                

    0021145749977024EF8000000000014112019134902
    9999999990000000000000025200000000000000000000000000009
    00000000I3WAMCVL6NRPX4AENDHRZ4BFWGT5JO2POVUGUPTTWLANF5UPW3GZIOYA

    SS_DBAJr,

    Você quer ignorar no momento da carga ou após esta importado excluir o conteúdo?


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    quinta-feira, 14 de novembro de 2019 23:24
  • -- importação para a tabela temporária
    BULK INSERT dbo.Importado
      from 'C:\arquivo.txt';
    
    with 
    Importado_2 as (
    SELECT substring (Registro, 24, 11) as CPF, 
              (substring (Registro, 11, 2) + '-' + substring (Registro, 13, 2) + '-' + substring (Registro, 15, 4)) --,103) 
    	as DATA,
           (substring (Registro, 19, 2) + ':' + substring (Registro, 21, 2)) as HORA
      from  [PontoSecullum4].dbo.Importado
    ),
    Importado_3 as (
    SELECT *, seq= row_number() over (partition by CPF, DATA order by HORA)
      from Importado_2
    )
    
      SELECT F.nome, f.CPF , I.DATA,
             max (case when seq = 1 then HORA end) as E1,
             max (case when seq = 2 then HORA end) as S1,
             max (case when seq = 3 then HORA end) as E2,
             max (case when seq = 4 then HORA end) as S2
        from Importado_3 as I
             inner join FUNCIONARIOS as F on F.CPF = I.CPF
        group by F.nome, f.CPF,I.DATA;

    Consegui com essa query, porem fazendo ajuste no arquivo como falei. Como faço pra ignorar a parte que não usarei.


    0000000000000000000000100            NOME  DA EMPRESA                                                                                        000010044500013411410201914112012000000019135008DF
    00000055431410201914230132200080003A75
    000000555314102019162601307034951952F3
    00000055631410201917220000000000000097
    00000055731410201917300106900000000028
    00000055831410201917470134000000000000
    00000055931410201918100000000000000091
    00000312231411201913490000000000000D68

    0000007735311020191706A0134900000006NOME DO FUNCIONARIO                

    0021145749977024EF8000000000014112019134902
    9999999990000000000000025200000000000000000000000000009
    00000000I3WAMCVL6NRPX4AENDHRZ4BFWGT5JO2POVUGUPTTWLANF5UPW3GZIOYA

    SS_DBAJr,

    Você quer ignorar no momento da carga ou após esta importado excluir o conteúdo?


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    Acredito que após a importação seria melhor. A query que tenho hoje da erro na importação.

    segunda-feira, 18 de novembro de 2019 15:35
  • Consegui com essa query, porem fazendo ajuste no arquivo como falei. Como faço pra ignorar a parte que não usarei.

    E como é possível identificar quais linhas deverão ser rejeitadas? Você possui documentação que informe o que contém cada campo do arquivo texto?

    Conhecendo-se o formato do arquivo texto, é possível rejeitá-las no código #2 ou mesmo no código #3. Preferencialmente no código #2, que é justamente a etapa de tratamento do arquivo importado para uma tabela intermediária.

    Acrescentei um comando DELETE no código #2, para apagar duas das linhas que foi possível deduzir a função delas.

    ---

    Você fez a atualização recomendada em acesso ao Reporting Services?


    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    Sim, fiz a atualização no Reporting, mas ainda sim, o erro continua.
    segunda-feira, 18 de novembro de 2019 15:36
  • SS_DBAJr,

    Atualmente a tabela que você esta utilizando para receber os dados esta definida com quais tipos de dados?


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    segunda-feira, 18 de novembro de 2019 16:43
  • SS_DBAJr,

    Atualmente a tabela que você esta utilizando para receber os dados esta definida com quais tipos de dados?


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    CPF, DATA e HORA.

    0000000001110000000000198            NOMEDAEMPRESA                                                 0000000000005680000
    000000540314102019122000000000089663
    0000005413141020191530000000000050F0
    000000872501112019104000000000000NOMEFUNCIONARIO                                         00000000000856AFA
    0000024183111120191415000000000006167 -  DATA
    0000024183111120191415000000000006167  -  HORA
    0000024183111120191415000000000006167  - CPF
    99999999900000000000000000000000000000000000000000000387
    ANNOD2JF5FIA2IJFHQE5VN3BXAF6AFMVG4AHXZPC4DTZP76EBOUIAATL55K6LHMO

    segunda-feira, 18 de novembro de 2019 18:06
  • SS_DBAJr,

    Encontrei a estrutura nos posts acima!

    O que você poderia fazer, é justamente definir inicialmente todas as colunas como NVarchar(), realizar a importação, e depois reestruturar cada coluna com seu respectivo tipo de dados, e por último excluir os dados desnecessário.


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]


    segunda-feira, 18 de novembro de 2019 18:11
  • Consegui com essa query, porem fazendo ajuste no arquivo como falei. Como faço pra ignorar a parte que não usarei.

    E como é possível identificar quais linhas deverão ser rejeitadas? Você possui documentação que informe o que contém cada campo do arquivo texto, além de DATA, HORA e CPF? Conhecendo-se o formato do arquivo texto, é possível rejeitá-las no código #2. Inclusive acrescentei um comando DELETE no código #2, para apagar duas das linhas que foi possível deduzir a função delas.

    Faça o seguinte: acrescente no código #2 os mesmos critérios que você utilizou para fazer ajuste no arquivo. Você tomou decisões em o que apagar manualmente considerando-se determinados fatores, certo? É só implementar os comandos DELETE que atendam aos fatores que você utilizou para apagar manualmente.

    -- código #2
    -- apaga linhas de início e final de trecho, além de CPF zerado
    DELETE Importado
    where left (Registro, 9) in ('000000000', '999999999')
    or (len (Registro) = 37 and substring (Registro, 23, 11) = '00000000000');

     

    Dúvida: os registros que são para serem aproveitados possuem qual tamanho? Isto é, numero de algarismos.


    José Diz     Belo Horizonte, MG - Brasil     [query performance tuning: Porto SQL]


    Este conteúdo é fornecido sem garantias de qualquer tipo, seja expressa ou implícita.

    • Editado José Diz segunda-feira, 18 de novembro de 2019 19:15
    segunda-feira, 18 de novembro de 2019 18:55
  • Registros aproveitados são: DATA, HORA E CPF, um total de 23 caracteres.

    terça-feira, 19 de novembro de 2019 16:02
  • SS_DBAJr,

    Neste caso, sabendo do tamanho de cada campo, você poderia fazer uso da função SubString() para copiar as partes desejadas da sua estrutura, tendo como base as posições e tamanho específicos de cada parte de dados.

    Veja se este exemplo te ajuda, elaborado com base na estrutura apresentada anteriormente:

    CPF, DATA e HORA.

    0000000001110000000000198            NOMEDAEMPRESA                                                 0000000000005680000
    000000540314102019122000000000089663
    0000005413141020191530000000000050F0
    000000872501112019104000000000000NOMEFUNCIONARIO                                         00000000000856AFA
    0000024183111120191415000000000006167 -  DATA
    0000024183111120191415000000000006167  -  HORA
    0000024183111120191415000000000006167  - CPF
    99999999900000000000000000000000000000000000000000000387
    ANNOD2JF5FIA2IJFHQE5VN3BXAF6AFMVG4AHXZPC4DTZP76EBOUIAATL55K6LHMO

    Select SubString(Registro,178,10) As Data,
           SubString(Registro,215,4) As Hora,
           SubString(Registro,255,11) As CPF
    From Importado
    Não sei se as posições iniciais estão certas, além disso, se repararmos na mesma linha: 0000024183111120191415000000000006167, podemos identificar as três partes que você deseja reaproveitar.


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    • Sugerido como Resposta IgorFKModerator sexta-feira, 6 de dezembro de 2019 19:00
    quarta-feira, 20 de novembro de 2019 11:05