Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
how to insert data from qlikview to oracle database ?
You can generate CSV from Qlikview and use SQL loader to insert data into oracle.
For large data insertion above method is suitable.
No way.. You cannot insert any data form Qlikview to Any database..
Selva
Hi Neha,
can u pls inform me how to use SQL Loader to insert data into oracle
Rgds,
Hardik
some answers in the community
http://community.qlik.com/search.jspa?q=write+back+to+database
for an overview on sqlloader go to orafaq .com site
You can create a tmp table with line number, than create one header and call SQL insert, but it's so slow.
some like this
for line = i to n
SQL insert into table (field1, field2, field3) Values (value1, value2, value3);
end
P.s.: (value1, value2, value3) its a line of your tmp table $tmp
than:
for line = i to x
SQL insert into table (field1, field2, field3) Values ($tmp);
end
SUB CriarLinhasParaInsert
//Limpando variáveis utizadas para seguir índice e criar script de inserção
LET linha = 1;
LET dados = '';
//Criando índice de linhas a serem processadas
Linhas: LOAD Concat(DISTINCT linha, ',', linha) as linhas Resident tabela;
LET linhas = FieldValue('linhas',1);
DROP Table Linhas;
ENDSUB
//***************************************************************************************************
NULLASVALUE *;
SET nulldisplay = 'null';
//***************************************************************************************************
SUB InserirLinhas
//Percorrendo cada linha do índice criado anteriormente
FOR Each linha in $(linhas)
//Lendo cada linha concatenada de acordo com o índice
LET dados = Replace(Peek('campo',$(linha)-1,'tabela'), Chr(39) & 'null' & Chr(39), 'null');
//Inserindo a linha na base
SQL $(linhaDados)
VALUES (
$(dados)
);
SQL Commit;
LET vInsertAtual = '$(linha)' & ' / ' & NoOfRows('tabela') & ' - ' & '$(vTabela)';
TRACE $(vInsertAtual);
NEXT
DROP Table tabela;
ENDSUB
//=========================================================================
//***************************************************************************************************
LET linha = 1;
LET dados = '';
LET linhaDados = '';
LET vTabela = 'F02478.QV_STG_RF_BDV02_CAIXA';
//***************************************************************************************************
LET linhaDados = 'INSERT INTO F02478.QV_STG_RF_BDV02_CAIXA
(
"REFERENCIA RF",
"DATA CAIXA",
"PATROCINADORA / PLANO",
COMPROMISSADA,
COMPROMISSADA1,
JUROS,
RECEBIMENTO,
PAGAMENTOS,
PRÉVIA,
Patrimônio,
ITAPOA,
filename
)';
tabela:
LOAD
RowNo() as linha,
Chr(39) & [REFERENCIA RF] & Chr(39) & ',' &
Chr(39) & [DATA CAIXA] & Chr(39) & ',' &
Chr(39) & [PATROCINADORA / PLANO] & Chr(39) & ',' &
Chr(39) & COMPROMISSADA & Chr(39) & ',' &
Chr(39) & COMPROMISSADA1 & Chr(39) & ',' &
Chr(39) & JUROS & Chr(39) & ',' &
Chr(39) & RECEBIMENTO & Chr(39) & ',' &
Chr(39) & PAGAMENTOS & Chr(39) & ',' &
Chr(39) & PRÉVIA & Chr(39) & ',' &
Chr(39) & Patrimônio & Chr(39) & ',' &
Chr(39) & ITAPOA & Chr(39) & ',' &
Chr(39) & filename() & Chr(39) as campo
FROM
[$(vDiretorio)$(vArquivo)]
(ooxml, embedded labels, header is 4 lines, table is CAIXA)
WHERE len([REFERENCIA RF]) > 0
;
CALL CriarLinhasParaInsert;
CALL InserirLinhas;
//***************************************************************************************************
Hardik
I suggest extreme caution writing data back to any source database.
QlikView is designed for BI / Data Discovery as in reading, transforming and visualizing data, but NOT writing data.
There are ways to frig write back, but be really careful. If you don't properly understand Oracle SQL Loader, then get somebody who does to mastermind it. Destroying a database with dodgy updates is possible and it is embarrassing to admit to having done that and asking for a database restore
I do not recommend using QlikView to enter data in database, but it is possible.
To insert data in data base i use the SSIS (Microsoft Integration Services).
I totally agree!