Error Importing Chart of Account from Sage 300

Hello Guys,

I’m trying to import the below Chart of Account from Sage 300 into Sage Budgeting and Planning. I’ve set all necessary parameters required for the import and Query display is showing correctly but when I click on the Run button, the system shows the below message in the Import Data Screen.

 

Your advise on the cause of the error and how to correct the error will be highly appreciated. 

  • From the messages what is happening is the source data for the ACCTFMTTD column is formatted as 45 characters long probably padded with spaces... the Account definition that has been set up in Budgeting and Planning is 10 characters of Data.   If your actual accounts are only 10 characters in length then all you need to do is in the import screen change the Account Number Line to be LEFT(ACCTFMTTD,10) and the Description Line to be LEFT(ACCTDESC,40) then your imports should work.

    If left is not valid in the syntax for your import we can find out what we need just let us know I think LEFT will work for you.

    If your account numbers in Sage 300 are more than the 10 characters defined you would need to set up the Account format to have the number of characters you need in the configuration section.

    If you have any question or need some assistance please contact support and we can do a dial in to take a look at it and figure out what we need to do for your needs.

    Kevin

  • Thanks Kevin,

    I'm getting the below error message when I click on the Run and going by your advise. Kindly advise on how to correct the error and how to setup the ERP Configurations.

    SELECT LEFT(ACCTFMTTD,10) as ACCTFMTTD, LEFT(ACCTDESC,40)as ACCTDESC, ACCTBAL,ACSEGVAL01, ACSEGVAL02, ACSEGVAL03 FROM GLAMF

    Scanning records for invalid data...
    Scan Completed!
    Inserting new records...
    Errors have occurred! Data import unsuccessful.

    Changed language setting to us_english.

    Changed database context to 'ABCODT'.


    END DATE: 24-Nov-15 TIME: 11:01:34 AM - ELAPSED TIME 00:00:00.0125
  • My guess here is that one or all of the ACCTBAL, ACSECVAL01, ACSEGVAL02 or ACSECVAL03 is not returning the correct data type or valid value for the column.

    I believe you are working on the Chart of Accounts import which the ACCTBAL would be the value used to fill the Credit Flag column and should be a 1 for a credit account 0 for a debit account.

    ACSEGVAL01 corresponds to the flag for if the account is an Actual Balance Account with a 1 for true and 0 for false   (in Sage 300 this defined as 15 Character String)

    ACSEGVAL02 corresponds to the flag for if the account is a Budget Balance Account with a 1 for true and 0 for false   (in Sage 300 this defined as 15 Character String)

    ACSEGVAL03 corresponds to the flag for if the account is a Statistical Value Account with a 1 for true and 0 for false   (in Sage 300 this defined as 15 Character String)

    So these 4 columns being returned need to have a value of a 1 or a 0, it looks lie the ACSEGVAL columns will not return the correct type of data.

    If you run a test query, what does the results of your query show?

    on the screen on the right side there is a box with a description of the values that the import is expecting based on the column selected in the query grid.

    Don't hesitate to contact support for assistance,

    Kevin

  • Hi. You are using the wrong import script for the Chart of Accounts import. The new 2016 Version has a wizard that creates import scripts for you automatically. Perhaps you can upgrade to this latest version? If you need a sample of the correct import scritp, then you can contact me at [email protected]