Can we insert data or records from qlikview to Database

[Anonymous]

Hi All,

from excel files i am loading data to qlikview and after calculation/manupulation  i need this data to be written back to database.

can we do this in qlikview?

[Sokkorn]

Hi,

Check this url may do your job

http://community.qlik.com/message/135858

http://www.citagus.com/citagus/blog/qlikview-writing-back-to-database/

Regards,

Sokkorn

[#3]

Hi,

It is possible to insert data into database through qlikview macro script.

Use following script to call a procedure in database

VB Script:

Set objADO = CreateObject("ADODB.Connection")

'Establish a connection

objADO.Open "connection_string"

'Set SQL

strSQL="call procedure"

'Begin Transaction

objADO.BeginTrans

'Execute Transaction

objADO.Execute strSQL, iAffected

objADO.CommitTrans

objADO.Close

Procedure:

Inside the procedure have some insert statements which in turn insert the data into database.

[#4]

Hi,

Use the following macro script to get some values from sheet passed as parameter to database procedure

Sub Update_Comment

Dim objADO

Dim objRs

Dim strSQL

vcomment=ActiveDocument.GetVariable("a").GetContent.String

vfield = ActiveDocument.GetVariable("b").GetContent.String

'Create ADO Object

Set objADO = CreateObject("ADODB.Connection")

'Establish a connection

objADO.Open "connection_string"

'Set SQL

strSQL="call procedure('"&vcomment&"','"&vfield&"')"

'Begin Transaction

objADO.BeginTrans

'Execute Transaction

objADO.Execute strSQL, iAffected

objADO.CommitTrans

objADO.Close

end sub

[#5]

HI, if You dont want to use VB, You can execute SQL in the LOAD Script

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Persist Security ...

TABLE:

LOAD * INLINE [

    FIELD1, FIELD2

    ROW1, NR1

    ROW2, NR2

    ROW3, NR3

];

ROWS_COUNT:

LOAD COUNT(FIELD1) AS ANZ RESIDENT TABLE;

LET V_ROWS = Fieldvalue('ANZ',1);

DROP TABLE ROWS_COUNT; //LET V_ROW = 1;

FOR V_ROW = 1 TO V_ROWS

LET V_FIELD1 = Fieldvalue('FIELD1',V_ROW);

LET V_FIELD2 = Fieldvalue('FIELD2',V_ROW);

SQL INSERT INTO TEST_TABLE1(FIELD1,FIELD2) VALUES('$(V_FIELD1)','$(V_FIELD2)');

NEXT;

Importent: If the table contains unique fields, Qlikview won't get any value for the 1+n fieldvalues.Therefore you has to write a exception with two variables V_FIELD1 V_FIELD_1_OLD

[#6]

Hi,

Why FieldValue is holding repeated values for only one time ?

Any idea on this.

Thank in advance.

[#7]

Hi,

use peek instead of FieldValue. You also don't need the ROWS_COUNT table:

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Persist Security ...

TABLE:

LOAD * INLINE [

    FIELD1, FIELD2

    ROW1, NR1

    ROW2, NR2

    ROW3, NR3

];

For i= 0 to NoOfRows('TABLE') -1

          LET V_FIELD1 = peek('FIELD1',$(i),'TABLE');

          LET V_FIELD2 = peek('FIELD2',$(i),'TABLE');       

          SQL INSERT INTO TABLE(FIELD1,FIELD2) VALUES('$(V_FIELD1)','$(V_FIELD2)');

NEXT

[#8]

Hi

I've just come across this post while trying to find a way to write data from QV to an SQL database and your script certainly seems to insert data into a table.  However, when I insert a truncate line

SQL TRUNCATE Table SQL_Training.Table_Test_SQL_Load;

In the script it appears to be skipped when reloaded and instead I just keep inserting additional rows into the table.  Any thoughts?

Cheers

Gareth

[pascal_theurot]

Hi

Have you tried OLEDB connexion instead of ODBC ?

[mjr6662000]

hi

i do it but not work.

i think problem in line

objADO.Open "connection_string"

please send me a sample file