Importar Várias planilhas excel

[RICARDO BARBOSA machado]

Boa noite,

Tenho várias planilhas em excel com várias sheet que preciso importar para uma mesma tabela. Mais especificamente duas abas (BP e DRE) para duas tabelas com mesmo nome. Além disso preciso que ele grave o nome do arquivo que está sendo importado em todas as linhas da tabela.

[AlexandreNSilva]

Bom dia!

eu tenho um script que faz exatamente isso, da uma olhada e uma adaptada nele conforme a sua necessidade.

 

--************************************************************************************
--        PASSO1:  PROCEDIMENTO PARA TRATAMENTO ( IMPORTA AS OSS PARA BASE )
--************************************************************************************
PRINT'CRIA TABELA TMP'
PRINT CONVERT(VARCHAR(10), GETDATE(), 103) + ' ' +   CONVERT(VARCHAR(08), GETDATE(), 108)

IF EXISTS (SELECT NAME FROM TEMPDB.SYS.TABLES WHERE NAME LIKE '%#PERFORMANCE_TMP%') DROP TABLE #PERFORMANCE_TMP

CREATE TABLE #PERFORMANCE_TMP(
        [NOME AGENTE]                            VARCHAR(MAX) NULL
    ,    [ID AGENTE]                                VARCHAR(MAX) NULL
    ,    [N¨²MERO DE CHAMADAS ATENDIDAS]            VARCHAR(MAX) NULL
    ,    [TEMPO M¨¦DIO DE CONVERSA]                VARCHAR(MAX) NULL
    ,    [TEMPO M¨¦DIO OCUPADO]                    VARCHAR(MAX) NULL
    ,    [TEMPO M¨¦DIO ACW]                        VARCHAR(MAX) NULL
    ,    [TEMPO TOTAL LOGADO]                    VARCHAR(MAX) NULL
    ,    [TEMPO TOTAL DE CONVERSA]                VARCHAR(MAX) NULL
    ,    [TEMPO TOTAL OCIOSO]                    VARCHAR(MAX) NULL
    ,    [TEMPO TOTAL ACW]                        VARCHAR(MAX) NULL
    ,    [TEMPO TOTAL TOCANDO]                    VARCHAR(MAX) NULL
    ,    [TEMPO TOTAL EM PAUSA]                    VARCHAR(MAX) NULL
    ,    [TEMPO DE PAUSA AUTOMATICO]                VARCHAR(MAX) NULL
    ,    [TEMPO DE PAUSA  INTERVALO]                VARCHAR(MAX) NULL
    ,    [TEMPO DE PAUSA PARTICULAR]                VARCHAR(MAX) NULL
    ,    [TEMPO DE PAUSA PAUSA 10]                VARCHAR(MAX) NULL
    ,    [TEMPO DE PAUSA TREINAMENTO]            VARCHAR(MAX) NULL
    ,    [TEMPO DE PAUSA FEEDBACK]                VARCHAR(MAX) NULL
    ,    [TEMPO DE PAUSA APOIO/CEDIDO]            VARCHAR(MAX) NULL
    ,    [TEMPO DE PAUSA AMBULAT?RIO]            VARCHAR(MAX) NULL
    ,    [TEMPO DE PAUSA PRODUCT/OUTBOUND]        VARCHAR(MAX) NULL
    ,    [TEMPO DE PAUSA PROBLEMAS T?CNICOS]        VARCHAR(MAX) NULL
    ,    [N¨²MERO DE CHAMADAS CURTAS]            VARCHAR(MAX) NULL
    ,    [N¨²MERO DE CHAMADAS TRANSFERIDAS]        VARCHAR(MAX) NULL
    ,    [N¨²MERO DE CHAMADAS INTERNAS]            VARCHAR(MAX) NULL
    ,    [N¨²MERO DE CHAMADAS REALIZADAS]        VARCHAR(MAX) NULL
    ,    [TEMPO M¨¦DIO DAS CHAMADAS REALIZADAS]    VARCHAR(MAX) NULL
    ,    [PORCENTAGEM TEMPO PRODUTIVO]            VARCHAR(MAX) NULL
) ON [PRIMARY];

--==============================================================
-- CRIA TABELA TEMPORÁRIA PARA PEGAR O NOME DO ARQUIVO MAILING
--==============================================================

IF EXISTS (SELECT NAME FROM TEMPDB.SYS.TABLES WHERE NAME LIKE '%#BASE_PERFORMANCE_TMP%') DROP TABLE #BASE_PERFORMANCE_TMP

CREATE TABLE #BASE_PERFORMANCE_TMP ( PERFORMANCE VARCHAR(150), CONTADOR INT IDENTITY )
DECLARE @PASTA VARCHAR(MAX) SET @PASTA = 'MASTER.SYS.XP_CMDSHELL ''DIR \\SERVER-ATLAS\motiva\planejamento\MIS\RELATÓRIOS\Claro\Renta\2018\'+@MESREF+'\Performance\*.txt /B'''               
-- select  * from #BASE_ANALITICO_TMP
INSERT INTO #BASE_PERFORMANCE_TMP   EXEC(@PASTA)
DELETE FROM #BASE_PERFORMANCE_TMP  WHERE PERFORMANCE IS NULL
                                          
-- EXECUTA LOOPING QUE IMPORTA OS REGISTROS                            

PRINT'CRIA TABELA TMP DE INSERÇÃO'
PRINT CONVERT(VARCHAR(10), GETDATE(), 103) + ' ' +   CONVERT(VARCHAR(08), GETDATE(), 108) 

IF OBJECT_ID('TEMPDB..#PERFORMANCE_CLARO_RENTA') IS NOT NULL BEGIN DROP TABLE #PERFORMANCE_CLARO_RENTA END

CREATE TABLE #PERFORMANCE_CLARO_RENTA(
        [NOME AGENTE]                            VARCHAR(MAX) NULL
    ,    [ID AGENTE]                                INT NULL
    ,    [N¨²MERO DE CHAMADAS ATENDIDAS]            INT NULL
    ,    [TEMPO M¨¦DIO DE CONVERSA]                INT NULL
    ,    [TEMPO M¨¦DIO OCUPADO]                    INT NULL
    ,    [TEMPO M¨¦DIO ACW]                        INT NULL
    ,    [TEMPO TOTAL LOGADO]                    INT NULL
    ,    [TEMPO TOTAL DE CONVERSA]                INT NULL
    ,    [TEMPO TOTAL OCIOSO]                    INT NULL
    ,    [TEMPO TOTAL ACW]                        INT NULL
    ,    [TEMPO TOTAL TOCANDO]                    INT NULL
    ,    [TEMPO TOTAL EM PAUSA]                    INT NULL
    ,    [TEMPO DE PAUSA AUTOMATICO]                INT NULL
    ,    [TEMPO DE PAUSA  INTERVALO]                INT NULL
    ,    [TEMPO DE PAUSA PARTICULAR]                INT NULL
    ,    [TEMPO DE PAUSA PAUSA 10]                INT NULL
    ,    [TEMPO DE PAUSA TREINAMENTO]            INT NULL
    ,    [TEMPO DE PAUSA FEEDBACK]                INT NULL
    ,    [TEMPO DE PAUSA APOIO/CEDIDO]            INT NULL
    ,    [TEMPO DE PAUSA AMBULAT?RIO]            INT NULL
    ,    [TEMPO DE PAUSA PRODUCT/OUTBOUND]        INT NULL
    ,    [TEMPO DE PAUSA PROBLEMAS T?CNICOS]        INT NULL
    ,    [N¨²MERO DE CHAMADAS CURTAS]            INT NULL
    ,    [N¨²MERO DE CHAMADAS TRANSFERIDAS]        INT NULL
    ,    [N¨²MERO DE CHAMADAS INTERNAS]            INT NULL
    ,    [N¨²MERO DE CHAMADAS REALIZADAS]        INT NULL
    ,    [TEMPO M¨¦DIO DAS CHAMADAS REALIZADAS]    INT NULL
    ,    [PORCENTAGEM TEMPO PRODUTIVO]            VARCHAR(MAX) NULL
    ,    [DATA_DA_ACAO]                            datetime
    ,    SKILL                                    VARCHAR(MAX) NULL
    ,    DATA_IMPORTACAO                            DATETIME
) ON [PRIMARY];

DECLARE @I INT, @F INT, @ARQV VARCHAR(250)                           

SET @I = (SELECT MIN(CONTADOR) FROM #BASE_PERFORMANCE_TMP)                            
SET @F = (SELECT MAX(CONTADOR) FROM #BASE_PERFORMANCE_TMP)                            

WHILE (@I <= @F) BEGIN

SET @ARQV = (SELECT PERFORMANCE FROM #BASE_PERFORMANCE_TMP WHERE CONTADOR = @I)

EXEC ('                            
BULK INSERT #PERFORMANCE_TMP
FROM                         
''\\SERVER-ATLAS\MOTIVA\planejamento\MIS\RELATÓRIOS\Claro\Renta\2018\'+@MESREF+'\Performance\'+@ARQV+'''                            
WITH(FIRSTROW =2, CODEPAGE = 1252, FIELDTERMINATOR = ''\t'', ROWTERMINATOR = ''0x0d'') ') 

INSERT INTO #PERFORMANCE_CLARO_RENTA
SELECT
        *
    ,    convert(datetime, substring(LEFT(RIGHT(@ARQV, 12), 8),1,2)+'/'+substring(LEFT(RIGHT(@ARQV, 12), 8),3,2)+'/'+substring(LEFT(RIGHT(@ARQV, 12), 8),5,4), 103) [DATA_DA_ACAO]
    ,    SUBSTRING(REPLACE(@ARQV, 'DATA_SKILL', ''),1, LEN(REPLACE(@ARQV, 'DATA_SKILL', ''))-13)[SKILL]
    ,    GETDATE() [DATA_IMPORTACAO]
FROM
    #PERFORMANCE_TMP

SET @I = @I+1                            
                            
END

PRINT'INSERE OS REGISTROS NA TABELA TEMP FINAL'
PRINT CONVERT(VARCHAR(10), GETDATE(), 103) + ' ' +   CONVERT(VARCHAR(08), GETDATE(), 108)

INSERT INTO [Claro_Renta].[dbo].[PERFORMANCE_CLARO_RENTA]
SELECT * FROM #PERFORMANCE_CLARO_RENTA

END

[RICARDO BARBOSA machado]

Boa noite!

Obrigado pela resposta.

Eu me esqueci de mencionar que estou tentando fazer isso no ACCESS. Agora eu pergunto: Consigo colocar esse script nele?

[AlexandreNSilva]

hum..no access, bom você pode utilizar uma macro para fazer isso, tanto no excel quanto no access, a logica é mais ou menos essa que eu postei

[RICARDO BARBOSA machado]

Boa tarde,

Tentei colocar no ACCESS, mas não consegui. Pelo que pude ver no ACCESS existe a opção de SQL, mas só serve para relatórios. Quando eu insiro o seu script ele diz que está faltando o SELECT, que imagino, só servir para relatórios.

Na opção MACRO ele só aceita a visão VBA.

[RICARDO BARBOSA machado]

Boa noite,

Vamos por partes então.

Alguém sabe como posso gravar nas planilhas (são várias com várias abas) o nome do arquivo e o nome das abas?

Exemplo: arquivo1.xlsx com duas abas chamadas BP e DRE

=> Gravar na célula "P1" em todas as linhas o nome da aba "BP",  e na célula "Q1" em todas as linhas o nome "arquivo1",

 

[RICARDO BARBOSA machado]

Boa noite,

Encontrei esse código que quando salvo na célula ele traz o nome do arquivo e o nome da aba. Agora preciso salvá-lo em todos os arquivos e em todas as abas em uma range específica (P1:P200). Alguém sabe como posso fazer?

=EXT.TEXTO(CÉL("nome.arquivo");LOCALIZAR("[";CÉL("nome.arquivo");1)+1;(LOCALIZAR("]";CÉL("nome.arquivo");1))-(LOCALIZAR("[";CÉL("nome.arquivo");1)+1))

Editado Novembro 18, 2019 por RICARDO BARBOSA machado

[RICARDO BARBOSA machado]

Boa noite,

Encontrei um jeito de ele abrir o arquivo e deu certo. Porém, agora ele abre a planilha, salva a informação na célula P1 a P100 só da primeira planilha, mas não salva nas demais apesar de deixar as linhas delas selecionadas. Então ele salva a informação somente na primeira planilha, mas deixa as demais (nas células p1 até p100) selecionadas e não salva as informações.
Outro fato é que a informação que estou tentando salvar é uma fórmula que traz o nome do arquivo na célula (=CÉL("filename")), porém, ele salva a fórmula mas não executa o resultado. Ela só funciona quando se clica dentro da célula.

Teria como ajustar?

Dim rst As DAO.Recordset, strSQL As String, xls As Object
Dim strlivro$
Set xls = CreateObject("Excel.Application")
strlivro = CurrentProject.Path & "\" & Dir("E:\AGF\teste\" & "*.xl*") 'pasta com o projeto"
xls.Workbooks.Open (strlivro)
xls.Visible = True
xls.Worksheets("bp").Activate ' Nome da planilha
'strSQL = "SELECT * FROM PesoMedio;" 'consulta
strSQL = "SELECT tabela1.campo1 FROM tabela1;" 'criando a consulta
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
xls.ActiveSheet.Range("P1:P100").Select ' em que coluna e a partir de qual celula
xls.ActiveCell.CopyFromRecordset rst
xls.ActiveWorkbook.Save
xls.Application.Quit
Set xls = Nothing
Do While Not strlivro = ""
strlivro = Dir

Loop

End Sub