topic Re: QlikView data into SQL in QlikView

QlikView data into SQL

Mon, 15 Jul 2013 13:27:37 GMT

Does anyone know a method or a way of putting QlikView data into SQL.

For example if i got data into QlikView from various sources (excel, sql, access) thereafter creating a straight table and wanting to put that into SQL?

Look forward to your replies.

Re: QlikView data into SQL

kaushiknsolanki — Mon, 15 Jul 2013 13:34:56 GMT

Hi,

Have a look at the link,

http://community.qlik.com/docs/DOC-2784

Regards,

Kaushik Solanki

Re: QlikView data into SQL

Mon, 15 Jul 2013 15:15:15 GMT

Hi, this only shows XML and not how to put that back into SQL

Re: QlikView data into SQL

marcus_sommer — Mon, 15 Jul 2013 15:30:36 GMT

See here http://community.qlik.com/message/135858#135858 or more here:

http://community.qlik.com/search.jspa?peopleEnabled=true&userID=&containerType=&container=&spotlight=true&q=write+in+database

http://community.qlik.com/search.jspa?peopleEnabled=true&userID=&containerType=&container=&spotlight=true&q=insert+database

- Marcus

Re: QlikView data into SQL

Mon, 15 Jul 2013 15:35:15 GMT

Hi, quite a few examples of this in the forum.

One way would be to use a vb macro linked to a button.

The user makes selections and with the button writes a predefined output back to an SQL database.

See the example macro below, and keep in mind that this example also has a truncate command in it.

You might want to remove that before testing as it empties the target table.

This is not very fast so don't try this with more then a 100K records or so.

The number,type,color fields are just some example fields to write back.

sub writeback ()

dim sServer, sConn, oConn,oRS

sServer="[SERVERNAME]"

sConn="Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=SERVERNAME;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;;Use Encryption for Data=False;Tag with column collation when possible=False;MARS Connection=False;DataTypeCompatibility=0;Trust Server Certificate=False"

Set oConn = CreateObject("ADODB.Connection")

oConn.Open sConn

Set oTT =CreateObject("ADODB.Recordset")

oTT.open "TRUNCATE TABLE [DATABASENAME].dbo.[TABLENAME]", oConn

Set oRS =CreateObject("ADODB.Recordset")

set table = ActiveDocument.GetSheetObject("TB01")

For RowIter = 1 to table.GetRowCount-1

set Number = table.GetCell(RowIter,0)

set Type = table.GetCell(RowIter,1)

set Color = table.GetCell(RowIter,2)

sqlstr="INSERT INTO [DATABASENAME].dbo.[TABLENAME] (Number,Type,Color) values('" & RowIter & "','" & Type.Text & "','" & Color.Text & "')"

oRS.open sqlstr, oConn

end sub

Re: QlikView data into SQL

Tue, 16 Jul 2013 10:18:10 GMT

does this go into QlikViewin the edit script section?

what is this:

Set oConn = CreateObject("ADODB.Connection")

oConn.Open sConn

and:

(Number,Type,Color) values('" & RowIter & "','" & Type.Text & "','" & Color.Text & "')"

oRS.open sqlstr, oConn