Document Sample
Importing-Data-into-Sage Powered By Docstoc
					                                Importing Data into Sage
The Import option allows you to enter data directly into Sage Accounts from CSV files. These files
can either be generated by Sage Accounts or by other programs, such as spreadsheets, word
processors or other accounting packages.

Data can be imported into any of the following:

    •   Customer Records
    •   Supplier Records
    •   Nominal Ledger Records
    •   Audit Trail transactions
    •   Stock Records
    •   Stock transactions
    •   Project Records - Sage 50 Accounts and Sage Line 50 v12.xx

The Import Option

The Import option can be used to import data into Sage Accounts for a number of reasons, for

    •   To add data to Sage Accounts from another program.

    •   To remove, add or amend a field in each of your records in Sage Accounts without having
        to edit each one individually.

    •   If you do not have any transactions in Sage but have entered all of your customer,
        supplier, nominal, bank or product details, you can change the account codes without
        having to enter all of the account details again.

    •   To batch update your Sage ledgers with transactions you have posted in a different

This Document deals with Importing data from Excel into Sage, and uses an example of Customer
Data. For the rules governing acceptable data, please refer to the latter part of this document.

To Prepare Data to be Imported

Imported data files must be in a specific format otherwise an error is reported and the import
procedure fails. These files are called CSV files and have the file extension .CSV.

Each record or transaction in these files takes up one line, and each field must be separated by a
comma. To view the complete CSV file structures, please press F1 to open the Help system, and
type in What File Structure Does Sage 50 Accounts Recognise? and see the other document ‘CSV
File Structure for Importing Example’ accompanying this for an example taken from Sage.

Please note: the size of the text in the Excel CSV docs does not seem to matter. Though it is
specified in the additional document (size 60, 30 etc.), the below should suffice.

Prepare an Excel workbook in the following style, setting out the headings as defined in the
accompanying document in the left hand column.
NOTE: Make sure that when you save your excel file it is saved in a CSV format (use the drop down
menu on the Save As window to find it.

To Import Data
Note: Before overwriting or adding to your data by using the Import option, you
must take at least one backup of your data.

   1. Open the File menu at the top left of your screen, then choose Import.

       The File Import window appears.

   2. From the File Import window, select the data types to be imported, for example, Customer
      Records then click Run.

       The Open window appears.

   3. From the Open window, browse to and select the file to be imported, then click Open.
    The Import procedure has two stages:

    The source file is checked to ensure that the data is in the correct format.

    Then if no errors are discovered, the data is imported and posted into the appropriate
    Sage data files.

4. If the import is successful, the File Import Success window appears displaying all of the
   imported data.
        Tip: If required, to exit from the window, click Close.

5. You can then view your newly imported customers in the Customer List.
If the data import was unsuccessful then the File Import Errors window appears displaying the
errors in the data that caused the import to abort.

The line in the data file where the error occurred is recorded, along with the field and the nature of
the error. To print the report, click Print. You should then edit the CSV file to correct the errors, and
run the import again. For further information about import errors, please refer to the following
section CSV file import error messages.

You have now imported your data, and can continue to use Sage Accounts.

Rules in Sage for preparing your Data
Comma Separated Value Files

Any file that is to be imported must be held in Comma
Separated Value (CSV) format. CSV format means that every
item of data is separated by a comma and each line of the file
represents one record, although you may get line wrap. For
example, a typical Customer Record in CSV format would look
like this.

SAGE0001,The Sage Group PLC,Great Park,Newcastle upon
Tyne,NE13 9AA,John Smith,0845 111 5555

The Import option makes the following assumptions about the
incoming data:

    •   Each data record is terminated by a carriage return,
        therefore, to create a new record, from your press

    •   Each line of the CSV file represents one record. This is
        created in normal text by entering a carriage return at
        the end of your record and is not affected by line wrap.
        For example, the following record represents one line
        of a CSV file, even though it wraps onto the next line.

        SAGE0001,The Sage Group PLC,Great Park,Newcastle
        upon Tyne,NE13 9AA,John Smith,0845 111 5555

    •   Each unit of data is separated by a comma.

    •   The data for each field in your CSV file must be
        entered in an exact order for it to be accepted into
        Sage Accounts.

        Note: For further information about the Sage Line 50
        CSV file structures, please refer to the following Ask
        Sage Articles or press F1 to open the Help system.

        12020 - CSV File Structures for Sage Line 50 v12.xx
        11344 - CSV File Structures for Sage Line 50 v7.xx to
        11969 - CSV File Structures for Sage Instant Accounts

    •   A unit of data in a record can be one of the following
        data types:

        Text        ABcd234
        Integer     1,2,3,4,5,and so on
        Decimal     23.58,132.05,
        Number      0.23 and so on.

    •   All units of data must be kept to their specific types of
        text, integer or decimal numeric, if not then the import
        will fail. For example, a customer's account reference
        such as SAGE001 can contain both alpha and numeric
        characters, therefore it is considered text.
        A department code can only contain numbers,
    therefore it is considered integer, for example 080.
    A customer's turnover can only contain decimal
    numbers, so it is considered decimal number, for
    example, 1500.00.

•   Each data unit has a maximum length. If data is
    entered that is longer than the defined maximum, for
    example, ten characters in an eight character field,
    then the data is simply shortened to the required
    length and no error is reported. For example, an
    account reference of SAGE0001354 is truncated to
    SAGE0001 because the maximum field length for an
    account reference is 8 characters.

•   The maximum field length of any data field is 255
    characters Data exceeding this figure causes the
    following message: 'Field too long for input buffer'. For
    further information about the messages produced
    during the Import routine, please refer to the section
    CSV file import error messages later in this article.

•                                        allowed,
    Spaces within numeric fields are not allowed for
    example, a price of 1 000.00 does not import but a
    price of 1000.00 imports successfully.

•   Spaces within customer or supplier account codes are
    removed and the text is changed to upper case. For
    example, Sage Group PLC is changed on import to
    SAGEGROU. The text has been cropped to the
    maximum eight character, converted to upper case
    and the spaces have been removed.

•   Quotation marks, ", cannot be imported into any
    version of Sage Accounts For example, a product
    description of, 8" * 11" Timber fails because CSV files
    treat " differently.

•   Enclosing a data field in quotes causes any commas
    contained within it to be read as part of the data and
    not as a separator For example, 10, Acacia Avenue
    would incorrectly enter 10 into the first line of the
    address then Acacia Avenue in the second address
    line but, "10, Acacia Avenue" imports correctly into
    address line 1.

•   Two consecutive commas signify a blank or zero length
    unit of data. Importing a blank field does not overwrite
    existing data in that field, leaving existing information
    intact. This feature is used when you want to change
    selected data in a record. For example, to change just
    the telephone number in a Customer Record, the
    import data might read:

    A00003,,,,,,,,0845 111 5555

    The address line fields between the account code and
    telephone number are not overwritten. However, if you
    mistakenly input:
         A00003,,, ,,,,,0845 111 5555

         the 4th field contents are removed.

CSV File Import Error Messages

Line too long for input buffer

Each complete record is a string of characters terminated by a carriage return. The maximum
length of a line is 1755 characters. If you have a line longer than 1755 characters this message
appears and you need to reduce the file length. This message may also be produced if you try to
import a file that is not in CSV format.

Field too long for input buffer

The maximum number of characters in any individual field is 60 characters. This message appears
when an individual field exceeds 60 characters. Any subsequent characters are treated as
belonging to the next field and may cause inconsistencies with expected field variables. For further
information about this, please press F1 to open the Help system.

Quotes out of sequence

Any field can be enclosed in double quotes (" ") and these quotes must appear as the first and last
characters of the field. If a quote is encountered in the middle of a field this message appears. For
example, if you use " for inches in your product descriptions, this causes the quotes out of
sequence error message to appear. You must edit the CSV file and remove these extra quotes.

Missing comma

If a closing quote (") is not followed immediately by a comma this message appears. You must edit
the CSV file and enter the missing comma.

Incomplete record

If the end of a line has no closing quotes and the last field in this line starts with an opening quote,
this message appears. Opening and closing quotes are dealt with in the same way as if no quotes
surround the field. The Import option reads any quotes as separators so they cannot be included
within a field. The relevant quote needs to be removed or if the opening quote is meant to be a
separator then the end quote must be inserted.

Disc or file error

This message occurs if the data files you are trying to import are corrupt or incorrect. It can also
occur if there is a disk read or write error.

Insufficient disk space to import this file

This message appears at the end of Pass 1 if the Import option has calculated that the amount of
space left on the data disk is insufficient to store the imported data file. You must free up more
space on your hard disk before proceeding with the import.

Error while writing file
This error occurs as a result of disk read or write errors. In this case the CSV file has not been fully
imported and the data files may not have been closed correctly. You should restore a backup of
your data and perform the import again.

Disk or file access error

This indicates an error reading or writing data. This could be due to a read or write file error or to
corrupt or uninitialised data. You should check your data files in your program using the Check
Data option and check any disks you are using for the import.

Too many fields

This message appears if there are more than the permitted number of fields on a line for the
import in progress. You must edit the CSV file and remove these extra fields.

Invalid data for .....

This error covers many situations but the type of field involved is always specified. This usually
indicates an inconsistent character, such as a letter in a numeric field. The CSV file must be edited
and the inconsistency removed.

Out of range for .....

This message means that the value specified for a numeric field is outside the permitted range.
The maximum value for all numeric fields, unless otherwise specified is 10 million. This field needs
to be edited and reduced to an accepted value.

Illegal tax code

This means that the value specified in a tax code field is not valid. To correct this you must enter a
valid tax code