importing Items - size limit on COA account field smaller than system limit (number + Description)

simonsimon Member Posts: 13
edited June 2018 in Accounts Business Range


I am trying to upload stock items and have hit this problem (that should really be fixed by Reckon) and was wondering if anyone knows a workaround:

The COA has an account:

630 · Consumables Lab - Stock on Hand

Which is 37 characters long. The text itself is 31 characters which appears to be the maximum allowed for the text field.

When trying to upload this, as one of the accounts associated with stock (COGS, Income or asset) I get the error that the field is too long.

Trial and error (with left(a1,x)) showed that the maximum allowable length (x) is 31.  But when I try to load a truncated version (first 31 characters) I get the error that the "specified account does not exist"

So I can't load it at normal length and I can't load it truncated.

My suspicion is that the developer of the upload failed to recognise that the total character length could be 31+7 (account number), 38 characters long not just the description of 31.

I could change the COA account description to make it shorter but that has downstream impacts on spreadsheets etc and so is not acceptable at this point.  Adding subaccounts for upload only would have the same downstream impact

So my questions:

1. is there a way to just use the account number or description instead of the combined?

2. can you wildcard the field - 30 characters and a * to make it select one?

3. Is it better to use QODBC to push this data into the tables? (just started looking at qodbc for reading data no idea how to load yet and I would imagine table keys would be tricky)

3. Any other ideas on how to make this work

Using Premier 2018

Thanks for your help.



  • simonsimon Member Posts: 13
    edited June 2018

    I think I have solved it.  just importing the account number appears to work.

    To separate account number from description, In excel, use the formula:

    =iferror(left(A1,find(A1," ")-1),"") where a1 is the coa (630 · Consumables Lab - Stock on Hand)

    this takes all the information before the first space (630).  returns nothing if no spaces found.

Sign In or Register to comment.