Error 28, "Item is not sold" when importing item sale from CSV

[GRSeach]

AccountRight Premier v19.16 (19.16.1.225770).

I'm a retired VB developer who's been asked to automate invoice creation in MYOB, from a bunch of Excel sheets.

In Excel VBA, I've extracted the data from the source files and have created separate CSV files for users to import into MYOB, one for each sale. I did it that way because MYOB will not give us an ODBC read-write key (thanks to our MYOB version no longer being supported), and I have no expertise with the REST API. This is just the way it has to be.

All inventory items exist in MYOB and although they are marked as being sold, they are not inventoried, consequently their quantity on hand is zero. The CSV does include a quantity sold.

When I import the CSV, I get the "Item is not sold", error 28. I've opened the CSVs in Excel and have verified that the Item No is correct and where it should be. Nothing is obviously out of place. The CSV contains no extra eous rows. What should I look for?

By the way, normally I would attach an example of the CSV so you can check my work, but we recently got live hacked, so all my computers are offline and I can't (don't dare) connect one to another to grab a file. I bought a new tablet so I could at least lodge this question.

I've done a lot of research on this, but none of it has been successful. I'm running out of time on this one. I hope you can help. Thanks in advance.

Graham

[Mike_James]

Hi @GRSeach , I've attached sample export files from an AR2024 file, because even the order of fields can generate that error message.

Personally I prefer the tab-delimited format. Commas in addresses can mess up the import.

If working in Excel, amounts should be formatted as numeric, no dollar sign or commas, which Excel messes up when saving as txt.

Fields you don't need can be excluded.

You can import multiple sales in one file as long as there is a blank line between each transaction. 

[GRSeach]

Hi @Mike_James , thanks for replying. I appreciate it.

I had already removed commas and apostrophes while extracting the source data, so comma-separated is fine.

I stripped out most unused columns. Here's what's left. I got the same error.

CoLastName, FirstName, AddressLine1, AddressLine2, AddressLine3, AddressLine4, Inclusive, Invoice number, Sale date, CustomersNumber, ShipVia, DeliverySta tus, Item number, Quantity, Tax code, SaleStatus

RH US LLC, , , , , , X, 1/09/2024, 4522971, , B, PIANOX_62GR, 2, EXP, O

Graham

[Mike_James]

Hi @GRSeach , the headers and data go out of alignment at Inclusive. Add another comma after the X and it should be OK. Also watch out for leading or trailing spaces, eg remove the space before 1/9/2024.

[GRSeach]

Hi again @Mike_James ,

That can't be right; after Inclusive comes InvoiceNumber, then SaleDate. In the data, I have "X" for Inclusive, blank for Invoice Number, then the date.

Inclusive, InvoiceNumber, SaleDate

X.             ,.                            , 1/09/2024

Graham

[GRSeach]

Hi @Mike_James ,

Sorry, I see it now. That's actually a typo in my message. The actual values include 2 commas, not 1.

Graham

[gavin12345]

Hi @GRSeach 

As you are using v19, the order doesn't really matter, however the mapping does. Carefully check that each column is mapped to the correct field. 

Best approach. Enter the Sales Order manually. Export it with the selected fields. Re-import to ensure it imports, then use that export file as your 'template' for future imports.

As mentioned by @Mike_James, the space before the item number (and other fields) will cause an import issue.

Regards

Gavin

[GRSeach]

Thanks @gavin12345 , and of course @Mike_James .

I will do as you suggest @gavin12345, and will report back.

Graham

[GRSeach]

Hi @Mike_James and @gavin12345 ,

I did as @gavin12345 suggested by manually creating a sale in MYOB, then exporting it. I then deleted that sale and tried reimporting it. Now I get the following:

Error 81. Tax account linked account is missing.

We're making progress, but I'm confused by this error since there are hundreds of sales for this very same customer and the offending sale existed happily in MYOB beforehand.

Any ideas?

Graham

[gavin12345 · Accepted solution]

Hi @GRSeach 

Your opening comment indicated you were using v19.16 - please confirm, as this error is more common with AR2024.

Also rather than providing snippets of info, attach the import file and the importlog file which shows what transaction caused the error. Importing is a complex process, and the slightest thing can cause an error.

Suggest you also go through the tax codes and ensure every tax code that you use have a linked account for tax paid and tax collected.

Regards

Gavin