none
Bulk Insert em nível avançado RRS feed

  • Pergunta

  • Olá pessoal,

    Sou novo neste forum e também no SQL e preciso muito da ajuda de vocês. Pelo que pude ver, há muita gente que entende muito de SQL por aqui.

    Eu tenho 6 tipos de arquivos .csv, cada qual com uma característica (6 tipos de arquivos). Eles estão todos dentro de um diretório/pasta e eu preciso que o SQL vá até esta pasta, leia os arquivos e insira em uma tabela e sem duplicações.

    2 tipos de arquivos tem cabeçalhos, nestes casos teremos que ler os valores do cabeçalho e criar colunas para que eles possam ser inseridos na tabela final. Cada arquivo deste tipo tem 1 cabeçalho.

    Tenho os arquivos hospedados no Hotmail disponíveis para download no link:

    https://skydrive.live.com/redir?resid=A62BDCB0D7C0E66A!173&authkey=!APUuAJNadAzlSMo

    Arquivos:
    1- urso47Test02.csv - é o arquivo que eu quero importar
    2- The New DelimitedSplit8K Function.sql - arquivo que trata os delimitadores
    3- RawData8000.fmt - arquivo BCP que trata importação dos dados
    4- GetFileType02.sql - sintaxe do SQL

    Criando um diretório "Temp" dentro do "C:\" da sua máquina e copiando a pasta descompactada dá para rodar as sintaxes sem muitas alterações. O caminho é: "C:\Temp\sm\".

    Segue abaixo a sintaxe para verificação, vamos começar com um dos tipos de arquivo. Alguém se habilita a encarar este desafio?

    Abraços e obrigado!

    --== arquivo GetFileType02.sql
    --== para testar a SP criada: EXEC dbo.GetFileType02 'C:\Temp\sm\urso47Test02.csv'
    
     
     DROP PROCEDURE dbo.GetFileType02
     GO
     
     CREATE PROCEDURE dbo.GetFileType02
     
        @pFullPath VARCHAR(500)
        AS
        SET NOCOUNT ON;
        SET DATEFORMAT MDY;
    
     CREATE TABLE #RawData
            (
            RowNum INT IDENTITY(1,1),
            LineData VARCHAR(8000)
            )
    ;
    DECLARE @ProcName   VARCHAR(128),
            @RowCount   INT,
            @SQL        VARCHAR(8000)
    ;
     SELECT @ProcName  = OBJECT_NAME(@@PROCID),
            @SQL       = REPLACE('
       BULK INSERT #RawData
       FROM ''<<@pFullPath>>''
       WITH (
            BATCHSIZE       = 2000000000, 
            CODEPAGE        = ''RAW'',
            DATAFILETYPE    = ''char'',
          --ERRORFILE       = ''<<@pFullPath>>'',
            FIELDTERMINATOR = ''\t'',
            FORMATFILE      = ''C:\Temp\sm\RawData8000.fmt'',
            MAXERRORS       = 2000000000,
            ROWTERMINATOR   = ''\n'',
            TABLOCK
            );'
            ,'<<@pFullPath>>',@pFullPath)
    ;
       EXEC (@SQL)
    ;
     SELECT #ListaEstat =
                MAX(
                    CASE 
                    WHEN RowNum = 1 AND LineData LIKE '[#Lista Estat]%'
                    THEN LTRIM(RTRIM(SUBSTRING(LineData,CHARINDEX('.',LineData)+1,8000)))
                    ELSE ''
                    END
                ),
            #FormatVersion =
                MAX(
                    CASE 
                    WHEN RowNum = 2 AND LineData LIKE '[Format Version]:%'
                    THEN LTRIM(RTRIM(SUBSTRING(LineData,CHARINDEX(':',LineData)+1,8000)))
                    ELSE ''
                    END
                ),
            #DataObtida =
                CAST(MAX(
                    CASE 
                    WHEN RowNum = 3 AND LineData LIKE '[#Data Obtida]:%'
                    THEN LTRIM(RTRIM(SUBSTRING(LineData,CHARINDEX(':',LineData)+1,8000)))
                    ELSE ''
                    END
                ) AS DATETIME),
            #NomedoDisposit =
                MAX(
                    CASE 
                    WHEN RowNum = 4 AND LineData LIKE '[#Nome do Disposit.]:%'
                    THEN LTRIM(RTRIM(SUBSTRING(LineData,CHARINDEX(':',LineData)+1,8000)))
                    ELSE ''
                    END
                ),
            #Endereço = 
                MAX(
                    CASE 
                    WHEN RowNum = 5 AND LineData LIKE '[#Endereço]:%'
                    THEN LTRIM(RTRIM(SUBSTRING(LineData,CHARINDEX(':',LineData)+1,8000)))
                    ELSE ''
                    END
                )
       INTO #Header
       FROM #RawData
      WHERE RowNum BETWEEN 1 AND 5
    ;
     SELECT @RowCount = COUNT(*)
       FROM #Header
      WHERE #ListaEstat		> ''      
        AND #FormatVersion		> ''        
        AND #DataObtida		> '1900'    
        AND #NomedoDisposit		> ''       
        AND #Endereço		> ''       
    ;
         IF @RowCount <> 1
      BEGIN 
            RAISERROR('ERRO:[%s]Problema no header %s',16,1,@ProcName,@pFullPath);
            RETURN;
        END
    ;
         IF NOT EXISTS (SELECT 1 FROM #RawData WHERE RowNum = 7 AND LineData = '#Utilizador,	[Nome,	Tot. de Impressões,	P & B(Tot. de Impressões),	Cor(Tot. de Impressões),	P & B: Resultado(Tot. de Impressões),	Cor: Resultado(Tot. de Impressões),	Total de Preto e branco(Copidora/Servidor de Documentos),	Preto e branco(Formato pequeno)(Copidora/Servidor de Documentos),	Preto e branco(Formato grande)(Copidora/Servidor de Documentos),	Total de Uma cor(Copidora/Servidor de Documentos),	Cor Única(Formato pequeno)(Copidora/Servidor de Documentos),	Cor Única(Formato grande)(Copidora/Servidor de Documentos),	Total de Duas cores(Copidora/Servidor de Documentos),	Duas cores(Formato pequeno)(Copidora/Servidor de Documentos),	Duas cores(Formato grande)(Copidora/Servidor de Documentos),	Total de Cor integral(Copidora/Servidor de Documentos),	Cor Integral(Formato pequeno)(Copidora/Servidor de Documentos),	Cor Integral(Formato grande)(Copidora/Servidor de Documentos),	Total de Preto e branco(Impressora),	Preto e branco(Formato pequeno)(Impressora),	Preto e branco(Formato grande)(Impressora),	Total de Uma cor(Impressora),	Cor Única(Formato pequeno)(Impressora),	Cor Única(Formato grande)(Impressora),	Total de Duas cores(Impressora),	Duas cores(Formato pequeno)(Impressora),	Duas cores(Formato grande)(Impressora),	Total a Cores(Impressora),	Cor(Formato pequeno)(Impressora),	Cor(Formato grande)(Impressora),	Total de Scanner(Scanner),	Total de Preto e branco(Scanner),	Preto e branco(Formato pequeno)(Scanner),	Preto e branco(Formato grande)(Scanner),	Total a Cores(Scanner),	Cor(Formato pequeno)(Scanner),	Cor(Formato grande)(Scanner),	Total de Preto e branco(Fax),	Preto e branco(Formato pequeno)(Fax),	Preto e branco(Formato grande)(Fax),	Total a Cores(Fax),	Cor(Formato pequeno)(Fax),	Cor(Formato grande)(Fax),	Transmissão(Fax),	Custos(Fax),	Volume utilizado(Limitação de volumes de utilização para impressão),	Valor limite(Limitação de volumes de utilização para impressão)
    ')
      BEGIN 
            RAISERROR('ERRO:[%s] Faltam detalhes no header %s',16,1,@ProcName, @pFullPath);
            RETURN;
        END
    ;
     SELECT [#Lista Estat]      								= MAX(hdr.[#Lista Estat]),
            [#Format Version] 								= MAX(hdr.[#Format Version]),
            [#Data Obtida]          							= MAX(hdr.[#Data Obtida]),
            [#Nome do Disposit.]    							= MAX(hdr.[#Nome do Disposit.]),
            [#Endereço]            								= MAX(hdr.[#Endereço]),
            [#Utilizador]      								= MAX(CAST(CASE WHEN split.ItemNumber = 1 THEN Item ELSE '' END AS VARCHAR(10))),
    	[Nome]      									= MAX(CAST(CASE WHEN split.ItemNumber = 2 THEN Item ELSE '' END AS VARCHAR(10))),
    	[Tot. de Impressões]      							= MAX(CAST(CASE WHEN split.ItemNumber = 3 THEN Item ELSE '' END AS INT)),
    	[P & B(Tot. de Impressões)]      						= MAX(CAST(CASE WHEN split.ItemNumber = 4 THEN Item ELSE '' END AS INT)),
    	[Cor(Tot. de Impressões)]      							= MAX(CAST(CASE WHEN split.ItemNumber = 5 THEN Item ELSE '' END AS INT)),
    	[P & B: Resultado(Tot. de Impressões)]      					= MAX(CAST(CASE WHEN split.ItemNumber = 6 THEN Item ELSE '' END AS INT)),
    	[Cor: Resultado(Tot. de Impressões)]      					= MAX(CAST(CASE WHEN split.ItemNumber = 7 THEN Item ELSE '' END AS INT)),
    	[Total de Preto e branco(Copidora/Servidor de Documentos)]			= MAX(CAST(CASE WHEN split.ItemNumber = 8 THEN Item ELSE '' END AS INT)),
    	[Preto e branco(Formato pequeno)(Copidora/Servidor de Documentos)]		= MAX(CAST(CASE WHEN split.ItemNumber = 9	THEN Item ELSE '' END AS INT)),
    	[Preto e branco(Formato grande)(Copidora/Servidor de Documentos)]		= MAX(CAST(CASE WHEN split.ItemNumber = 10 THEN Item ELSE '' END AS INT)),
    	[Total de Uma cor(Copidora/Servidor de Documentos)]				= MAX(CAST(CASE WHEN split.ItemNumber = 11	THEN Item ELSE '' END AS INT)),
    	[Cor Única(Formato pequeno)(Copidora/Servidor de Documentos)]			= MAX(CAST(CASE WHEN split.ItemNumber = 12 THEN Item ELSE '' END AS INT)),
    	[Cor Única(Formato grande)(Copidora/Servidor de Documentos)]			= MAX(CAST(CASE WHEN split.ItemNumber = 13 THEN Item ELSE '' END AS INT)),
    	[Total de Duas cores(Copidora/Servidor de Documentos)]      			= MAX(CAST(CASE WHEN split.ItemNumber = 14 THEN Item ELSE '' END AS INT)),
    	[Duas cores(Formato pequeno)(Copidora/Servidor de Documentos)]		 	= MAX(CAST(CASE WHEN split.ItemNumber = 15	THEN Item ELSE '' END AS INT)),
    	[Duas cores(Formato grande)(Copidora/Servidor de Documentos)]		 	= MAX(CAST(CASE WHEN split.ItemNumber = 16	THEN Item ELSE '' END AS INT)),
    	[Total de Cor integral(Copidora/Servidor de Documentos)]      			= MAX(CAST(CASE WHEN split.ItemNumber = 17 THEN Item ELSE '' END AS INT)),
    	[Cor Integral(Formato pequeno)(Copidora/Servidor de Documentos)]	 	= MAX(CAST(CASE WHEN split.ItemNumber = 18 THEN Item ELSE '' END AS INT)),
    	[Cor Integral(Formato grande)(Copidora/Servidor de Documentos)]			= MAX(CAST(CASE WHEN split.ItemNumber = 19 THEN Item ELSE '' END AS INT)),
    	[Total de Preto e branco(Impressora)]      					= MAX(CAST(CASE WHEN split.ItemNumber = 20 THEN Item ELSE '' END AS INT)),
    	[Preto e branco(Formato pequeno)(Impressora)]		  			= MAX(CAST(CASE WHEN split.ItemNumber = 21 THEN Item ELSE '' END AS INT)),
    	[Preto e branco(Formato grande)(Impressora)]      				= MAX(CAST(CASE WHEN split.ItemNumber = 22 THEN Item ELSE '' END AS INT)),
    	[Total de Uma cor(Impressora)]     						= MAX(CAST(CASE WHEN split.ItemNumber = 23 THEN Item ELSE '' END AS INT)),
    	[Cor Única(Formato pequeno)(Impressora)]      					= MAX(CAST(CASE WHEN split.ItemNumber = 24 THEN Item ELSE '' END AS INT)),
    	[Cor Única(Formato grande)(Impressora)]      					= MAX(CAST(CASE WHEN split.ItemNumber = 25 THEN Item ELSE '' END AS INT)),
    	[Total de Duas cores(Impressora)]      						= MAX(CAST(CASE WHEN split.ItemNumber = 26 THEN Item ELSE '' END AS INT)),
    	[Duas cores(Formato pequeno)(Impressora)]	    				= MAX(CAST(CASE WHEN split.ItemNumber = 27 THEN Item ELSE '' END AS INT)),
    	[Duas cores(Formato grande)(Impressora)]		  			= MAX(CAST(CASE WHEN split.ItemNumber = 28 THEN Item ELSE '' END AS INT)),
    	[Total a Cores(Impressora)]      						= MAX(CAST(CASE WHEN split.ItemNumber = 29 THEN Item ELSE '' END AS INT)),
    	[Cor(Formato pequeno)(Impressora)]      					= MAX(CAST(CASE WHEN split.ItemNumber = 30 THEN Item ELSE '' END AS INT)),
    	[Cor(Formato grande)(Impressora)]      						= MAX(CAST(CASE WHEN split.ItemNumber = 31 THEN Item ELSE '' END AS INT)),
    	[Total de Scanner(Scanner)]      						= MAX(CAST(CASE WHEN split.ItemNumber = 32 THEN Item ELSE '' END AS INT)),
    	[Total de Preto e branco(Scanner)]      					= MAX(CAST(CASE WHEN split.ItemNumber = 33 THEN Item ELSE '' END AS INT)),
    	[Preto e branco(Formato pequeno)(Scanner)]	     				= MAX(CAST(CASE WHEN split.ItemNumber = 34 THEN Item ELSE '' END AS INT)),
    	[Preto e branco(Formato grande)(Scanner)]		   			= MAX(CAST(CASE WHEN split.ItemNumber = 35 THEN Item ELSE '' END AS INT)),
    	[Total a Cores(Scanner)]      							= MAX(CAST(CASE WHEN split.ItemNumber = 36 THEN Item ELSE '' END AS INT)),
    	[Cor(Formato pequeno)(Scanner)]      						= MAX(CAST(CASE WHEN split.ItemNumber = 37 THEN Item ELSE '' END AS INT)),
    	[Cor(Formato grande)(Scanner)]      						= MAX(CAST(CASE WHEN split.ItemNumber = 38 THEN Item ELSE '' END AS INT)),
    	[Total de Preto e branco(Fax)]      						= MAX(CAST(CASE WHEN split.ItemNumber = 39 THEN Item ELSE '' END AS INT)),
    	[Preto e branco(Formato pequeno)(Fax)]      					= MAX(CAST(CASE WHEN split.ItemNumber = 40 THEN Item ELSE '' END AS INT)),
    	[Preto e branco(Formato grande)(Fax)]      					= MAX(CAST(CASE WHEN split.ItemNumber = 41 THEN Item ELSE '' END AS INT)),
    	[Total a Cores(Fax)]      							= MAX(CAST(CASE WHEN split.ItemNumber = 42 THEN Item ELSE '' END AS INT)),
    	[Cor(Formato pequeno)(Fax)]      						= MAX(CAST(CASE WHEN split.ItemNumber = 43	THEN Item ELSE '' END AS INT)),
    	[Cor(Formato grande)(Fax)]      						= MAX(CAST(CASE WHEN split.ItemNumber = 44 THEN Item ELSE '' END AS INT)),
    	[Transmissão(Fax)]      							= MAX(CAST(CASE WHEN split.ItemNumber = 45 THEN Item ELSE '' END AS INT)),
    	[Custos(Fax)]      								= MAX(CAST(CASE WHEN split.ItemNumber = 46 THEN Item ELSE '' END AS INT)),
    	[Volume utilizado(Limitação de volumes de utilização para impressão)]		= MAX(CAST(CASE WHEN split.ItemNumber = 47 THEN Item ELSE '' END AS INT)),
    	[Valor limite(Limitação de volumes de utilização para impressão)]		= MAX(CAST(CASE WHEN split.ItemNumber = 48 THEN Item ELSE '' END AS INT))    
    FROM #RawData rd
      CROSS JOIN #Header hdr
      CROSS APPLY (
                   SELECT ItemNumber, 
                          Item = REPLACE(REPLACE(Item,'[','') ,']','')
                     FROM dbo.DelimitedSplit8K(LineData,',')
                  ) split
      WHERE rd.RowNum     > 7
        AND LEN(LineData) > 1
      GROUP BY rd.RowNum
    ;
    GO

    Erro:
    Msg 50000, Level 16, State 1, Procedure GetFileType02, Line 92
    ERRO:[GetFileType02]Problema no header C:\Temp\sm\urso47Test02.csv


    O erro está em algum lugar aqui, porém já fiz diversas alterações e não consegui resolver:
    SELECT @RowCount = COUNT(*)  --== Linha 85
    FROM #Header
        WHERE #ListaEstat > ''      
        AND #FormatVersion > ''        
        AND #DataObtida > '1900'    
        AND #NomedoDisposit > ''       
        AND #Endereço > ''       ; --== Linha 92

    sábado, 5 de janeiro de 2013 17:53

Todas as Respostas

  • Olá,

    Você ja tentou analisar esse seu problema utilizando o Integration Services? Lá você pode criar scripts em vbs se for o caso e também existem steps mais robustos para ler todos os arquivos de um diretório como o For Each Loop Container.

    Se a resposta foi útil, classifique-a


    Att,
    Marcos Freccia [MTA|MCTS|MCITP|MCT SQL Server 2008]
    Blog|Twitter
    Assine também os feeds clicando aqui

    sexta-feira, 11 de janeiro de 2013 13:18
  • Poderia tentar executar tua rotina com os arquivos sem cabeçalho, pelo visto o erro provavelmente seja ali e também tu consegue ver se o restante da rotina esta consistente.


    --
    Marcus Vinícius Bittencourt
    blog: isqlserver.wordpress.com
    www.sqlserverRS.com.br

    segunda-feira, 14 de janeiro de 2013 16:51
  • Urso47,

    As definições configuradas no cabeçalho do arquivo estão de acordo com a disposição e tipos de dados utilizados?


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

    segunda-feira, 28 de janeiro de 2013 15:20