AX 2012 Excel Import "Specified cast is not valid."

Question Status

Suggested Answer
CJ 373 asked a question on 28 Jun 2011 2:50 PM

I'm using the AX 2012 pre-release version of the excel add-in to try to import data.

I'm currently using the demo data and am trying to create a new vendor for the demodata's "CEU" company.

I've made the connection in excel to the CEU company, added the tables vendtable and dirpartytable and refreshed the data.

My plan is to create a new PartyID in dirpartytable by creating a company called "Test" by typing in "Test" in the name field of the excel sheet and typing in the number 7163 into the Party ID field (the previous Party ID had been 7162 so I know that 7163 has not been used.)

When I go to publish the new table I get the error "Specified cast is not valid." and I'm not sure what the error means or how to fix it.

Once I can get a new PartyID in the dirpartytable created, I can use the "Field Lookup" to look up the created PartyID in the vendtable and sucessfully publish a new vendor, but I don't know what the error means that I'm getting from the dirpartytable. Is it the error that is giving me problems or am I doing the entire process incorrectly?

Any help would be greatly appreciated, thank you.

Suggested Answer
CJ 373 responded on 1 Jul 2011 8:44 AM

For those who run into this problem:

I did some research on "Specified cast is not valid" and it seems to be coming from the sql server database when you try to 'cast' an incompatible data type. I think it may be because the excel sheet contains empty cells which try to become null values in the database.

I'm not entirely sure how accurate that is but I ended up being able to import parties into the dirpartytable by adding the additional fields from field chooser and added data into the 'Language', 'Party Type', and 'Search Name' fields.

Even though it looks like the field chooser only designates 'Party ID' and 'Name' as necessary fields, leaving blank values in at least one of the additional fields seems to cause it to reject the import.

Good luck!

Tim Schofield responded on 2 Jul 2011 10:59 PM

I don't the standard Excel import is capable of handling complex imports like Vendors, Customers, Adresses, Products/items, Forecasts, Journals,, or anything with an InventDimId.

But I'd love for someone to prove me wrong!

Uday-AX responded on 30 Sep 2011 5:06 AM

Tim,  You are correct. i am trying to load load product/items with product variants and storage and tracking dimensions using excel add in both methods (add data/add table). but finally forced to agree your point after cracking my head. could you share me the best way to do this task.

Thanks for your help.  

Volodymyr Yakubiv responded on 30 Sep 2011 6:52 AM

please read following article:

This should help you with Products/Items import using services.

Uday-AX responded on 30 Sep 2011 7:16 AM

This is a great post. Thanks much for sharing. I will give a try and update.

Huzaifa Gain responded on 30 Sep 2011 12:18 PM

Please read the following post to get an idea on what services are support and if not why not...

Xander Vink responded on 3 Oct 2011 6:40 AM

Bill Thompson suggests in an answer to the previously posted post that importing vendors isn't possible with the Excel add-in. So if you know another (simple) way to do this, please share :)

Second, you should upgrade to AX 2012 RTM with CU1. There is no need to work with the pre-release anymore.

Uday-AX responded on 4 Oct 2011 5:03 AM

Using the AIF services i was able to add the products/product master/variants and release products. Thanks to  Volodymyr Yakubiv.

But to release product variants i need "InventDimCombinationService". It is no where in AOT services. Any idea please?

Also, using excel add in AX 2012 RTM, I was able to load vendors in two tables DirPartyTable, VendTable for organization type. But for people type i was not able to load. Getting a personal suffix issue. i have involved DirPerson and DirPersonName table as well. But not succeded. Is it a product bug or any solution to do this?



Xander Vink responded on 4 Oct 2011 5:15 AM

Also, using excel add in AX 2012 RTM, I was able to load vendors in two tables DirPartyTable, VendTable for organization type.

Could you please explain how you did this?

Loading (creating) vendors using the Excel add-in gives me a 'surrogate key expansion error', no matter what... :(