Google sheets import query in google sheets not importing certain numbers

Google sheets import query in google sheets not importing certain numbers,google-sheets,Google Sheets,I am working on some data where i have to import the raw data from sheet Prepaid to the Master sheet but am seeing that certain number cells dont get imported like in cell B18 in sheet named Master. If I convert the raw data cell to number it works but it converts 11892667013478301 to 11892667013478300 leading to a mismatch. Is this is a size restriction on the number Sheet is below https://docs.google.com/spreadsheets/d/12y5h6NYArpEctQ2FD-AXJrqZcQydnEd5BjrOALMJEGI/edit?usp=sharing

I am working on some data where i have to import the raw data from sheet Prepaid to the Master sheet but am seeing that certain number cells dont get imported like in cell B18 in sheet named Master. If I convert the raw data cell to number it works but it converts 11892667013478301 to 11892667013478300 leading to a mismatch. Is this is a size restriction on the number

Sheet is below https://docs.google.com/spreadsheets/d/12y5h6NYArpEctQ2FD-AXJrqZcQydnEd5BjrOALMJEGI/edit?usp=sharing


#1

From QUERY docs:

In case of mixed data types in a single column, the majority data type determines the data type of the column for query purposes. Minority data types are considered null values.

Since most values in your column end with two 0s, they don't reach the digit limit of 15, and are treated as numbers. The values that reach 15 digits are treated as string values, and since those are a minority in the column, they are considered null values.

To avoid this, you can force all values in the column to be treated as strings via TO_TEXT, and apply the QUERY to that.

=QUERY(ARRAYFORMULA(TO_TEXT(Prepaid!E:F)),"select * where Col1 is not null")

#2

I'll delete my other answer, since yes, the issue seems to be that you are hitting the maximum number of significant digits, 15, for a number in Google Sheets. You can prove this by tring to add any small number to any of your (numeric) cells in Prepaid!F - the number doesn't increase, since it can't display any more significant digits.

The majority of your values are 15 signifcant digits plus two zeroes on the end. But F18 and F28 end in 01, not 00, so they are treated as strings. Forcing them to a number "discards" the last two significant digits, making them 00.

Perhaps the easiest answer for you is to force all of columns E and F to be text strings, rather than numeric values, and then they can all be dealt with equally, such as running queries against them.

Let me know if this helps at all.


#3

From QUERY docs:

In case of mixed data types in a single column, the majority data type determines the data type of the column for query purposes. Minority data types are considered null values.

Since most values in your column end with two 0s, they don't reach the digit limit of 15, and are treated as numbers. The values that reach 15 digits are treated as string values, and since those are a minority in the column, they are considered null values.

To avoid this, you can force all values in the column to be treated as strings via TO_TEXT, and apply the QUERY to that.

=QUERY(ARRAYFORMULA(TO_TEXT(Prepaid!E:F)),"select * where Col1 is not null")

#4

I'll delete my other answer, since yes, the issue seems to be that you are hitting the maximum number of significant digits, 15, for a number in Google Sheets. You can prove this by tring to add any small number to any of your (numeric) cells in Prepaid!F - the number doesn't increase, since it can't display any more significant digits.

The majority of your values are 15 signifcant digits plus two zeroes on the end. But F18 and F28 end in 01, not 00, so they are treated as strings. Forcing them to a number "discards" the last two significant digits, making them 00.

Perhaps the easiest answer for you is to force all of columns E and F to be text strings, rather than numeric values, and then they can all be dealt with equally, such as running queries against them.

Let me know if this helps at all.


#5

I will try that but first question is why the query in master is not able to fetch the data into the sheet if it can exist as a string in the prepaid sheet

#6

As lamblichus answers below, QUERY will only retrieve values that are the same TYPE as the majority of data in that column. So if most values are numeric, and a few vaalues are text, it does not retrieve any of the text values, even if those rows match the query criteria, and instead returns blank values for those rows.

#7

I will try that but first question is why the query in master is not able to fetch the data into the sheet if it can exist as a string in the prepaid sheet

#8

As lamblichus answers below, QUERY will only retrieve values that are the same TYPE as the majority of data in that column. So if most values are numeric, and a few vaalues are text, it does not retrieve any of the text values, even if those rows match the query criteria, and instead returns blank values for those rows.