4 Replies Latest reply: Sep 14, 2011 9:19 PM by ianmcgivern RSS

    Fetch QVD File and Store in a new database

    Felipe Ruiz

      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

          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

              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
                  Felipe Ruiz

                  Hi,

                   

                  Could you give an example how do that ?

                   

                  Thanks

                    • Fetch QVD File and Store in a new database

                      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