Browse
 
Tools
Categories

General File Preparation Requirements:

Author: Steve Childs Reference Number: AA-00849 Created: 2011-02-17 22:03 Last Updated: 2011-11-03 16:00 0 Rating/ Voters

Your current data is a representation of countless hours of data entry over time.  It is a given that your data is very important to the continued success of your business.  Therefore properly preparing, exporting, importing, and auditing your data is crucial.  Please read and follow each of the guidelines printed below to insure that all data this important to your business (that you plan to export and re-import) is exported and imported properly.

Your data (records) needs to be exported in like record sets.  In order for us to import your data, you (or your network admin) will need to export your data into separate, distinct text (.txt or .csv) files (tab delimited preferred)—by record type, (i.e. Customer records in a separate file, Supplier/Vendor records in a separate file, etc).  Depending on the types of data import services you have purchased and/or that are available to you, the following list may help you create a strategy to properly export your records into multiple files that contain records of like type:

    • Customer/Prospect Records (Customer Number, Company Name, Primary Contact First Name, Primary Contact Last Name, Address, City, State, Zip, etc)
    • Supplier/Vendor Records (Supplier Number, Supplier/Vendor Name, Primary First Contact First Name, Primary Contact Last Name, Address, City, State, Zip, Phone #, etc…)
    • Additional Individual Contact Records (Contact First Name, Contact Last Name, Contact Phone #, Contact Email Address, etc, with a link to the Customer Number or ID)
    • Additional Individual Activities related to any Individual Contact Records (Date of Activity, Type of Activity,
    • Additional Note Records related to any Individual Contact Records.
    • Inventory Item Records (Item Number, Item Description, Item Categories, etc)
    • Inventory Shipment / Stock Level Records (In many cases, the shipments / actual stock levels are stored in a separate table).
    • Inventory Pricing Levels (In many cases, price level 1, price level 2, etc are stored in a single table comprising item pricing).
    • Quotation (Header) Records (Quotation #, Quotation Date, Bill To Customer Number, etc)
    • Quotation (Line Item) Records (Qty Ordered, Qty Shipped, Item #, Description, Price, related Quotation #, etc)
    • Invoice (Header) Records (Invoice #, Invoice Date, Bill To Customer Number, etc)
    • Invoice (Line Item) Records (Qty Ordered, Qty Shipped, Item #, Description, Price, related Invoice #, etc)
    • Etc…

File Format/File Type: Whenever possible, please export the files into one of the following file types:

  • .txt files (tab delimited)  (Note: This is the most 'accurate' of all types, as comma's and quote/double quote marks typically do NOT cause issues with the columns of data, as it many times does with Comma Delimited or Comma Separated data files)
  • .txt files (comma delimited)
  • .csv files (comma separated values)
  • .xls files (MS Excel format)  (Note: Excel is very convenient to 'open', 'view', or 'edit' data in.  However Excel, in many cases, is not a good application to export data 'into'.  For instance, if you do not explicitly determine the 'data type' for each column, Excel will usually 'guess' the data type.  For example.  Suppose you have a column called "Customer ID", and suppose the first record had '1000' as the customer ID, and suppose that many of the other records had a alpha/numeric ID's such as ABC101, or 101ABC.  In this case Excel would 'guess' that the field is a number (since the first value was '1000') and would auto DELETE all the other Customer ID's that had alpha characters--since Excel guessed the field was (and set the field to) a number). This is not good at all...
  • .mdb files (MS Access format) (Some of the same issues as outlined above on the .xls format can apply here as well if the fields are not explicitily set to 'text' fields or other field types that agree with the data type being imported).

File Column Headings:

Naming Your Data File:  Please simply name each file with some type of common sense convention, such as XYZCompany_CustomerData_May2.txt, and XYZCompany_SupplierData_May2.txt, etc…  (Each data file would also have an accompanying Field Mapping Worksheet that would explain each of the fields/columns in the data file and would be named in a similar fashion, such as XYZCompany_CustomerData_FieldMapping.doc and XYZCompany_SupplierData_FieldMapping.doc. NOTE:

    Making Changes to your Exported Data Files:  In some cases a file may need to be re-exported to include all original data plus additional records that have been added to your system after you originally exported the records/data.  In this case, please do not change anything about the name of the file, except the date at the end.  Please do not change any of the column headings and/or the order of the columns.  If additional columns need to be added to subsequent versions of the data export file(s), please add them to the end of the row of columns and make a note of the change.  (This will make the task of re-importing the data much more simple and accurate, and will enable the data re-import to be completed in considerably less time, with fewer errors).

    Additional Notes About Incompatible or Invalid Data: 

    1. Incompatible Fields: No two accounting software applications are exactly alike, including the number and type of fields.  There may be fields in your application that are not currently in OneSource and/or that have no meaning or application in OneSource.  Conversely, there may be fields in OneSource that have no related fields in your current application. (Solution: Complete a Field Mapping Worksheet for each type of data you are exporting, such as Customers, Suppliers, etc… to help us understand which fields are important to your business, how the data is used, what the name of the field is, a sample record or two, etc)
    2. Invalid Data Types:  In some cases, both applications provide a field for the same type of data, such as a phone number, a date, or a record number, such as a customer number or an invoice #.  The problem is, in some cases, the type of data that is valid for the field may be different.  For instance, a field that stores a date may allow the date to be entered in a less than exact manner, such as 2.22.2002, or Feb 22, 2002, or 2.52.2002.  The other application may require the data to be entered as 2/22/2002 and may automatically delete or ignore the data being imported from the other application.  (Solution: While some types will simply not be compatible, most invalid data type problems can be corrected with a little work in advance.  Using the table below as a guide, instruct someone in your company to browse and correct all correctable problems in all of the data you plan to have OneSource import)

     

    Field Name or Type

    Examples of invalid or problematic characters or text

    Possible solutions or examples of valid data.

    Additional Notes

    Misplaced Text or Numbers

    Having a phone number entered in the address or in one of the contact name fields.

    Browse through your data and correct all data that has been typed in the wrong place.

     

    Text that is combined together when it needs to be separate.

    Fields where the First Name and the Last Name are in the same field.   Where the City, State, and Zip are in the same field.

    Browse through your data and correct all data that has been typed in the wrong place.  (If necessary or desired, you may need to split the fields up after the data has been exported out of your application, using Excel as the text editor—but not saving the file as an Excel (.xls) file).

    These types of problems make your data more difficult to find, or sort, or use.  For example, If the first name and last name are in the same field you will not be able to search for a Customer or Supplier record by first name or last name.  You will also not be able to send a letter beginning with something like, “Dear Jim” if Jim is part of a full name stored in one field.  If the City, State, and Zip are stored in one field, you will not be able to sort or search by City, or State, or Zip Code, etc…

    Duplicate ID’s (Duplicate Invoice #’s, Duplicate Customer Numbers, etc…)

    Self Explanitory…

    You typically cannot change the numbers.  You just need to be aware that they cannot be properly imported into any system, including OneSource.

    If or when we encounter this, we import the newer of the two records and give you a report of which records had duplicates.

    ID fields, such as a Customer Number, an Item Number, an Invoice Number, etc

    !@#$%^&*(), single quote marks, double quote marks

    ???

    In most cases, ID fields cannot be changed after they have been used by the system.  If any of these types of fields are invalid, it may require a small or large amount of data to be reentered by hand.

    Description or Name fields, such as a Company Name, an Item Description

    Names with double quote (“”) marks. 

    In may cases, you can change the double quotes to two (2) single quote marks (or remove the quote marks all together if possible).

     

    Phone Numbers

    444.444.4444, or any other text or quote marks in the phone number field

    4444444444 or 444-444-4444

     

    Date Fields

    2.22.2002 or any other additional text or characters or quote marks.

    2-22-2002 or 2/22/2002

     

    Currency Fields (Item cost and price fields)

    Any data other than numbers, (including single or double quote marks)

    $200.00, or 200.00, or 200

     

    Number Fields (such as a Quantity field on a Sales Order or in an Item record)

    Any data other than numbers.

    Browse through your data and correct all data and remove all text accidentally entered into number fields.

     

    Large Memo or Notes Fields

    N/A

     

    These types of fields typically allow for virtually unlimited types of text or characters.

    Fields that have values that begin with a 0.

    0100, 000900

    Export the data to a .csv or .txt file in a comma delimited format.  (Note: While Excel can be used to view or edit data saved in .csv or .txt format, avoid exporting data to or saving data in Excel, as Excel any many cases will try to “help” you by guessing what “type” of data (text, number, date, etc) resides in a column of data, based on the first record it finds, which in many cases causes a whole column of data to be automatically converted to a data type that is incorrect / wrong.

    If the data is being transferred from a text to a text or a number to a number, you may be fine.  However, in some cases when transferring from a text field to a text field or a number field, the 0’s are stripped off.  This can case the values to be different / wrong.

     
    Wiki Software by: KnowledgeBase Manager Pro v6.2.2 (Built with: JS.GUI - AJAX Applications)