Differences between revisions 1 and 44 (spanning 43 versions)
⇤ ← Revision 1 as of 2007-05-11 05:52:39 →
Size: 1293
Editor: julius
Comment:
← Revision 44 as of 2009-10-09 04:58:07 → ⇥
Size: 5129
Editor: test02
Comment:
Deletions are marked like this. Additions are marked like this.
Line 2: Line 2:
Importing files is a quick way of getting data from one system, possibly manipulating it in a third party software and reimporting into CONTROL. The import must adhere to the following headings Importing files is a quick way of getting data from one system, possibly manipulating it with third party software and (re)importing into CONTROL.
Line 4: Line 4:
= STOCK FILE IMPORT =
== Here are the fields currently supported on our standard stock import program. ==
Stock Number		 The data '''MUST''' have
Line 8: Line 6:
Description  * 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".
Line 10: Line 10:
Category (Dept/Sub-department) To import the file you need to run:
Line 12: Line 12:
Supplier 1, {{{
impXXmin "file_name"}}}
 . where XX => dr/cr/st/ca/gl for debtors,creditors,stock,categories,general ledger
Running {{{impXXmin -b "file_name"}}} will blank fields if the field is equal to zero or blank
Line 14: Line 17:
Supplier 1 Product Code, 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.
Line 16: Line 19:
Barcode Number 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.
Line 18: Line 21:
Cost You can not run the --qoh and the --obqty in the same run.
Line 20: Line 23:
Sell Price (Inc GST) To get an empty copy of the import file, run
Line 22: Line 25:
Unit (assume buy unit = sell unit, etc.) {{{
expXXmin}}}
and specify the desired filename for the spreadsheet. Make sure that the filename ends with ".csv".
Line 24: Line 29:
Quantity on hand

Minimum,

Maximum

Weight

Volume

Parent,

Colour,

Size

= CREDITORS IMPORT =
Supplier code

main_contact

main_addr1

main_addr2

main_city

main_state

main_postcode

main_country

main_phone

main_fax

main_email

other_addr1

other_addr2

other_city

other_state

other_postcode

abn





= CUSTOMERS IMPORT =


Customer Code

First Name

Last Name

Customer Name (Business name or Full Customer name)

Address 1

Address 2

State

Postcode

Country

Main Phone

A/H Phone

Mobile Ph,

Fax Number

Contact

Group 1

Group 2



DepartmentDepartment ID

Department Name





Category

Department ID

Category ID

Category






 == STOCK FILE IMPORT - 53 fields ==
 * Stock/Style Number
 * Description
 * Comment
 * Category (Dept/Sub-department - maximum of 8 characters)
 * Alternate Category
 * 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
 * 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
 * Parent/Style number (this and next two columns are only valid for "child" items)
 * Attribute
 * Colour
 * Size
 * Season
 * Rebate 1
 * Rebate 2
 * Print Labels
 * Lead Time (days)
== 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
== CUSTOMERS IMPORT - 27 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
== 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

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 for debtors,creditors,stock,categories,general ledger

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.

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.

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

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 - 53 fields

  • Stock/Style Number
  • Description
  • Comment
  • Category (Dept/Sub-department - maximum of 8 characters)
  • Alternate Category
  • 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
  • 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
  • Parent/Style number (this and next two columns are only valid for "child" items)
  • Attribute
  • Colour
  • Size
  • Season
  • Rebate 1
  • Rebate 2
  • Print Labels
  • Lead Time (days)

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

CUSTOMERS IMPORT - 27 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

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

Import (last edited 2024-11-08 05:38:48 by JennyTinsey)