Difference between revisions of "Import Data"

From Skynet Software Wiki
Jump to navigation Jump to search
(Created page with "= Overview = This import uses an XLS Spreadsheet to import all of the necessary data. It is recommended to import in the order displayed in this Wiki. = Customers = Column Fo...")
 
m (spelling corrections)
 
(28 intermediate revisions by 2 users not shown)
Line 1: Line 1:
−
= Overview =
+
===In This Section===
 
This import uses an XLS Spreadsheet to import all of the necessary data. It is recommended to import in the order displayed in this Wiki.
 
This import uses an XLS Spreadsheet to import all of the necessary data. It is recommended to import in the order displayed in this Wiki.
  
−
= Customers =
+
=== Step 1 - Upload File ===
 +
The first step is to upload the Excel file. Supported files are XLS, XLSX and XLSM. Contact support for other file types.<br />
 +
 
 +
'''Upload File'''<br />
 +
Select "Browse" and then navigate to the Excel spreadsheet on your computer.<br />
 +
 
 +
'''Ignore the First Row'''<br />
 +
Tick this option if the worksheet has a header record. So the import will ignore the first line.<br />
 +
 
 +
'''Select Import Type'''<br />
 +
Select the appropriate import type to import against.<br />
 +
 
 +
'''Default Stock Type (No BOM)'''<br />
 +
If importing Bill of Materials Product Data, select the Default Stock Type to use when Stock Records do not have any BOM components.<br />
 +
 
 +
'''Default Stock Type (BOM)'''<br />
 +
If importing Bill of Materials Product Data, select the Default Stock Type to use when Stock Records have BOM components.<br />
 +
 
 +
=== Step 2 - Worksheet Options ===
 +
'''Select Worksheet'''<br />
 +
Select the appropriate worksheet from the drop-down box.
 +
 
 +
=== Step 3 - Confirm Data ===
 +
In Step 3 you will be presented with the data. Anything in green will be created, anything in blue will be updated, anything in red is an error and needs resolving before you are able to complete the import.
 +
 
 +
=== Customers ===
 +
It is recommended to only run this at the setup stage of the system, as you will need to go through and clean up the Customers' contacts and addresses manually.<br />
 +
 
 +
Any further update afterwards will overwrite any manual work completed afterwards and may duplicate efforts.
 
Column Format:
 
Column Format:
−
Customer
+
#Customer
−
Telephone Number
+
#Telephone Number
−
Accounts Contact
+
#Accounts Contact
−
Customer Service Contact
+
#Customer Service Contact
−
Accounts Email Address
+
#Accounts Email Address
−
Customer Service Email Address
+
#Customer Service Email Address
−
Invoice Line 1
+
#Invoice Line 1
−
Invoice Line 2
+
#Invoice Line 2
−
Invoice Line 3
+
#Invoice Line 3
−
Invoice Line 4
+
#Invoice Line 4
−
Invoice Line 5
+
#Invoice Line 5
−
Delivery Line 1
+
#Delivery Line 1
−
Delivery Line 2
+
#Delivery Line 2
−
Delivery Line 3
+
#Delivery Line 3
−
Delivery Line 4
+
#Delivery Line 4
−
Delivery Line 5
+
#Delivery Line 5
−
VAT Number
+
#VAT Number
  
−
= Machines =
+
=== Suppliers ===
−
Column Format:
+
Although the "Delivery Lines" are included in the template (as it is a direct copy of the "Customers" template), they are not currently used and will be completely ignored by the import.
−
Machine ID Number
+
;Column Format:
−
Machine Specification
+
#Supplier Name
−
Serial Number
+
#Telephone Number
−
Year of Manufacture
+
#Accounts Contact
−
Machine Rate Per Hour
+
#Customer Service Contact
 +
#Accounts Email Address
 +
#Customer Service Email Address
 +
#Invoice Line 1
 +
#Invoice Line 2
 +
#Invoice Line 3
 +
#Invoice Line 4
 +
#Invoice Line 5
 +
#Delivery Line 1 (Not Used)
 +
#Delivery Line 2 (Not Used)
 +
#Delivery Line 3 (Not Used)
 +
#Delivery Line 4 (Not Used)
 +
#Delivery line 5 (Not Used)
 +
#VAT Number
  
−
= Tools =
+
=== Stock Category Codes ===
 
Column Format:
 
Column Format:
−
Tool Number
+
#'''Code ID'''
−
Rack Location
+
#'''Description'''
−
Available Status
+
#'''Type''' - Values must be one of the following:
 +
#*Machine
 +
#*Tool
 +
#*Packaging
 +
#*Masterbatch
 +
#*Raw Materials (this includes Additives)
 +
#*Finished Product (this covers any product)
 +
#'''Default''' - Value must be "Yes" or "No".
 +
See [[How Do I setup stock for MRP?|How Do I setup stock for MRP?]] for more details on how they will be created by default.
  
−
= Stock Category Codes =
+
=== Machines ===
−
Column Format:
+
Please Note: This import will fail if there is no "Machine" Stock Type already setup.
−
Code ID
+
;Column Format:
−
Description
+
#Machine ID Number
 +
#Machine Specification
 +
#Serial Number
 +
#Year of Manufacture
 +
#Machine Rate Per Hour
  
−
= Finished Goods =
+
=== Tools ===
−
Column Format:
+
Please Note: This import will fail if there is no "Tool" Stock Type already setup.
−
Customer
+
;Column Format:
−
Own SKU
+
#Tool Number
−
Customer SKU
+
#Rack Location
−
Part Description
+
#Available Status
−
Colour
 
−
Commodity Code
 
−
Box Quantity
 
  
−
= Suppliers =
+
=== Supplier Data ===
 
Column Format:
 
Column Format:
−
Supplier Name
+
#Supplier Name
−
Telephone Number
+
#Our SKU
−
Accounts Contact
+
#Supplier Description
−
Customer Service Contact
+
#Supplier Reference / SKU
−
Accounts Email Address
+
#Minimum Order Quantity
−
Customer Service Email Address
+
#Lead Time (Days)
−
Invoice Line 1
+
#Price Per KG
−
Invoice Line 2
+
#Price Per Thousand
−
Invoice Line 3
+
#Price Each
−
Invoice Line 4
 
−
Invoice Line 5
 
−
Delivery Line 1 (Not Used)
 
−
Delivery Line 2 (Not Used)
 
−
Delivery Line 3 (Not Used)
 
−
Delivery Line 4 (Not Used)
 
−
Delivery line 5 (Not Used)
 
−
VAT Number
 
  
−
= Supplier Data =
+
=== Bill of Material / Finished Products ===
 
Column Format:
 
Column Format:
−
Supplier Name
+
#Customer
−
Our SKU
+
#Your Part Number / SKU
−
Supplier Description
+
#Customer Part Number / SKU
−
Supplier Reference / SKU
+
#Part Description
−
Minimum Order Quantity
+
#Commodity Code
−
Lead Time (Days)
+
#Finished Component Colour
−
Price Per KG
+
#Part Weight
−
Price Per Thousand
+
#Material Stock Code
−
Price Each
+
#Material Description
 +
#Material Supplier Reference
 +
#Material %
 +
#Masterbatch Stock Code
 +
#Masterbatch Description
 +
#Masterbatch Supplier Reference
 +
#Masterbatch %
 +
#BOP Stock Code
 +
#BOP Description
 +
#BOP Supplier Reference
 +
#BOP UOM
 +
#BOP Stock Code
 +
#BOP Description
 +
#BOP Supplier Reference
 +
#BOP UOM
 +
#Bag Stock code
 +
#Bag Description
 +
#Bag Supplier Reference
 +
#Bag UOM
 +
#Bag Quantity
 +
#Box Stock Code
 +
#Box Description
 +
#Box Supplier Reference
 +
#Box UOM
 +
#Box Quantity
 +
#Preferred Machine 1
 +
#Preferred Machine 2
 +
#Preferred Machine 3
 +
#Cavities
 +
#Tool Number
 +
#Labour Content
 +
#Standard Cycle Time
 +
#Set Up Time (hours)
 +
#Scrap %
 +
#Selling Price each
 +
#Secondary Op Needed
 +
#Comments

Latest revision as of 11:48, 22 October 2021

In This Section

This import uses an XLS Spreadsheet to import all of the necessary data. It is recommended to import in the order displayed in this Wiki.

Step 1 - Upload File

The first step is to upload the Excel file. Supported files are XLS, XLSX and XLSM. Contact support for other file types.

Upload File
Select "Browse" and then navigate to the Excel spreadsheet on your computer.

Ignore the First Row
Tick this option if the worksheet has a header record. So the import will ignore the first line.

Select Import Type
Select the appropriate import type to import against.

Default Stock Type (No BOM)
If importing Bill of Materials Product Data, select the Default Stock Type to use when Stock Records do not have any BOM components.

Default Stock Type (BOM)
If importing Bill of Materials Product Data, select the Default Stock Type to use when Stock Records have BOM components.

Step 2 - Worksheet Options

Select Worksheet
Select the appropriate worksheet from the drop-down box.

Step 3 - Confirm Data

In Step 3 you will be presented with the data. Anything in green will be created, anything in blue will be updated, anything in red is an error and needs resolving before you are able to complete the import.

Customers

It is recommended to only run this at the setup stage of the system, as you will need to go through and clean up the Customers' contacts and addresses manually.

Any further update afterwards will overwrite any manual work completed afterwards and may duplicate efforts. Column Format:

  1. Customer
  2. Telephone Number
  3. Accounts Contact
  4. Customer Service Contact
  5. Accounts Email Address
  6. Customer Service Email Address
  7. Invoice Line 1
  8. Invoice Line 2
  9. Invoice Line 3
  10. Invoice Line 4
  11. Invoice Line 5
  12. Delivery Line 1
  13. Delivery Line 2
  14. Delivery Line 3
  15. Delivery Line 4
  16. Delivery Line 5
  17. VAT Number

Suppliers

Although the "Delivery Lines" are included in the template (as it is a direct copy of the "Customers" template), they are not currently used and will be completely ignored by the import.

Column Format
  1. Supplier Name
  2. Telephone Number
  3. Accounts Contact
  4. Customer Service Contact
  5. Accounts Email Address
  6. Customer Service Email Address
  7. Invoice Line 1
  8. Invoice Line 2
  9. Invoice Line 3
  10. Invoice Line 4
  11. Invoice Line 5
  12. Delivery Line 1 (Not Used)
  13. Delivery Line 2 (Not Used)
  14. Delivery Line 3 (Not Used)
  15. Delivery Line 4 (Not Used)
  16. Delivery line 5 (Not Used)
  17. VAT Number

Stock Category Codes

Column Format:

  1. Code ID
  2. Description
  3. Type - Values must be one of the following:
    • Machine
    • Tool
    • Packaging
    • Masterbatch
    • Raw Materials (this includes Additives)
    • Finished Product (this covers any product)
  4. Default - Value must be "Yes" or "No".

See How Do I setup stock for MRP? for more details on how they will be created by default.

Machines

Please Note: This import will fail if there is no "Machine" Stock Type already setup.

Column Format
  1. Machine ID Number
  2. Machine Specification
  3. Serial Number
  4. Year of Manufacture
  5. Machine Rate Per Hour

Tools

Please Note: This import will fail if there is no "Tool" Stock Type already setup.

Column Format
  1. Tool Number
  2. Rack Location
  3. Available Status

Supplier Data

Column Format:

  1. Supplier Name
  2. Our SKU
  3. Supplier Description
  4. Supplier Reference / SKU
  5. Minimum Order Quantity
  6. Lead Time (Days)
  7. Price Per KG
  8. Price Per Thousand
  9. Price Each

Bill of Material / Finished Products

Column Format:

  1. Customer
  2. Your Part Number / SKU
  3. Customer Part Number / SKU
  4. Part Description
  5. Commodity Code
  6. Finished Component Colour
  7. Part Weight
  8. Material Stock Code
  9. Material Description
  10. Material Supplier Reference
  11. Material %
  12. Masterbatch Stock Code
  13. Masterbatch Description
  14. Masterbatch Supplier Reference
  15. Masterbatch %
  16. BOP Stock Code
  17. BOP Description
  18. BOP Supplier Reference
  19. BOP UOM
  20. BOP Stock Code
  21. BOP Description
  22. BOP Supplier Reference
  23. BOP UOM
  24. Bag Stock code
  25. Bag Description
  26. Bag Supplier Reference
  27. Bag UOM
  28. Bag Quantity
  29. Box Stock Code
  30. Box Description
  31. Box Supplier Reference
  32. Box UOM
  33. Box Quantity
  34. Preferred Machine 1
  35. Preferred Machine 2
  36. Preferred Machine 3
  37. Cavities
  38. Tool Number
  39. Labour Content
  40. Standard Cycle Time
  41. Set Up Time (hours)
  42. Scrap %
  43. Selling Price each
  44. Secondary Op Needed
  45. Comments
Retrieved from ‘https://wiki.genisys-systems.co.uk/index.php?title=Import_Data&oldid=1892’