how to insert data from qlikview to oracle database ?

[#1]

how to insert data from qlikview to oracle database ?

[neha_sri]

You can generate CSV from Qlikview and use SQL loader to insert data into oracle.

For large data insertion above method is suitable.

[selvakumarsr]

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

Selva

[#4]

Hi Neha,

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

Rgds,

Hardik

[maxgro]

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]

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]

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;

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

[Anonymous]

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]

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]

I totally agree!