topic Fetch QVD File and Store in a new database in QlikView

Fetch QVD File and Store in a new database

julruiz123 — Wed, 14 Sep 2011 21:50:14 GMT

Hi,

I have the next situation

First i get the data from a qvd file

//CARGAR DATOS DE UN QVD

FACT:

LOAD

// tipoFactura,

// codigoFactura,

// codigoCliente,

// tipoVenta,

// Total,

// Iva,

// subTotal,

// fechaFactura,

// añoFactura,

// mesFactura,

// Moneda

añoFactura&'-'&mesFactura&'-'&'01' as fecha,

SUM(Total) as valor

FROM

[..\Qvd\SIHL01_TRA.qvd](qvd)

GROUP BY añoFactura, mesFactura;

Next i need to connect to postgres database and copy each record

//CONEXION SGI

ODBC CONNECT32 TO PostgreSQL30;

//fetch each record

SQL INSERT INTO sigshaio.sig.a(fecha,valor)

VALUES();

Someone know how to resolve this situation ??

Thanks!!

Fetch QVD File and Store in a new database

Wed, 14 Sep 2011 21:58:10 GMT

I'm pretty sure this isn't possible although I could very well be wrong. Anytime I have to store data from a QVD in a database I use a different ETL tool to get the job done.

Someone suggested possibly coding something in VB but I've never tried it myself. Sounds like a good idea though.

Fetch QVD File and Store in a new database

Wed, 14 Sep 2011 22:52:15 GMT

Another "Easier" but still "Dirty" way would be to dump your file from QVD to Excel and then write an intergration services package in SQL to import the data.

Fetch QVD File and Store in a new database

julruiz123 — Thu, 15 Sep 2011 00:14:28 GMT

Hi,

Could you give an example how do that ?

Thanks

Fetch QVD File and Store in a new database

Thu, 15 Sep 2011 01:19:24 GMT

The SQL part I can't give you example of unfortunately - I work with Object Oriented Database now and don't have an instance of MSSQL. Doing a simple search or checking your MSSQL Help will give you a sample of how to import CSV into SQL Server using SSIS. For the QlikView part - exporting data to Excel - you can use a macro. Below is some sample code that you can modify:

Sub ExportToCSV

‘Use only if you would like to export everything - remove if you want to export selection

Activedocument.ClearAll

vFile = "Drive:\Directory\File.csv"

vDirectory = "Drive:\Directory

' Create the File System Object

Set objFSO = CreateObject("Scripting.FileSystemObject")

'Test if the Directory exists, else create one

If objFSO.FolderExists(vDirectory) Then

Set objFolder = objFSO.GetFolder(vDirectory)

Else

Set objFolder = objFSO.CreateFolder(vDirectory)

End If

'Select the Object you would like to Export to CSV

set obj = ActiveDocument.GetSheetObject("CHWhateverID")

obj.ExportEx vFile, 1

End Sub