Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

how to insert data from qlikview to oracle database ?

how to insert data from qlikview to oracle database ?

13 Replies
neha_sri
Contributor II

Re: 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.

selvakumarsr
Contributor

Re: how to insert data from qlikview to oracle database ?

No way.. You cannot insert any data form Qlikview to Any database..

Selva

Not applicable

Re: how to insert data from qlikview to oracle database ?

Hi Neha,

can u pls inform me how to use SQL Loader to insert data into oracle

Rgds,

Hardik

MVP
MVP

Re: how to insert data from qlikview to oracle database ?

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

mvaugusto
Contributor

Re: how to insert data from qlikview to oracle database ?

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

mvaugusto
Contributor

Re: how to insert data from qlikview to oracle database ?

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;

//***************************************************************************************************

Re: how to insert data from qlikview to oracle database ?

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

mvaugusto
Contributor

Re: how to insert data from qlikview to oracle database ?

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).

mvaugusto
Contributor

Re: how to insert data from qlikview to oracle database ?

I totally agree!

Community Browser