IMPORTING FILES

Importing files is a quick way of getting data from one system, possibly manipulating it with third party software and (re)importing into CONTROL.

The data MUST have

• the columns in the correct sequence. Do NOT rearrange the column sequence.
• the column headings exactly as they were in the original blank spreadsheet

All columns with data in them will be assumed to match the columns defined below. All columns past the last column with actual data in the import file, will be given the currently defined "default value".

To import the file you need to run:

impXXmin "file_name"

• where XX => dr/cr/st/ca/gl/sz/cl for debtors,creditors,stock,categories,general ledger,size,colour

Running impXXmin -b "file_name" will blank fields if the field is equal to zero or blank

Running impstmin --qoh "file_name" is used in stock to import the quantity on hand field, otherwise it will be ignored. This updates the closing balance field. use this option when exporting out of one location closing balance to import into new location. Use this when importing a new location. it will use the value on hand and qty on hand columns on spreadsheet

Running impstmin --obqty "file_name" is used to import the opening balance quantity on hand. This is different from above. Closing balance will be automatically updated. this uses opening balance on spreadsheet

You can not run the --qoh and the --obqty in the same run.

To convert stock items to have mixed case. --to_mix_case

--alter_only - to only alter items that can be found in the spreadsheet (no adds)

--add_only - this will add only items which can not be found in the spreadsheet (no alters)

--no_colour_update - this will not alter or add any of the colour palettes

--no_size_update - this will not alter or add any of the sizes.

--do_size_update - This will update the size palette

--do_colour_update - This will only update the colour palette

-L - this will not update the log.dat. This is useful for fixing accounts which you do not want polled. eg. removing customers from franchisee

--do_online_update This will update the online TAB on stock file maintance

To get an empty copy of the import file, run

expXXmin

and specify the desired filename for the spreadsheet. Make sure that the filename ends with ".csv".

STOCK FILE IMPORT - 94 fields

• Stock/Style Number
• New_Code - enter the new code for the stock item in this field. enter the word DELETE if you want to delete a range of stock items (must meet deletion criteria)
• Description
• Comment
• Category (Dept/Sub-department - maximum of 8 characters)
• Alternate Category 1
• Alternate Category 2
• Alternate Category 3
• Alternate Category 4
• Alternate Category 5
• Alternate Category 6
• Alternate Category 7
• Alternate Category 8
• Supplier 1 (supplier code - maximum of 8 characters)
• Supplier 1 Product Code,
• Supplier 1 Cost in SUPPLIER'S currency
• Supplier 2 (supplier code - maximum of 8 characters)
• Supplier 2 Product Code,
• Supplier 2 Cost in SUPPLIER'S currency
• Supplier 3 (supplier code - maximum of 8 characters)
• Supplier 3 Product Code,
• Supplier 3 Cost in SUPPLIER'S currency
• Last Supplier Cost in LOCAL currency
• Duty %
• Tax Code (3=0%, 4=10%)
• Other Charges (amount or % of supplier cost in local currency)
• Landed Cost
• Current Sell Price (Inc GST)
• Promotional Sell Price (Inc GST)
• Promotional Start Date
• Promotional End Date
• Price Level 1
• Price Level 2
• Price Level 3
• Price Level 4
• Buy Unit (assume buy unit = sell unit, etc.)
• Hold Unit
• Buy_Hold Factor
• Selling Unit
• Hold_Sell Factor
• Barcode Number
• Quantity on hand
• Opening Quantity
• Minimum
• Maximum
• Processing Type (C = Consignment, D = discontinued, G = Gross (no qty), I = Inactive, M = Miscellaneous (fixed GP), S = scalable item, W = whole number only)
• Reporting Type
• Statistical (separate field in Rel 11 - mark items with an S)
• Temporary (separate field in Rel 11 - mark items with an T)
• On Internet (y/n)
• Weight
• Volume
• Attribute Group
• Colour palette
• Size Run

• SubSize Run

• Parent/Style number (this and next two columns are only valid for "child" items)
• Attribute
• Colour
• Size

• SubSize

• Season
• Rebate 1
• Rebate 2
• Print Labels
• Lead Time (days)
• Branch Product Code
• Bin Location
• Installation Charge
• Serial Item Y/N
• Associated Product Code

CREDITORS IMPORT - 24 fields

• Supplier_code
• Supplier_name
• main_contact
• main_addr1
• main_addr2
• main_addr3 (City, State, Postcode)
• main_country
• main_phone
• main_fax
• main_email
• web_address
• other_addr1
• other_addr2
• other_addr3 (City, State, Postcode)
• Supplier_ABN
• Supplier_Group
• Payment_Type (E for eft, C for cheque)
• BSB_Number
• Account_Number
• Account_Name
• Credit Terms (in days)
• Past End of Month
• GL Sundry Invoice Expense Account
• Supplier Charges GST
• Parent Code
• Settlement Discount

CUSTOMERS IMPORT - 32 fields

• Customer Code
• First Name
• Last Name
• Title (Mr, Mrs, Miss, Dr, etc)
• Customer Name (Business name or Full Customer name)
• Address 1
• Address 2
• Address 3 (City, State)
• Postcode
• Zone (delivery zone or postcode)
• Country (3 character country code)
• Main Phone
• A/H Phone
• Mobile Phone
• Fax Number
• Email Address
• Contact Person
• Group 1
• Group 2
• Credit Limit
• Delivery Address 1
• Delivery Address 2
• Delivery Address 3
• Category Price Group
• Stock Pricing Level
• Special Price End Date
• Point Rates
• Customer Accepts Backorders
• Send Customer Allocation update
• Auxiliary Code
• Statement Required
• Statement Send method
• Parent Account

CATEGORY/DEPARTMENT IMPORT - 29 fields

• Category ID (Category code including department prefix, or just department prefix)
• Category Name
• Category Type
• Group_price_start_date
• Group_price_end_date
• Discount_Markup
• Group1_DM
• Group2_DM
• Group3_DM
• Group4_DM
• Group5_DM
• Group6_DM
• Group7_DM
• Group8_DM
• Group9_DM
• Group10_DM
• Group11_DM
• Group12_DM
• Group13_DM
• Group14_DM
• Bonus Point Per Value
• Stock GL Control Account
• Cost of Sales GL Account
• Sales GL Account
• Installation Revenue GL Account
• Stock In Transit GL Account
• Work in Progress GL Account
• Stock Adjustment GL Account
• Recoverable Costs Credit GL Account

GENERAL LEDGER IMPORT - 8 fields

• GL code
• GL Description
• Post / No post
• P/L or B/S
• Debit / Credit
• Level
• Prime Parent
• Second Parent

run expglmin --imp_ly_bal for the csv, add the column "Last_Year_P12_Bal", after all the normal standard columns. importing this csv with extra column without the --imp_ly_bal will still run but the column will not update. also importing with the --imp_ly_bal with a normal csv without this column will still run as normal. please note that if the column is 0 or blank it will actually blank the field

COLOUR PALLETE IMPORT -

• Pallette Code
• Pallette Description
• Colour Code
• Colour Description
• Colour Add Amount

SETTING UP NEW CUSTOMERS

stock

• impstmin --qoh "file name". note: remove the , for the thousands, remove $ , and make sure figures are rounded to 2 decimal places. it uses the qoh figure on spreadsheet and value on hand
• importing creditors balances - crsimpmin filename , must be in the following order, supplier code, date, invoice no. amount - no headings required
• importing customers balances - drsimpmin filename, must be in the following order, customer code, date, invoice no. and amount - no headings required