Thursday, 25 September 2008

When Ctrl+C, Ctrl+V Fails--Send to Excel Saves the Day!

The other day a very scary thing happened to me-copy and paste from NAV failed! I was on a sales order and I copied the sales lines to the clipboard and pasted them to Excel. Nothing new there, we've been able to do this for years and it's been a pretty cool feature of NAV but when I came to check the totals, I was horrified to see that a few of my sales lines were missing!

I tried again and still they were missing. After some investigation, I found the cause of the problem was that some of my items had a double quote in the description for the items. This is not uncommon, especially when you have descriptions with inch sizing in the description. The problem seems to be when the very start of a field is a double quote and there is no double quote at the end. Excel was taking from the start of the double quote to the next double quote as being a full description field (so one of my fields included 10 or so sales lines in the field.)

I was about to log this with Microsoft as a problem with NAV when I saw that if I typed similar data into a text file and copied and pasted into Excel, exactly the same thing happened.

Thankfully the new (version 5.0 new) send to Excel feature saved the day as the XML approach copes with the double quotes without a problem.

2 comments:

Oleg Vasileshnikov said...

Hi,
This is because excel use function "Text to Columns" to separate pasted text. And by default this function use Quota as "Text qualifier".
To workaround this problem, before pasting you should run this excel function (Data->Text to Columns)and set "Text Qualifier" to "None". Now excel will normally paste texts with quotas until you restart excel.

Gaspode said...

Thanks for that tip. It's always useful when people leave comments and I appreciate you taking the time. Cheers, Dave.