Differences between revisions 1 and 40 (spanning 39 versions)

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

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 - 51 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
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 / Temporary (should be marked as a S or 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

CREDITORS IMPORT - 26 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

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

CATEGORY/DEPARTMENT IMPORT - 19 fields

Category ID (Category code including department prefix, or just department prefix)
Category Name
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

GENERAL LEDGER IMPORT - 8 fields

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

-  ⇤ ← Revision 1 as of 2007-05-11 05:52:39 → 
  Size: 1293
  Editor: julius
  Comment:
+   ← Revision 40 as of 2009-08-29 02:44:33 → ⇥
  Size: 4404
  Editor: jenny_ovpn
  Comment:
-Deletions are marked like this.
+Additions are marked like this.
 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:
-= 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:
-Category (Dept/Sub-department)
+To import the file you need to run:
 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
-Line 16:
+Line 19:
-Barcode Number
+To get an empty copy of the import file, run
-Line 18:
+Line 21:
-Cost
+{{{
expXXmin}}}
and specify the desired filename for the spreadsheet. Make sure that the filename ends with ".csv".
-Line 20:
+Line 25:
-Sell Price (Inc GST)

Unit (assume buy unit = sell unit, etc.)

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 - 51 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
 * 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 / Temporary (should be marked as a S or 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
== CREDITORS IMPORT - 26 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
== CUSTOMERS IMPORT - 23 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
== CATEGORY/DEPARTMENT IMPORT - 19 fields ==
 * Category ID (Category code including department prefix, or just department prefix)
 * Category Name
 * 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
== GENERAL LEDGER IMPORT - 8 fields ==
 * GL code
 * GL Description
 * Post / No post
 * P/L or B/S
 * Debit / Credit
 * Level
 * Prime Parent
 * Second Parent