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

[#1]

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

[#2]

if you paste into script only

OLEDB CONNECT TO ........

Brokerage_Table:

SQL SELECT *

FROM Metastorm.dbo.tblBrokerageSystem;

Load

    AccountExec,

...

...

...

does your script reload succesfully?

[#3]

first of all

you've got

exit Script;

before connection string so your OLE DB script will not execute

[#4]

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.

[#5]

I'm not sure but

try move load statement before sql select

Brokerage_Table:

SQL SELECT *

FROM Metastorm.dbo.tblBrokerageSystem;

Load

    AccountExec,

...

[#6]

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

[#7 · Accepted solution]

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;