|
Size: 654
Comment:
|
← Revision 23 as of 2013-09-18 06:09:34 ⇥
Size: 2071
Comment: converted to 1.6 markup
|
| Deletions are marked like this. | Additions are marked like this. |
| Line 1: | Line 1: |
| = OFFICE CHOICE DATA IMPORT = | |
| Line 3: | Line 4: |
| = Stock import: = | Note: there's crap at the end of the sql output which you should remove also. == Stock import: == |
| Line 6: | Line 9: |
| sql.exe "select code,sup_code,c_code,name,per,sg_code,n_cost,on_h,lpo_date,lm_date,lr_date,price from stock;" > stock.txt | 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 |
| Line 8: | Line 11: |
| Inside stock.txt is hopefully data for all those fields in fixed with format. However since this data and programs are from 1991 the dates are all fubared and so stuff for say 26th july 2006 will come out in teh format, 07/26/106, this also frells with the fixed with output so you need to manually adjust the headings to make them align before trying to open it with excel. | 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/'''1'''yy 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 |
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