Data Import

The standard data import utility is restricted to Texada Support staff only and can be accessed from the Utilities menu for System Maintenance for importing source data on LINUX systems.

These programs provide the ability to import multiple source text files into a destination file.
Generally this process is used to convert a new client's data from his old record system to SRM software file format.
This import action is not meant to make minor changes to existing data as it does not replace existing records unless the entire table is "cleared" out first.

Caution:
Existing data can be over-written and lost during the import process.
Always do a complete backup when new files are being imported into a company with any existing data.
If unsure on how these functions work, consult with experienced Texada Support.

These menu options provide the ability to create a template to capture and save the data, and then import from multiple text files into one destination file queuing all the standard data imports to run consecutively.
The order of the queue is pre-determined by the software to avoid potential RECORD NOT ON FILE errors.
e.g. The customer file is imported before ARAR and the Vendor is imported before APAP etc.

The prompts are:

CONVERSION TYPE

Select one of the date formats to apply to the data:

• Select North American to interpret dates in the Month/Day/Year format.
• Select European to interpret dates in Day/Month/Year) format.

DATE FORMAT

The selected date format from the Conversion Type is displayed for confirmation.
All dates on the spreadsheets must be entered in this format including the slashes to separate the values.

Note: Telephone and fax numbers can be entered without the dashes (-) as the format respected is defined by the masks in the Company Information.

Import File List:
Click the DETAILS button to access the data file lists as follows:

SEQ

The sequence number tracks the records in the table.

DATA TO IMPORT

The following Import data files are listed in the sequence in which the data should be captured, and the templates noted with an * (asterisk) provide the option to include existing data in the exported template.

• Chart of Accounts
• Divisions And Locations
  Note: The values 'SEL', 'ALL' and '~HO' cannot be assigned as Division codes or Alternate Division numbers in the Division Information table or when importing new division information, as these values are used for unique search actions by the standard selection filters, and by SmartEquip.
• Operators
  • New operators will be added with the same password as the operator code and will be prompted to update password at first sign in.
  • No Role Security is captured for new operators with this import, so this can be setup later in the Associated Operators manually or using the Export/Import Security Role templates provided in Security Roles.
  • A unique System ID is assigned to each new operator.
• Customer Type Codes
• Customers *
  • Blank customer only added if none exists and only if the customer name, tax1 code, tax 2 code, and on account columns are not all blank in the new record
  • When new customers are added they are automatically given website access with a default password that matches their new customer number and a default email address that matches their new customer number@GW.COM
    This web access information can always be modified in this Internet Information window or in the Portal Customer screen later.
• Customer Parent Account *
• Customer Notes *
• Customer Collector Notes *
• Customer Alternate Phone Numbers *
• Customer Secondary Types *
• Customer Balances *
  • the 'Type' column on the template represents I=Invoice, P=Payment, F=Finance Charge
  • 'Blank' in the Currency column becomes the Customer's currency
• Customer Credit Cards *
• Customer Driver's License *
• Customer Job Sites *
  • option to flag site as Physical Address for customer and to include Created Date/Time
  • if a valid 'Account Opened Date' is not passed in then the "Import as of" date is saved as the Account Open date
• Customer Name Check List *
• Vendors *
  • Direct Credit Banking fields reflect company banking preference
• Vendor Balances *
  • 'Blank' in the Currency column becomes the Vendor's currency.
  • If the invoice # submitted in Column C is greater than 9 characters then this value populates the "Invoice Ref" field on the A/P Invoice up to a max 20 characters.
• Rental Classes *
  • 'Clear' only removes all rental classes not currently associated with groups or products, and removes associated class services
  • GL accounts default from class defaults and if specified in import spreadsheet must be valid
  • The class Overtime Hours in a Day/Week/Month etc default from the Class Defaults and the hours submitted from the spreadsheet override the class defaults, but if values are zero then the class defaults are used.
    So to import with zero for all these columns, the Class Defaults need to be changed to all zeros before importing the classes.
• Sales Classes *
  • 'Clear' removes all sales classes not associated with groups or products, and removes associated class services
  • GL accounts default from class defaults and if specified in import spreadsheet must be valid
• Rental Groups *
  • 'Clear' removes all rental groups not associated with products, and removes associated group services
  • If 'Check Availability Y/N' column is left blank then this is converted to a Y on import
• Sales Groups *
  • 'Clear' removes all sales groups not associated with products, and removes associated group services
  • If 'Check Availability Y/N' column is left blank then this is converted to a Y on import
• Class/Group Services
  • 'Clear' removes ALL class and groups services
  • class/group must be valid
  • service code must be valid
  • record not saved if both Flat Amount and Percent are zero
  • percent cannot exceed 100
• Products *
  • validates the Class/Group referenced, and does not add the class/group from this spreadsheet
  • invalid product locations raise an error and will not be imported
  • 'Inventory' flag is 'blank' on the import file and the product is in a Re-rent Product Class or if the Inventory GL account is an expense account in the Product Class as outlined in Inventory Flag Defaults then the 'Inventory' flag is imported as N, otherwise it is set to Y
  • when adding rental class, 28Day/Monthly Billing defaults from Calendar
  • changes to Group, Class, Cost Type and Inventory Y/N flags are tracked in the Audit Log.
  • 'Blank' in the 'Special Order' column becomes N
  • 'Barcodes' restricted to unique Product#/Barcode# value but Barcode can match same product's number.
    Note: Any Barcodes that match a different product number (other than its own product#) are removed from the Barcode table regardless of whether it was on the import sheet or not.
  • Tag values:
    • Bulk Rentals with multiple tags yes - allows duplicate prod/loc rows and creates new tags, updates RSIL and Avg Cost accordingly
    • 'Date Acquired' is mandatory on the import sheet, and depreciation is written from that date forward as of the end of each month, along with the YTD number and YTD dollar depreciation totals
    • 'Tag Status' and "Warranty Expire Date' apply to rental products with an Asset Tag
    • Duplicate lines for a Bulk asset creating multiple tags are allowed for "Products" having the same 'Product#', 'Serial#'(or blank), 'Original Cost', 'OnHand Qty' and 'Date Acquired', only if assigned a different 'Owning Division'.
• Sales Product Locations
• Sales Product Serial Numbers *
• Rental Item Special Pricing
  • does not output existing special rate codes but includes option to populate spreadsheet with Group and Product Standard Rates
• Suggested Sales List
• Safety Notes
• Equipment Specifications
• Sales History

FILENAME

Enter the corresponding import Tab-Delimited ASCII file.
The directory path is not required if the file has been saved in the data directory on the server.

If the file has been saved locally, put the focus on this field and click the UPLOAD button to be moved from the Client to the Server as outlined in Select a File to Upload and to populate this field with the correct path and name.

Note: Import programs do not do complete error checking.
Error checking needs to be done at the Excel spreadsheet level prior to the final import of the data.

CLEAR

Check this box to DELETE all existing records in a file and replace them with the records in the import file.
A warning is triggered and confirmation to proceed is required, as deleted records can only be restored from a earlier backup.
SCS password is required.

Uncheck this box to add records from the import file to the existing file if the do not already exist without losing existing data.
Errors will be generated on incompatible records.

Note: When existing data for tables with "non-unique keys" such as Checklists, Comments, etc is included in the exported template, entries will be duplicated when that data is re-imported if the Clear option is not utilized.

DETAILS

Window to review the import file information and click DETAILS to view and modify the Excel column assignment for the fields in the data file.
The Excel columns are used to match the data on the import file to each field in the resulting data file.

Click the UPLOAD button if the file needs to be moved from the Client to the Server as outlined in Select a File to Upload

TEMPLATE

Window to create a new spreadsheet for this file table, listing all the data fields in the table in preparation for capturing the import data.
Files marked with an * (asterisk) in the Data to Import list above, provide the option to export existing data on the template, all other files always export an empty template.

• When existing data for Rental Groups is to be included on the template, the prompt to Specify Rate Code Order is triggered.

• When the existing data for Products is to be included on the template, the prompt to export All products, or just Rental or just Sales only is provided from the Selection Required window.
  

• When the existing data for Rental Products or All products is to be output on the template, the prompt to Specify Rate Code Order is triggered for the rentals.
  The prompt to export 'Original Cost' and 'Depreciation', or 'Current Book Value' is also triggered for Rental Products, using the Selection Required window. For Bulk Rentals the Original Cost/Depreciation or Book Value is calculated as the sum of the Tag amounts divided by the Tag quantity.
  When Rental Products are to be included on the template, the Input Required window is triggered to capture a default 'Date Acquired' that will populate column Q when there is a Fixed Asset tag but no date acquired on it.

Note: When existing data for tables with "non-unique keys" such as Checklists, Comments, etc, is included in the exported template, entries will be duplicated when the data is re-imported if the Clear option is not utilized to remove existing data for those same tables before importing.

Finished?

Click ACCEPT to proceed or CANCEL to abort the import.
Confirmation to proceed is required.

A record is written to track the import and whether any existing tables were cleared, in the Audit Log.

Importing Products:
If a product file is being imported, the Specify Rate Code Order screen is triggered.

Errors:
If errors in the import, such as duplicate records or records already exist that were not cleared occur the Data Import Errors screen in triggered.

Manual Data Capture:
If your firm does not wish to use import files to initially capture start up data, data can be loaded manually.
Refer to the following guides:

G/L Start-Up
A/R Start-Up
A/P Start-Up
Inventory Start-Up
Counter Start-Up
Back Office Start-Up
P.O. Start-Up
Work Order Start-Up

Note: Contact Texada Client Services if your firm requires data import for outstanding Contract details, using the Import Contracts utility.

Balance the G/L with the Sub-Ledgers:
Using either the manual or data import method of capturing the data, start up G/L postings still need to be completed to balance the A/R, A/P, and Inventory sub-ledgers to the General Ledger, as follows:

After loading the start-up Accounts Receivable and Accounts Payable sub-ledger open invoice records, print the A/R and A/P Aging reports to verify that the two sub-ledger totals match the corresponding G/L control account totals.

Similarly after the Inventory start-up information has been loaded, print the Inventory Value report to verify that the value of the products loaded into the Inventory sub-ledger matches the G/L inventory accounts.

The A/R and the A/P control account opening balances and the Inventory account balances should then be loaded with the other starting G/L account balances using Miscellaneous G/L Journals and then posted using Post Miscellaneous Journals.

ACTIONS:
Additional actions provided by buttons on the Data Import screen include:

• Access the Data List:
  Click the DETAILS button to list the data that can be imported with the Clear, view Details, and create a Template actions.

• Move a File from the Client to the Server:
  In preparation for importing, click on the relevant Filename field and then click the UPLOAD button to upload an import file saved as a "tab-delimited text file" using Select a File to Upload.
  The filename defaults based on server type to the current directory for Linux or to c:\temp after uploading.