topic Re: how to insert data from qlikview to oracle database ? in QlikView

how to insert data from qlikview to oracle database ?

Fri, 20 Nov 2020 18:02:30 GMT

how to insert data from qlikview to oracle database ?

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

neha_sri — Sat, 28 Jun 2014 06:08:27 GMT

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

For large data insertion above method is suitable.

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

selvakumarsr — Sat, 28 Jun 2014 06:10:04 GMT

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

Selva

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

Sat, 28 Jun 2014 08:36:23 GMT

Hi Neha,

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

Rgds,

Hardik

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

maxgro — Sat, 28 Jun 2014 19:39:07 GMT

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

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

mvaugusto — Wed, 03 Sep 2014 16:30:31 GMT

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

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

mvaugusto — Wed, 03 Sep 2014 16:33:06 GMT

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

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 ?

Anonymous — Wed, 03 Sep 2014 16:37:20 GMT

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

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

mvaugusto — Wed, 03 Sep 2014 16:41:09 GMT

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

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

mvaugusto — Wed, 03 Sep 2014 16:41:44 GMT

I totally agree!

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

Anonymous — Mon, 16 Mar 2015 16:58:39 GMT

Yes You can...

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

buzzy996 — Mon, 16 Mar 2015 17:05:02 GMT

it's big thing,but u can achieve through web services.

but in other way,some one said above if u like,

1.export from qlikview into excel

2. load from excel into ur database table

(For this:

1. start

2.Program

3.sql import and export wizard

4. set ur authorization details

5. select ur excel file

6. click finish).

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

Anonymous — Mon, 16 Mar 2015 18:12:40 GMT

Yes.

You can also export to XML and don't use excel.

You can also use a program with capture XML generated and insert it's contents into DB.

So You Can....

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

Tue, 06 Oct 2015 16:31:06 GMT

Hello,

To insert data into ORACLE, 2 ways :

1. ODBC : add "MODE WRITE" in ODBC Connection string like below

ODBC

CONNECT32 TO [MICDW;DBQ=XXXXXXX] (XUserId is DJLJFDLKJDKLÖFLKÖFJD, XPassword is DFLHJ=DFKJKÖLKLDJF, Mode is write);

2. Use Oracle OLEDB driver , works well !!!

64-bit Oracle Data Access Components (ODAC) Downloads

http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html

Regards