6 Replies Latest reply: Dec 4, 2012 1:23 PM by Wojciech Parzyszek RSS

    Can an application load data from Excel files and a SQL database?

      I have an application which loads data each month from QVD files, which are populated from Excel files. My next objective is to make a link between my application and a SQL database, which I have done. However, the application reloads but no data from the SQL database appears only the data from Excel files.

       

      My question is "can a single application load data from Excel files and a SQL database and if so how?"

       

      I have attached my script & application below (without data). The script linking the application to SQL database is named 'Brokerage Table'.

       

      Any help would be appreciated as this is the first time I have linked to a SQL database.

       

      Many thanks.

      John

        • Re: Can an application load data from Excel files and a SQL database?
          Wojciech Parzyszek

          if you paste into script only

           

          OLEDB CONNECT TO ........

           

          Brokerage_Table:

          SQL SELECT *

          FROM Metastorm.dbo.tblBrokerageSystem;

          Load

              AccountExec,

          ...

          ...

          ...

           

           

           

           

          does your script reload succesfully?

          • Re: Can an application load data from Excel files and a SQL database?
            Wojciech Parzyszek

            first of all

             

             

            you've got

             

             

            exit Script;

             

            before connection string so your OLE DB script will not execute

              • Re: Can an application load data from Excel files and a SQL database?

                Thanks Pari Pari, I didn't realise there was an 'Exit Script' function in the previous tab. I have removed this and now the application loads the data from both sources (QVD files from Excel spreadsheets and SQL database).

                 

                It doesn't seem to rename the fields though. Is this function available when inporting data from a SQL Database? I need this so fields already in the application can link, for example Inception Date & inception_date, should just be Inception Date.

                 

                Thanks again for your help.

                  • Re: Can an application load data from Excel files and a SQL database?
                    Wojciech Parzyszek

                    I'm not sure but

                     

                    try move load statement before sql select

                     

                     

                    Brokerage_Table:

                    SQL SELECT *

                    FROM Metastorm.dbo.tblBrokerageSystem;

                    Load

                        AccountExec,

                    ...

                      • Re: Can an application load data from Excel files and a SQL database?

                        I tried -

                         

                        LOAD

                                  AccountExec, 

                                  ...

                                  ...

                        FROM Metastorm.dbo.tblBrokerageSystem;

                        SQL SELECT *

                         

                        But I received an error saying the file cannot be found (as in the SQL database).

                         

                        With -

                         

                        Brokerage_Table:

                        SQL SELECT *

                        FROM Metastorm.dbo.tblBrokerageSystem;

                        LOAD

                                 AccountExec,

                                 ...

                         

                        The data is imported from the SQL database but doesn't appear to take any notice (ie. "as [Inception Date]" command).

                         

                        Any ideas or examples would be great.

                         

                        Many thanks.

                        John

                          • Re: Can an application load data from Excel files and a SQL database?
                            Wojciech Parzyszek

                            try this...

                             

                             

                            Brokerage_Table:

                            Load

                                      AccountExec,

                                assured as Insured,

                                brokerage_orig_ccy as [Brokerage Base],

                                brokerage_sett_ccy as [Brokerage SC],

                                brokerage_sterling_equiv as [Brokerage GBP],

                                budget_indicator,

                                client_ac_no,

                                client_gross,

                                client_net,

                                expiry_date as [Expiry Date],

                                extract_date as [Extract Date],

                                import_date as [Import Date],

                                Import_Unique_Ref,

                                date(date#(inception_date,'DD/MM/YYYY HH:MM:SS'),'DD-MMM-YYYY') as [Inception Date],

                                installment_no,

                                new_renewal,

                                old_trans_id,

                                orig_ccy_exch_rate,

                                orig_ccy_id,

                                prev_month,

                                reassured as [Re Insured],

                                relBudgetGBP,

                                risk_description,

                                risk_ref,

                                sett_ccy_exch_rate,

                                sett_ccy_id,

                                stats_1_id,

                                stats_1_id_official,

                                stats_1_id_original,

                                stats_2_id,

                                stats_2_id_official,

                                stats_2_id_original,

                                stats_3_id,

                                stats_3_id_official,

                                stats_3_id_original,

                                stats_4_id,

                                stats_4_id_official,

                                stats_4_id_original,

                                stats_5_id,

                                stats_5_id_official,

                                stats_5_id_original,

                                stats_6_id,

                                stats_6_id_official,

                                stats_6_id_original,

                                stats_7_id,

                                stats_7_id_official,

                                stats_7_id_original,

                                stats_8_id,

                                stats_8_id_official,

                                stats_8_id_original,

                                trans_description,

                                trans_id,

                                transaction_entry_date,

                                transaction_no,

                                TrueSterlingBkg,

                                txtBrokerageAccount,

                                Month(date(date#(extract_date,'DD/MM/YYYY HH:MM:SS'),'DD-MM-YYYY')) as Date,

                                Month(date(date#(extract_date,'DD/MM/YYYY HH:MM:SS'),'DD-MM-YYYY')) as Month,

                                year(date(date#(extract_date,'DD/MM/YYYY HH:MM:SS'),'DD-MMM-YYYY')) as Year,

                                txtYear as Year,

                                type_id,

                                update_date

                            ;

                             

                            SQL SELECT *

                            FROM Metastorm.dbo.tblBrokerageSystem;