OFFICE CHOICE DATA IMPORT

Their data is in some sql flat file database and comes with dos apps to extract it with.

Note: there's crap at the end of the sql output which you should remove also.

Stock import:

To generate the data use this command:

sql.exe "select code,sup_code,c_code,name,per,sg_code,n_cost,on_h,lpo_date,lm_date,lr_date,price,rol,min_ord from stock;" > stock.txt

now because this program is from 1991 the date format totally screws up with 2000+ dates and needs to be fixed because it it frells the fixed width formatting and I noticed dates and data would be chopped off when importing to excel, anyways this simple command fixes dates in teh format of mm/dd/1yy to become mm/dd/yy

cat stock.txt | sed 's/\([0-9][0-9]\/[0-9][0-9]\/\)1\([0-9][0-9]\)/\1\2/g' > stockdatefixed.txt

You shouldn't need to fix up the alignment after that but check it anyways before importing into excel.

Debitor import:

Dos is lame and had limits on args so you need to split queries up then join the files.

sql.exe "select code,name,add1,add2,sub_state,state,pcode,d_name,d_add1,d_add2,d_sub_state,d_state,d_pcode,phone1 from debtors;" > d1.txt

sql.exe "select phone2,fax,car,email,contact,abn,cr_limit,d_specinst,statement,date_lpaid,date_linv from debtors;" > d2.txt

paste d1.txt d2.txt | sed 's/CONTROL-M//;s/CONTROL-I//' > debtors.txt (NOTE THOSE ARE CONTROL CHARACTERS AND YOU WONT BE ABLE TO PASTE THEM INTO A COMMAND)

cat debtors.txt | sed 's/\([0-9][0-9]\/[0-9][0-9]\/\)1\([0-9][0-9]\)/\1\2/g' > debtorsdatefixed.txt

Don't forget to ditch the garbage lines at the top and bottom of debtorsdatefixed.txt before importing.

Creditor import:

Get these fields:

CODE,TYPE,ACC_NO,NAME,ADD1,ADD2,SUB_STATE,STATE,PCODE,PHONE1,PHONE2,FAX,CAR,ACCOUNTFAX,SALEMAIL,EMAIL,CONTACT,CATEGORY, ABN,GL_CODE,PAYMETHOD,REMITTER,BSB,BANKACCNO,PAYADVICE,PAYPERIOD,DAYS_BP,DISCOUNT,DATE_LPAID,AMNT_LPAID,CR_LIMIT, PIM_FLAG,MIN_ORDER,CURRENT,DAYS30,DAYS60,DAYS90,UNALLOC,BALANCE,PUR_MTD,PUR_YTD,PUR_LYR,HIST