I have created an APP and created an ODBC connection, pasted an SQL script into the data load editor and all works fine.
I then created a link to an Excel file and tried to load that data into the same APP and I get the error
Data has not been loaded. Please correct the error and try loading again.
App successfully saved.
I then created a new app and loaded the excel data successfully.
Can anyone help me identify the problem.
The complete script in the data load editor is for the ODBC
SET ThousandSep=','; SET DecimalSep='.'; SET MoneyThousandSep=','; SET MoneyDecimalSep='.'; SET MoneyFormat='£#,##0.00;-£#,##0.00'; SET TimeFormat='hh:mm:ss'; SET DateFormat='DD/MM/YYYY'; SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]'; SET FirstWeekDay=0; SET BrokenWeeks=1; SET ReferenceDay=0; SET FirstMonthOfYear=1; SET CollationLocale='en-GB'; SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec'; SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December'; SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun'; SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';
product.productcode, --'"', replace(replace ((case when sa3.documenttype = 'Invoice' then isnull(ol.manualdescription,product.description) else isnull(cnl.manualdescription,product.description) end), CHAR(10),''),CHar(13),' ') as 'Decription', --'"' , --Product Groups and Attributes
PG1.Name AS ProductGroup1 , PG2.Name AS ProductGroup2 , PG3.Name AS ProductGroup3 ,
product.udfRangeName As 'Range', product.udfrangestyle As 'Style', product.udfexclusivity as 'Exclusivity', product.udfpriceclass as 'Price Class' , product.udfmaterial as 'Material' , product.udfsize as 'Size' , product.udfshape as 'Shape', product.udffinishcolour as 'Colour',
case when sa3.DocumentType = 'Invoice' then format(round((OL.Quantity * OL.StockPerConversion),2),'###.##') else format((cnL.Quantity),'###.##') end as 'Qty Desp' , --need to ensure sales are all represented in the same units
isnull(stockper.percode,per.percode) as 'Per',
case when sa3.documenttype = 'Invoice' then ol.unitsellprice * (OL.Quantity * OL.StockPerConversion) else sa3.TotalSales end 'Total Undisc Price', sa3.totalsales 'SA-Total Sell', 'MPV' = sa3.totalinternalcost - sa3.totalcost, sa3.totalcost 'SA-Total Cost', sa3.totalinternalcost 'SA-Total IntCost',
sa3.DocumentType 'Doc Type',
'Stock Type' = case when left(product.productcode,3) = 'ZZC' then 'Catalogue Special' when left(product.productcode,3) = 'ZZZ' then 'Opal Dead Special' when left(product.productcode,2) = 'ZZ' then 'Generic Special' when left(product.productcode,1) = 'Z' then 'Defined Special' when product.producttype <>1 then 'Other' else 'Stock' End ,
CASE when sa3.DocumentType = 'Invoice' then isnull(oh.ordernumber,sa3.documentid) else cnh.CreditNoteNumber end as 'Doc No', CASE when sa3.DocumentType = 'Invoice' then ol.linenumber else cnl.linenumber end as 'Line No' ,
--'DocFrom' = case when isnull(olp.orderlineid,0)=0 THEN 'Opal' else 'BT' End,
ol.discount1 'Rep Disc1', ol.discount2 'Rep Disc2', ct.name 'Customertype', customer.name 'Customer', --customer.customertypeid, case when customer.cashaccount = 0 then 'Credit' else 'Cash' end as 'Cash a/c',
'Premier a/c' = case when customer.customertypeid = 5 then 'Retail' when customer.cashaccount = 1 then 'Trade Cash' else 'Trade Credit' end, sa.name 'Sales Area', st.name 'Sales type',
customer.customercode 'Cust Code', (customer.customercode + '~' + dbo.psiPostcodeFromString(isnull(oh.deliveryaddress,isnull(croh.deliveryaddress,cnh.invoiceaddress)))) as 'Property', dbo.psiPostcodeFromString(isnull(oh.deliveryaddress,isnull(croh.deliveryaddress,cnh.invoiceaddress))) 'Delivery Pcode', REPLACE(REPLACE(isnull(oh.deliveryaddress,isnull(croh.deliveryaddress,cnh.invoiceaddress)),CHAR(10),' '),CHAR(13),' ') 'Delivery Address', r.name ' Region', case when product.udfexclusivity in ('CPH Branded','Exclusive','Semi Exclusive') then 'Obese' else 'Non-Obese' end as 'Obese'
from FinancialYear fy WITH(NOLOCK) left join SalesAnalysis3 sa3 WITH (NOLOCK) on fy.FinancialYearID = sa3.FinancialYearID LEFT JOIN InvoiceHeader IVH WITH(NOLOCK) ON SA3.DocumentID = IVH.InvoiceID and sa3.DocumentType = 'Invoice' left join invoiceline as ivl on ivl.InvoiceLineID = sa3.DocumentLineID and sa3.DocumentType = 'Invoice' left join orderheader as oh with (nolock) on ivh.orderid = oh.orderid left join orderline as ol with (nolock) on ivl.orderlineid = ol.orderlineid LEFT JOIN CostAdjustmentHeader CAH WITH(NOLOCK) ON SA3.DocumentID = CAH.CostAdjustmentID LEFT JOIN Branch AS B4 WITH(NOLOCK) ON B4.BranchID = SA3.HomeBranchID LEFT JOIN Branch AS B3 WITH(NOLOCK) ON B3.BranchID = SA3.IssuingBranchID LEFT JOIN Branch AS B1 WITH(NOLOCK) ON B1.BranchID = SA3.BranchID LEFT join product on sa3.productid = product.productid LEFT join supplier WITH(NOLOCK) on supplier.supplierid = product.supplierid left join SalesRep as sr with (nolock) on sa3.salesrepid = sr.salesrepid
left join orderlinepriceinfo as olp with(nolock) on ivl.orderlineid = olp.orderlineid left join region as r with(nolock) on r.regionid = b1.regionid left join productprice as pp with(nolock) on sa3.productid = pp.productid and 0 = pp.branchid and pp.length = 0 left join productprice as ppo with(nolock) on sa3.productid = ppo.productid and ppo.branchid =isnull(sa3.IssuingBranchID, sa3.OrderingBranchID) and ppo.length=0 left join customer with(nolock) on sa3.customerid = customer.customerid left join customertype as ct with(nolock) on customer.customertypeid = ct .customertypeid left join customerfinancial as cf with (nolock) on cf.customerid = customer.customerid LEFT JOIN ProductGroup AS PG1 WITH(NOLOCK) ON PG1.ProductGroupID = sa3.ProductGroup1ID LEFT JOIN ProductGroup AS PG2 WITH(NOLOCK) ON PG2.ProductGroupID = sa3.ProductGroup2ID LEFT JOIN ProductGroup AS PG3 WITH(NOLOCK) ON PG3.ProductGroupID = sa3.ProductGroup3ID left join saletype as st with (nolock) on sa3.SaleType = st.SaleTypeID left join salesarea as sa with(nolock) on sa3.salesareaid = sa.salesareaid
left join per as stockper on stockper.perid = ol.CostPerID
left join CreditNoteLine as cnl with(nolock) on cnl.CreditNoteLineID = sa3.documentlineid and cnl.creditnoteid = sa3.documentid and sa3.documenttype = 'Credit'
left join CreditNoteHeader as cnh with (nolock) on cnh.CreditNoteID = cnl.CreditNoteID left join orderheader as croh with(nolock) on cnh.orderid = croh.orderID left join per with(nolock) on per.perid = isnull(ol.inputPerID,cnl.inputperid)
where sa3.financialyearid >= 6 -- >=2014
AND FOR THE EXCEL FILE
LOAD
[FinancialYear],
[Period],
[PdIndex]
FROM [lib://Sales (Despatches) starting from 2014/Financial Period Index.xlsx]
