DPU: Importing YSL Data

Step	Details
Clear Database	truncate table au truncate table au_itms DBCC CHECKIDENT('au', RESEED, 1) DBCC CHECKIDENT('au_itms', RESEED, 1) truncate table nl truncate table nl_trans DBCC CHECKIDENT('nl', RESEED, 1) DBCC CHECKIDENT('nl_trans', RESEED, 1) truncate table crd_stock truncate table stk truncate table stk_alloc truncate table stk_cat truncate table stk_preorder truncate table stk_trans DBCC CHECKIDENT('crd_stock', RESEED, 1) DBCC CHECKIDENT('stk', RESEED, 1) DBCC CHECKIDENT('stk_alloc', RESEED, 1) DBCC CHECKIDENT('stk_cat', RESEED, 1) DBCC CHECKIDENT('stk_preorder', RESEED, 1) DBCC CHECKIDENT('stk_trans', RESEED, 1) truncate table bins truncate table goods DBCC CHECKIDENT('bins', RESEED, 1) DBCC CHECKIDENT('goods', RESEED, 1) delete from sl DBCC CHECKIDENT('sl', RESEED, 1) truncate table pl DBCC CHECKIDENT('pl', RESEED, 1) truncate table adrbook DBCC CHECKIDENT('adrbook', RESEED, 1) truncate table inv truncate table inv_itms DBCC CHECKIDENT('inv', RESEED, 1) DBCC CHECKIDENT('inv_itms', RESEED, 1) truncate table pi truncate table pi_itms DBCC CHECKIDENT('pi', RESEED, 1) DBCC CHECKIDENT('pi_itms', RESEED, 1) truncate table po truncate table po_itms DBCC CHECKIDENT('po', RESEED, 1) DBCC CHECKIDENT('po_itms', RESEED, 1) truncate table gdn truncate table gdn_itms DBCC CHECKIDENT('gdn', RESEED, 1) DBCC CHECKIDENT('gdn_itms', RESEED, 1) truncate table grn truncate table grn_itms truncate table grn_splits DBCC CHECKIDENT('grn', RESEED, 1) DBCC CHECKIDENT('grn_itms', RESEED, 1) DBCC CHECKIDENT('grn_splits', RESEED, 1) truncate table so truncate table so_itms truncate table soi_shipped DBCC CHECKIDENT('so', RESEED, 1) DBCC CHECKIDENT('so_itms', RESEED, 1) DBCC CHECKIDENT('soi_shipped', RESEED, 1) update nl set balance = 0, foreignbal = 0
Run Data Import	Use DataMap, dm_ysl130c.idm to import customers, suppliers, nominal ledger, stock, sales orders, sales order items
Import Balances	Run OneTOpenBal utility: Stock Opening balances. Memo data for customers, suppliers, nominal, banks and stock. Import Audit Trail. There is an additional option to import the audit trail.
Update Stock Prices from YSL.mdb	Download latest copy of ysl.mdb Copy & paste stock table to XLS spreadsheet. Delete all columns bar StockRef, Spr1, Spr2, Spr3, Spr4. Add blank columns before and after the stock code column and after Spr4. Enter unique characters in the preceding (~), following (#) and last column (). Save file as .csv and open in text editor. Use search / replace / macros to create entries suitable for SQL insert statement, eg: ('AA121015',3.55,4.05,4.65,5.3), Drop and recreate yslstk table in SQL DB. In the .csv file, add following code to every 900 lines, removing the comma from the preceding line: INSERT INTO [dbo].[yslstk] ([stkcd],[pr1],[pr2],[pr3],[pr4]) VALUES* Run the finished SQL query in SQL workbench for selected DB. Update stock prices: update stk set sellpr=(select top 1 y.pr1 from yslstk y where y.stkcd=stk.stkcd) update stk set sellpr2 =(select top 1 y.pr2 from yslstk y where y.stkcd=stk.stkcd) update stk set sellpr3 =(select top 1 y.pr3 from yslstk y where y.stkcd=stk.stkcd) update stk set sellpr4 =(select top 1 y.pr4 from yslstk y where y.stkcd=stk.stkcd)
Post Import Checks	Missing Nominal Codes. *select nl.accref as NomCode, (select COUNT() numb from so_itms oi where oi.nomcd = nl.accref) from nl Check Creditors / Debtors Balances. select nl.accref as NomCode, (select COUNT() numb from so_itms oi where oi.nomcd = nl.accref) from nl* .

Ice Ledger

Data Import: YSL