Copy Table from query manager does not copy all the rows

[najwalokman]

SAP Business One 10.0 (10.00.191) FP 2202 (64-bit)

Why are not all rows copied when we use the "Copy Table" function and paste into Excel?

[Johan_Hakkesteegt]

Looking at the actual data, you appear to have the same lines in both. What about the top lines? In other words, if data is not being copied, is it data from the top, the middle or the end?

P.S. I could not reproduce this (n.b. 1 difference for the title row):

[najwalokman]

Thank you for your response @Johan_Hakkesteegt 

Total rows displayed in SAP B1 Query Manager: 47,966

Total rows pasted into Excel using "Copy Table": 47,937

So, 29 rows are missing after pasting. I checked and both the first and last rows in SAP B1 appear in Excel, so it seems like the missing rows are somewhere in the middle of the data.

Has anyone experienced this before? Could this be a limitation or a bug in version 10.0 FP 2202?

[Johan_Hakkesteegt]

A couple of things you could try:

• Save your query, close and reopen the B1 client. Surprisingly often this will solve this kind of weird phenomenon.
• In Excel add filters at the top and check that you have the expected numbers of columns -> perhaps some rows were pasted next to each other. Possibly because of a special character in one of the values.
• If that is not the case, you could try adding a running number in the query, with the ROW_NUMBER function. Something like this:

select ROW_NUMBER() OVER(ORDER BY X.[some field or fields here]) AS row_nr
,*
from
( your query here ) X

Now when you copy this query result to Excel, you can check where the row number from the query result starts to deviate from the row number in Excel.

[mgregur]

Hi,

what @Johan_Hakkesteegt mentioned is the most likely culprit (special characters). Comparing data on both screens shows that the bottom row is the same, which means most likely all data was copied. But other than that, is there any reason why you are using this method instead of the built-in Export to Excel function?

BR,

Matija

[Johan_Hakkesteegt]

Personally I use the copy table function because in my experience the Export to Excel function is cumbersome and error prone. Although it depends on the situation of course.