Inquiridor
Bulk Insert em nível avançado
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
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- Sugerido como Resposta Junior Galvão - MVPMVP terça-feira, 11 de fevereiro de 2020 13:15
-
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- Sugerido como Resposta Junior Galvão - MVPMVP terça-feira, 11 de fevereiro de 2020 13:15
-
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]
