Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
if you paste into script only
OLEDB CONNECT TO ........
Brokerage_Table:
SQL SELECT *
FROM Metastorm.dbo.tblBrokerageSystem;
Load
AccountExec,
...
...
...
does your script reload succesfully?
first of all
you've got
exit Script;
before connection string so your OLE DB script will not execute
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.
I'm not sure but
try move load statement before sql select
Brokerage_Table:
SQL SELECT *
FROM Metastorm.dbo.tblBrokerageSystem;
Load
AccountExec,
...
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
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;