How can i read from a qvd the fields and store the data into an oracle table

[geogou1973]

Hello.

A few days ago i found the solution how to insert data from an input box into an oracle table with the vbscript module below.

sub oracle

set myStore = ActiveDocument.Variables("STORE")

insStore = myStore.GetContent.String 

set mySales = ActiveDocument.Variables("SALES")

insSales = mySales.GetContent.String 

set db = CreateObject("ADODB.Connection")

db.open "DSN=xxx;UID=xxx;PWD=xxx"

set Orderfile = db.execute("INSERT INTO QLIKVIEW3 (STORE, SALES) VALUES (' " & insStore & " ', ' " & insSales & " ');")

set Orderfile = db.execute("COMMIT;")

db.Close

end sub

Now i'm trying to find how can i store the data that the fields of a qvd have into an oracle table with the script below but does not pass.

sub oracle

set myStoreid = ActiveDocument.Fields("STORE_ID")

set mySalesid = ActiveDocument.Fields("SALES_ID")

set db = CreateObject("ADODB.Connection")

db.open "DSN=xxx;UID=xxx;PWD=xxx"

set Orderfile = db.execute("INSERT INTO QLIKVIEW4 (STORE, SALES) VALUES (' " & myStoreid & " ', ' " & mySalesid & " ');")

set Orderfile = db.execute("COMMIT;")

db.Close

end sub

Has anyone any idea how this can be done ?

Thank you in advance.

[Gysbert_Wassenaar]

1. load the data from the qvd file into a table:

     MyTable:

     load * from myqvd.qvd (qvd);

2. store the table in a csv file

     Store MyTable into mycsv.csv (txt);

3. import the data from the csv file into the oracle database

     See this article: www.orafaq.com/node/848

     or this blog post: blog.mclaughlinsoftware.com/2012/03/05/oracle-csv-imports/

[geogou1973]

Hello Gysbert.

The data that i manage is huge.

Is there any other way to read the field like the variables and after to insert the data into an oracle table ?

[Gysbert_Wassenaar]

There are hundreds of ways. But using vbscript macros is one of the worst solutions you can choose. Especially if the amount of data is huge. Creating an External Table in Oracle or using sql*loader or another ETL tool to load data from a csv file into an oracle database are far better options. A couple of ETL tools can also read qvd files so you woudn't need to export to csv first.

[#5]

With QlikView Expressor, you can read QVD files directly and also write Oracle tables, easily. Give it a try.

[mvaugusto]

I think the QlikView Expressor can help you.