Square Boxes in Item Card Navision 2009 Problem

Cloud

Hi,

We have recently set up 300 items on Navision 2009. We do an export of Orders from our Magento Store to be imported into NAV this is when we discovered it has spaces at the end of the item code on Navision. On Windows XP machine there are 2 square boxes at the end of the code in the item card. On a newer machine it displays as spaces. I'm not too sure if these are called Carriage Returns?
We do not know how these appeared and having looked at the forums I can't make sense? Sorry

If we manually change the item code it takes more than 10-20 mins to update (probably more but cause it was taking long we had to stop it).

So instead of changing the codes in Navision, is it possible to amend in the CSV when we import? like importing a special character?

fyi - I've tried adding blank spaces in the CSV and tried to import it but it doesn't import at all...

please can someone help? I don't want to change Nav as it could take more than a day

Thanks

Mayur

vaprog

Cloud wrote:

is it possible to amend in the CSV when we import?

You should make sure the csv file is clean before attempting to import it. The file needs to be in OEM character set (no UTF-8/Unicode). It should have DOS line endings (CR followed by LF). I am not sure what happens to spaces before and after the field values. Best remove them also to be sure. There must not be any control characters in the file other than line ending combinations. Best make sure the file is created correctly from the beginning. Check it in a text editor, not in Excel.

Item."No." is used as a foreign key in numerous tables. If you do not want to use RENAME and let the system do all it needs, to keep those relations, you need to be careful to maintain them on your own. This obviously depends on what data you created on import (e.g. Item Unit of Measure), and how the imported items had been used since.

Cloud

Hi,

I am not sure what you mean. The file is exported using Magento into CSV but tilde separated for example:

CustomerPONumber~OrderNumber~OrderDate~LineItemUPC~LineItemUnitPrice~LineItemQuantity~ShipToFirstName~ShipToLastName~Department~ShipToAddress1~ShipToAddress2~ShipToCity~ShipToState~ShipToZip~ShipToCountry~ShipToPhone~Dccode~~~
700003461~700003461~08/21/2013~GA0033~1~TWE~Test Account~Gardens and Homes Direct~18 Church Street~~Ilkey~~LS29 9DS~GB~000000000~~~



In Nav we created this item code GA0033 this has 2 spaces or 2 square boxes at the end. and for this reason the order when imported has a line error because it cannot find it.
So instead of renaming in NAV which could take hours/days - I thought if we can do something in Magento or in the CSV when we export it, we won't have line errors and NAV will be able to match the items....

Hope that's a bit clear

Thanks

Mayur

Cloud

Hi,

don't know where my other reply went? Strange - hope you see it.

Also the 300 item were created manually in NAV. I'm not a NAV professional and nobody in the whole company knows.

vaprog

Did you import the csv file? if yes, how.
If you did not, in what way is the csv file relevant to your question?

Kishorm

My guess is that the 2 characters at the end of the item no. are CR & LF and that these were created because the user who created the items copied the field value from a cell in Excel and then pasted into the no. field in NAV. If you copy the cell instead of the cell contents in Excel then you do end up with this problem.

My advice would be to bite the bullet and rename the items to the code without the CR / LF at the end, however I understand that you do not want to go down this route.

Assuming this is the case and that the characters are CR & LF then you can't amend the CSV file as these characters are used to specify a new record so the only alternative you have is to update the import. What you'll need to do is add some code to first look for an item with the no. as per the imported value (to work with you correct item codes) and if a match is not found then look for an item where the no. is the imported value plus CR & LF at the end.

Cloud

I've imported it using Job Queue Entries.

there are 300 DD items (which were recently created) we have another 1000 items on NAV that were created by someone who now has left the company.

The CSV is relevant because we need to import orders in NAV using CSV. The problem lies when we import the orders only existing items get mapped while the 300 items we created manually do not. this is because of the spaces or the square boxes.

hope that makes sense?

Cloud

Hi Kishorm,

Thanks, that makes sense.

Does this mean I would have to add a code to every import?

again I'm not a NAV expert or a Coder. What code should I be using? Is there a standard one? I can then get my Magento Coder to add it to every CSV export (assuming this is what you mean) so when we do an import in NAV the code runs first and then import alll the orders with CR/LF and without.


Thanks

Mayur

Kishorm

Hi Mayur

You would need to add code to any import routine you have in NAV that needs to match on item numbers from an import file. As previously explained, you cannot amend the CSV file because the characters are CR & LF and these are used to separate each record in the Import so you Magento coder can not make the change. You need to make the change(s) in NAV.

As per my initial statement, this is not the way I would go - I would fix the root of the problem (ie clean the bad data in NAV) rather than create workarounds to imports etc - remember this bad data may cause you other problems, e.g. If you needed to export item data it could cause issues there.

Cloud

Thanks Kishorm,

for your help.

Savatage

here are some other discussions about cr & lf

viewtopic.php?f=23&t=55000&p=267143

vaprog

Where are the sqares/spaces? in the Item table or in the imported order lines?
In your initial post you said, "it has spaces at the end of the item code on Navision". What field of what table exactly does this refer to? In your import line there is LineItemUPC. Am I correct in assuming this has to be converted to the NAV Item."No.". Is it possible the wrong data is in that translation table?
If the "item code" is indeed read from the csv file, are there any characters in the csv file at the end of that "item code" field (just before ~ ) that do not belong to the item code?

Cloud

Hi Vaprog,

I've sorted it.